MySQL Bugs: #36198: MYSQL Connector 3.1 AND 5.1 : Strings with a length of 255 chars = ODBC Error

Bug #36198	MYSQL Connector 3.1 AND 5.1 : Strings with a length of 255 chars = ODBC Error
Submitted:	18 Apr 2008 9:01	Modified:	22 Apr 2008 13:08
Reporter:	M. Locks	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.1 AND 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	255 chars, MySql Connector, odbc call failed

Description:
The connector identifie the quotes of a string in a WHERE clause as a part of the string. So if we makes a where on a string of 255 chars, there is an ODBC call failed.

Exemple : - On an Access Linked to MySQL database I make a querie (wich works on non linked Access and MySQL but not with myodbc !):

SELECT * FROM [CON_E_6] WHERE [Ecriture] = '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000859'

The connector takes the querie like :
SELECT `con_e_6`.`ID` FROM `con_e_6` WHERE (`Ecriture` = '00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000085 ) ;

(Look the end of the interpreted querie, the 2 last chars are missing because of the '.)

How to repeat:
Just make and sql querie with a string of 255 chars in the where clause and that pass by mysql connector of course.

The querie will prompt an ODBC call failed error.

Suggested fix:
Don't take count of the quotes in the string's char length (as the quotes don't make part of the string after all).

Or the querie should accept 257 chars (le string of 255 chars + the two quotes).

Well...after reflection, it's critical...

Hi and thanks for your report.

I checked latest 3.51GA and this is definitely not a driver problem as it works as expected in generic ODBC client:

SELECT * FROM dnevnik WHERE Opis = "0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008590123456789"

				Return:	SQL_SUCCESS=0

Get Data All:
"Rbr", "Godina", "Tip1", "Tip2", "Datum1", "Datum2", "Datum3", "Konto", "KOpis", "PB", "Ime", "Dok1", "Dok2", "Pot", "Dug", "Status", "Opis", "Marker"
0 rows fetched from 18 columns.

And in Logs:
080422 13:51:51	     76 Query       SELECT * FROM dnevnik WHERE Opis = "0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008590123456789"

Hi again. I must admit I do not understand the nature of your problem so I made test (above) with existing table that has VARCHAR fields of length less than 255. I used your string for matching (255 chars) and mine (320+ chars). All went fine, both in Access2003 and in Microsoft generic ODBC client.

Then I made a table:
mysql> create table bug36198( Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, SomeVChar VARCHAR(500));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into bug36198 values (NULL,"00000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000008590123456789");
Query OK, 1 row affected (0.00 sec)
...
and inserted 2 records, one your, one mine (longer string). Opened Access and linked table in question. All works fine. Added table via Query wizard, all works fine.

Please inspect your configuration and find out which SW/setting is trimming your SELECT as it is not MyODBC...

Well, first of all it seems to bug only in the 'where' clause.

I thought it was the mysql connector because the same query in a non linked access database or directly in the mysql database just work fine and only the linked access makes an "ODBC -- call failed". The only way I could fix that was to cut the strings has following :

Changing this kind of query : 
      SELECT * FROM mylinkedtable WHERE id='000000...001256' (where the id is 255 char long without the quotes)
Into : 
      SELECT * FROM mylinkedtable WHERE id='000000...00' + '0000...001256' (where the first string is like 100 char and the second 155 always without the quotes)

And the second one just works fine. 

If it can help, the Windows 2000 Pro is in French, I didn't change the configuration of the connector (or only to cross "Return Matching Rows", "Allow Big Results", "Change BIGINT Columns To INT" and "Save queries to myodbc.sql"), the version of MySQL is 5.0.51a and it's installed with WampServer.

Um, this sure sounds like some limitation in Access interpreter but I still don't know how to pass this exact query to Access. It only has "wizards"...
In any case I think others might benefit from your findings.

Thanks for your interest in MySQL.