MySQL Bugs: #27409: MyODBC 3.51.14 Returns garbage values with SHOW COLUMNS

Bug #27409	MyODBC 3.51.14 Returns garbage values with SHOW COLUMNS
Submitted:	23 Mar 2007 21:23	Modified:	11 Apr 2008 16:36
Reporter:	Matt Woody
Status:	Duplicate
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51.14	OS:	Microsoft Windows (Windows XP)
Assigned to:		Target Version:
Tags:	garbage, incorrect, columns, show, 3.51.14, MyODBC

Description:
When using an ADODB connection from Windows XP to a mysql database on Windows Server
2003, the command "SHOW COLUMNS FROM `x`" does not return useful information.  Instead it
returns the field types (Field, Type, Key, Default,...) but the actual value is
unprintable characters (possibly multibytes?)

It is repeatable with mySQL 4.1.10 and 4.1.14, but does not occur in 4.0.14 or 5.0.18.

How to repeat:
1.  Install mySQL 4.1.10 using defaults on Server 2003, attached are the ini settings
2.  Create a VB project with rough coding like this

Private prefs_db As New ADODB.Connection
my_sql = "SHOW COLUMNS FROM `mytable`"
Set search = prefs_db.Execute(my_sql)

3.  Just FYI, the command "SHOW TABLES" works fine

mysql ini file

Attachment: my.ini (application/octet-stream, text), 8.89 KiB.

Hi Matt and thanks for your report.

> It is repeatable with mySQL 4.1.10 and 4.1.14, but does not occur in 4.0.14 or 5.0.18.
Did you mean "but does not occur in 4.0.15" (there were some changes in 4.1.15 and up)?

Please paste output from:
mysql -uroot -T testdb
show columns from my_table

for both 4.1.14 and 5.0.18.

Is this problem repeatable with any table/engine I may choose?

If I'm not mistaken, here's what's the problem:
 - Please see Bug#10491. The result is returned as binary... I don't see how the driver
can work around it so we'll have to wait until it's fixed in server.

As for driver, it works correctly (test done with MS generic ODBC client):

	Full Connect(Use Driver)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbc1'.
SQLExecDirect:
				In:				hstmt = 0x008B1960, szSqlStr = "show tables", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"Tables_in_test"
"a"
"aaa"
"ab"
"ab1"
"b"
"data1"
"datetimetest"
"tbug"
"test"
"test1"
"test18531"
"test2"
"vtest"
"x_booltest"
108 rows fetched from 1 column.

SQLExecDirect:
				In:				hstmt = 0x008B1960, 
										szSqlStr = "show columns from test20135", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"Field", "Type", "Null", "Key", "Default", "Extra"
"idfield", "int(11) unsigned", "NO", "PRI", <Null>, "auto_increment"
"dfield", "varchar(1)", "YES", "", <Null>, ""
2 rows fetched from 6 columns.

SQLColumns:
				In:				StatementHandle = 0x008B1960, CatalogName = "test", 
										NameLength1 = 4, SchemaName = "test", NameLength2 = 4, TableName = "test20135",

										NameLength3 = 9, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
				Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
"COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE",
"REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
"ORDINAL_POSITION", "IS_NULLABLE"
"test", "", "test20135", "idfield", 4, "integer unsigned", 11, 4, 0, 10, 0, "", <Null>,
4, <Null>, <Null>, <Null>, "NO"
"test", "", "test20135", "dfield", 12, "varchar", 1, 1, <Null>, <Null>, 1, "", <Null>,
12, <Null>, 1, <Null>, "YES"
2 rows fetched from 18 columns.

Even VB project in VS2005Pro works correctly:
    Dim cx As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    cx.ConnectionString = "DSN=myodbc1sd" 'Or whatever
    cx.Open()
    rs.Open("SHOW COLUMNS FROM solusd.dnevnik", cx, CursorTypeEnum.adOpenForwardOnly,
LockTypeEnum.adLockOptimistic)
    Dim oField As ADODB.Field
    Dim oStr As String
    oStr = ""
    Do Until rs.EOF
        For Each oField In rs.Fields
            oStr = oStr & (oField.Name & ": " & oField.Value) & vbCrLf
            If oField.Name = "Extra" Then
                oStr = oStr & vbCrLf
            End If
        Next
        Console.Write(oStr)
        oStr = ""
        rs.MoveNext()
    Loop
    rs.Close()
Output:
Field: Rbr
Type: int(10) unsigned
Null: NO
Key: PRI
Default: 
Extra: auto_increment

Field: Godina
Type: year(4)
Null: NO
Key: MUL
Default: 2002
Extra: 

Field: Tip1
Type: char(3)
Null: YES
Key: MUL
Default: 
Extra: 

Field: Datum1
Type: date
Null: YES
Key: MUL
Default: 
Extra: 

Field: Konto
Type: char(10)
Null: NO
Key: MUL
Default: 
Extra: 

Field: PB
Type: char(13)
Null: YES
Key: MUL
Default: 
Extra: 

Field: Dok1
Type: char(25)
Null: YES
Key: MUL
Default: 
Extra: 

Field: Pot
Type: decimal(12,2)
Null: NO
Key: 
Default: 0.00
Extra: 

Field: Marker
Type: char(3)
Null: YES
Key: MUL
Default: 
Extra: 

But VB 6 returns error: Run time error '424' object required
most probably because of binary content... 

Probably VS does something smarter than VS.

That's probably it, the column names are binary encoded.  Since I am only using VB6, is
there an easy way to know the data is in binary and then encode it back to regular text? 
Maybe add something to the connection string to force the charset conversion?  I'm not a
pro programmer, so there may be something obvious that I could do to fix it.

I would add that the only reason I found this was trying a new version of the connector. 
The older version I had did not recognize the new password encryption, so I had been
setting all the passwords with "old_password('pwd')", but one of my friends said the new
connector fixed it.  After downloading the new one, my program could no longer check for
the presence of columns.  Interestingly enough, there is a middle version that fixes the
password issue and does not have the problem with the column encoding.

On the other question about mySQL versions, I only tested on the versions I had
available.  So I can't say exactly which version the "bug" appeared.

Thanks

Matt
Matt

hi!

i am experiencing the same thing,
with odbc connector 3.51.23 and 4.1.20 server a
SHOW COLUMNS statement returns binary data instead of varchar.

with server 5.0.45 SHOW COLUMNS works fine.

is there a workaround?
thx.
michael b.

"with server 5.0.45 SHOW COLUMNS works fine."
As I said before, this is a server problem and I doubt this fix will be backported to
4.x. Usual workaround is using CAST which is not available for ad-hoc queries like this,
sorry... I think you can only write your own parsing routine.

hi.
you suggested a parsing routine. as i still use vb6.0 with ado
where would you see this be located?
thx,
michael b.

Michael, to be honest, never thought about it... it just seemed logical thing to do.
Server and connector sources are open and you're welcome to do any change that suit your
needs (even upgrade to VS2005).

Thanks for your interest in MySQL.

I thought I would just add a final comment, I finally figured out how to work around this.
 Just add a charset to the connection string as shown below.

open_str = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51
Driver};Port=3306;charset=utf8;.....

Hope that helps someone in the future.

Matt