Bug #32986 MyODBC recordset returns bad data and causes error in my_thread_global_end
Submitted: 4 Dec 2007 22:49 Modified: 5 Dec 2007 17:41
Reporter: Michael Pryor (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.22 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: show index

[4 Dec 2007 22:49] Michael Pryor
Description:
Retrieving rs("Key_name") (or any other column name) from a recordset returned from a "SHOW INDEX FROM tablename" call will yield bad memory data and mysql will exit with "Error in my_thread_global_end(): 2 threads didn't exit"

How to repeat:
For code to reproduce do the following:

mysql> create database test;
mysql> use test;
mysql> create table A ( b int );
mysql> create index areallylongindexnameistheoneiamlookingfor on A(b);

Create a file called test.vbs in any directory

Put this in it:
<%

Dim db: Set db = CreateObject("ADODB.Connection")
db.Open "DRIVER={MySQL ODBC 3.51
Driver};server=localhost;uid=root;pwd=abcdef;database=test;Option=3;"

Dim rs: Set rs = db.Execute("SHOW INDEX FROM A")
WScript.Echo rs("Key_name")
%>
Done.

From a command prompt: cmd> cscript.exe test.vbs

You should see something like ?????????????? and then after a second,
Error in my_thread_global_end(): 2 threads didn't exit

Expected to see "areallylongindexnameistheoneiamlookingfor"
[5 Dec 2007 7:28] Tonci Grgin
Test case

Attachment: 32986.vbs (application/octet-stream, text), 2.12 KiB.

[5 Dec 2007 7:38] Tonci Grgin
Hi Michael and thanks for your report. You actually reported two unrelated problems:
 1) Script output garbaged
 2) Threads not exiting

2) Please search bugsdb. This is known server problem which has been fixed. Also, related to this is bug#32366.

As for 1) it is quite more complex and related to bug#10491 originally. The point is in that "BINARY" flag in metadata is set disabling the client from distinguishing BLOB fileds (for example) from "ad hoc" queries like "SHOW ...". This problem has been described in so many bug reports, please search. I believe your data is returned in form of "0xareallylongindexnameistheoneiamlookingfor" which ADO can not translate to anything other than ??????? even though it's humanly readable.

Anyway, check out on my test case and post your remarks as I can't repeat described behavior on:
 MySQL server 5.0.54BK on WinXP Pro SP2 localhost
 MyODBC 3.51.22
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | latin1                      |
| character_set_connection | latin1                      |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | latin1                      |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\mysql507\share\charsets\ |
+--------------------------+-----------------------------+
[5 Dec 2007 7:53] Tonci Grgin
Same output in odbcte32.exe (MS generic ODBC client):

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbccln'.
SQLExecDirect:
				In:				hstmt = 0x00852098, szSqlStr = "SHOW INDEX FROM bug32386", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"Table", "Non_unique", "Key_name", "Seq_in_index", "Column_name", "Collation", "Cardinality", "Sub_part", "Packed", "Null", "Index_type", "Comment"
"bug32386", 0, "PRIMARY", 1, "Id", "A", 0, <Null>, <Null>, "", "BTREE", ""
"bug32386", 1, "areallylongindexnameistheoneiamlookingfor", 1, "Id", "A", <Null>, <Null>, <Null>, "", "BTREE", ""
"bug32386", 1, "areallylongindexnameistheoneiamlookingfor", 2, "VcFld", "A", <Null>, <Null>, <Null>, "YES", "BTREE", ""
"bug32386", 1, "shrtndxn", 1, "VcFld", "A", <Null>, <Null>, <Null>, "YES", "BTREE", ""
4 rows fetched from 12 columns.
[5 Dec 2007 15:56] Michael Pryor
Tonci is correct, although I've searched and searched for a workaround for myodbc and mysql 4 and can't figure one out.
[5 Dec 2007 17:41] Michael Pryor
here's some sample code to work around this bug

Function MySQLConvertByteArrayToString( f  ) 

	MySQLConvertByteArrayToString = f
	If TypeName(f) <> "Byte()" Then Exit Function

	Dim s: s = ""
	Dim i
	For i = 1 to UBound(f) + 1
		s = s & Chr(AscB(MidB(f, i, 1)))
	Next
	MySQLConvertByteArrayToString = s

End Function
[2 Jan 2008 9:21] Tonci Grgin
Michael, I am sorry for the late response. You can always use CAST(... AS CHAR) (or anything you need) too and/or upgrade to 5.0.48+ where certain related problems were fixed on server side.