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 20:23	Modified:	11 Apr 2008 14:36
Reporter:	Matt Woody	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.14	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	3.51.14, columns, garbage, incorrect, MyODBC, show

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

Praise the Lord that I found this topic! I've spent my entire weekend to get a solution for this, I almost gave up my creating some dirty work arround. 

This problem was SO hard to pin down because "SHOW TABLES" works fine but "SHOW FIELDS" gives back question marks and "?????d" kind of fields which make perfectly sense if the output is binary! I'm using 4.1.18 by the way...

For anyone that wants to solve this issue in ASP Classic
Use a Binary to String function (you can Google it fairly easily) or... if you're lazy, here's the function I'm using from Antonin Foller:

Function BinaryToString(Binary)
  Dim cl1, cl2, cl3, pl1, pl2, pl3
  Dim L
  cl1 = 1
  cl2 = 1
  cl3 = 1
  L = LenB(Binary)
  
  Do While cl1<=L
    pl3 = pl3 & Chr(AscB(MidB(Binary,cl1,1)))
    cl1 = cl1 + 1
    cl3 = cl3 + 1
    If cl3>300 Then
      pl2 = pl2 & pl3
      pl3 = ""
      cl3 = 1
      cl2 = cl2 + 1
      If cl2>200 Then
        pl1 = pl1 & pl2
        pl2 = ""
        cl2 = 1
      End If
    End If
  Loop
  BinaryToString = pl1 & pl2 & pl3
End Function

Works like a charm now!