Bug 68426 - ODBC - Failure to retrieve and display data via Base GUI with mysql and ODBC - LibreOffice OSX 64bit
Summary: ODBC - Failure to retrieve and display data via Base GUI with mysql and ODBC ...
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-08-22 11:27 UTC by Alex Thurgood
Modified: 2013-08-29 13:56 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
text of full error messages (1.40 KB, text/plain)
2013-08-26 09:09 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2013-08-22 11:27:05 UTC
With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SELECT * FROM `m
Comment 1 Alex Thurgood 2013-08-22 11:29:19 UTC
Strange, my report got chomped, so reposting :

With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SELECT * FROM `m
Comment 2 Alex Thurgood 2013-08-22 11:30:18 UTC
With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SELECT * FROM 'm
Comment 3 Alex Thurgood 2013-08-22 11:31:39 UTC
With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SELECT * FROM "`m
Comment 4 Alex Thurgood 2013-08-22 11:33:43 UTC
With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

SELECT * FROM m
Comment 5 Alex Thurgood 2013-08-22 11:35:14 UTC
Closing this as INVALID pending investigation as to why BZ refuses to accept more than a far more limited number of characters.
Comment 6 Robinson Tryon (qubit) 2013-08-22 14:57:30 UTC
(Testing Bugzilla re: Total # of characters... perhaps it doesn't like SQL?)

With my shiny new 64bit OSX build of LO master :

Version: 4.2.0.0.alpha0+
Build ID: 22d1beb78a475e4846af945afde1c4d6c263b5d6

I can connect to a registered ODBC datasource, and see the tables listed when I click the Tables icon in the main base window.

However, if I double click on any table entry, I get the following error message :

The data content could not be loaded.
Syntax error in SQL statement.

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

SQL Status: HY000
Error code: 1000

XXXXXX X XXXX X

Lorem Ipsum Dolor Set (...or something, I don't remember how that goes! :P )

Another few lines of text here
should be enough to tell
if the problem exists in total number of chars
or if it's just biased against SQL...
Comment 7 Alex Thurgood 2013-08-23 12:43:52 UTC
Sigh...if that's the case, then we're in trouble when it comes to reporting SQL problems on Base. Is this some recent BZ SQL injection clean-up zeal or something ? There never used to be a problem with reporting bugs in this way.



Alex
Comment 8 Alex Thurgood 2013-08-25 11:09:39 UTC
Re-opening to unconfirmed, will try and find some other way of providing the info required. Looks like it'll have to be as an attachment.
Comment 9 Alex Thurgood 2013-08-25 11:11:01 UTC
Putting Lionel on CC
Comment 10 Alex Thurgood 2013-08-26 09:09:00 UTC
Created attachment 84636 [details]
text of full error messages

Output of error messages when attempting to display the contents of a mysql table via ODBC
Comment 11 Lionel Elie Mamane 2013-08-28 05:13:01 UTC
What bugzilla does not like is the NULL characters in the SQL command, in the schema and table name... which are also most probably the reason for this bug.
Comment 12 Lionel Elie Mamane 2013-08-28 07:09:56 UTC
It looks like the table name is given in UTF-16, but that is not the encoding that MySQL expects.

It works for me on Debian GNU/Linux amd64 (64 bits). However, I can reproduce something similar with table names containing non-ascii characters if the setting "Character set" is set to something wrong. Could you check this setting (edit / database / properties / Additional settings)?
Comment 13 Lionel Elie Mamane 2013-08-28 08:26:12 UTC
Try setting *both* "charset = utf8" in the ODBC DSN being used *and* the LibreOffice character set option to "Unicode (UTF-8)". If it does not work, reopen.

The whole story is that:
 - LibreOffice and the MyODBC driver on the one hand
 - the MyODBC driver and the MySQL server on the other hand
need to agree on what character set to use where. If the MyODBC driver
does conversion, these two decisions need not necessarily match.

My guess is that your LibreOffice is configured to use UTF-16 (because that is the "System" default), but MyODBC/MySQL configured to use latin1 (the default) or maybe utf8. Note that AFAIK MySQL does not support using UTF16 (maybe MyODBC does, not sure).

Where it gets hairy is that MyODBC IMHO is buggy in that respect. Try to create
in MySQL a column with non-ASCII characters in its name. Then set LibreOffice and MyODBC to "Western (ISO8859-1)" and "latin1", respectively. You'll notice that the column names comes out wrong.

MyODBC has a "charset" parameter, so when it is set it *must* arrange for everything to be in that charset when communicating with the application. MyODC's design is to *not* set that charset for the communication with MySQL, but to do conversion itself (probably for speed reasons on the server: do more work on the client, rather than on the server). So it sets the MySQL server variable character_set_results to NULL (overriding the default). Which leads MySQL to give column names in character_set_system, which by default is UTF8... But MyODBC does not do conversion there!
Comment 14 Alex Thurgood 2013-08-29 13:12:08 UTC
(In reply to comment #13)

<sarcasam on> Fantastic  ! <sarcasm off>

Will try messing around with the collation and charsets.

I now recall getting bitten by a similar problem with OOo2.1 on OSX 10.3 or 10.4 many years ago, but at least at that time I still got data retrieval, even if I couldn't send modified or new data to the server.



Alex
Comment 15 Alex Thurgood 2013-08-29 13:56:11 UTC
Result ! yes, thanks Lionel. 

It seems that it is sufficient to just set the charset in the odbc.ini file, however I have done as you suggested and set both to the same value in odbc.ini and within the ODB Additional Settings dialog.