Bug #24131 SHOW CREATE TABLE result truncated with mysql 3.23 and ODBC driver 3.51.12.00
Submitted: 9 Nov 2006 9:30 Modified: 11 Jul 2008 14:45
Reporter: yann le gloinec Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.25, 5.1.4 OS:Microsoft Windows (Windows XP)
Assigned to:
Tags: 3.51, ADO, MyODBC, regression, SHOW CREATE
Triage: D1 (Critical)

[9 Nov 2006 9:30] yann le gloinec
Description:
(sorry for my poor english)
We use Visual Basic 6.0 and ADO 2.8 and mysql 3.23.49 (I know, it's very old)

When doing a "SHOW CREATE TABLE test", and then reading the field value, if the tables has many rows and indexes, the result is truncated and can't be used.

This happens on all our computers with this version of the driver, not with the previous version of the driver

How to repeat:
szCnnString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;UID=root;PASSWORD=;OPTION=3"
MysqlCon.Open szCnnString

Set Rs = MysqlCon.Execute("SHOW CREATE TABLE test", lRecords)
Text1.Text = Replace(Rs![Create Table], vbLf, vbCrLf)
[10 Nov 2006 12:57] Tonci Grgin
Hi Yann and thanks for your problem report. Can you please post sample project demonstrating this behavior? I would like to see my.ini/cnf attached to report too.
[10 Nov 2006 15:05] yann le gloinec
vb project to test show create and text field read bugs

Attachment: bugs mysql.zip (application/x-zip-compressed, text), 1.88 KiB.

[13 Nov 2006 13:31] yann le gloinec
My DBA gave me this, seems to be my.cnf!

Attachment: confmysql.dump (application/octet-stream, text), 2.43 KiB.

[11 Dec 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Dec 2006 12:58] yann le gloinec
I reopen this because sample project and my.cnf files are already present...
[11 Dec 2006 14:13] Tonci Grgin
Hi Yann. I was able to verify this problem with test case attached and table structure below. Value of `Create Table` field is garbage. I'm using 3.51.14 (not released yet).

C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.0.27-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table a;
Field   1:  `Table`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     64
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY

Field   2:  `Create Table`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     1024
Max_length: 178
Decimals:   31
Flags:      NOT_NULL BINARY

+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------+
| Table | Create Table

                             |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------+
| a     | CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(30) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------+
1 row in set (0.00 sec)

mysql>
[11 Dec 2006 14:14] Tonci Grgin
Test case

Attachment: Bug24131.vbs (application/octet-stream, text), 746 bytes.

[11 Dec 2006 14:14] Tonci Grgin
SQL log

Attachment: Bug24131SQL.zip (application/zip, text), 2.71 KiB.

[18 Apr 2007 14:25] zumba lavache
Hello,
I have exactly the same problem with mySQL 5 and ODBC driver 3.51.14 in a VB6 application running on windows 2003 server. Can you please provide news and support about this ? I can'do anything without a corrective patch !
(sorry for my poor english too, Mr Le Gloinec if you read this can you contact me on beta AT the-continent.org, speaking in french if it's your language !)
[17 May 2007 12:14] Brendan Taylor
I am running a VB6 application, using MyODBC to connect to MySQL 4.1.x. I have been using MyODBC 3.51.11 with no problem. I tried to upgrade to 3.51.15 and suddenly the SHOW CREATE TABLE statement returned a long string with mostly '?' characters. It sounds like the same problem that's been described in this thread. I suspect it's related to character encoding. Any update on this issue?
[17 May 2007 12:57] Tonci Grgin
Brendan, please do search bugs db for bugs reported against MyODBC 3.51.15. This is a side effect of Bug#10491 and a perfectly good patch... Your values are returned in form of "0xACTUALSTRING" and your comment does not belong here.
[23 Jul 2007 11:53] Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Both original and Brendan's problems work as expected in 3.51.17

Thank you all for your interest in MySQL.
[23 Apr 2008 14:50] Tonci Grgin
While testing Bug#35741 I've found that bug reappeared. I used table clientes from that report:
Cn.Execute "CREATE TABLE  `imprenta`.`clientes` ( " & _
          "  `Codigo` int(10) unsigned NOT NULL auto_increment, " & _
          "  `Nombre` varchar(255) character set latin1 default NULL, " & _
          "  `Telefono` varchar(255) character set latin1 default NULL, " & _
          "  `Observaciones` longtext character set latin1, " & _
          "  `Direccion` varchar(255) character set latin1 default NULL, " & _
          "  `Dni` varchar(255) character set latin1 default NULL, " & _
          "  `CP` int(11) default NULL, " & _
          "  `Provincia` varchar(255) character set latin1 default NULL, " & _
          "  `Poblacion` varchar(255) character set latin1 default NULL, " & _
          "  PRIMARY KEY  (`Codigo`) " & _
          ") ENGINE=InnoDB ...

Last thing VBS gives me is "Poblaci".
[23 Apr 2008 14:52] Tonci Grgin
Option Explicit
Const DSN = "Uid=root;Pwd=*****;Driver={MySQL ODBC 5.1 Driver}; Server=localhost; Database=test; OPTION=3"

Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SHOW CREATE TABLE `clientes` "

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .Open(strSQL)
End With

WScript.Echo rs.Fields(1).Name 'Create Table
WScript.Echo rs.Fields(1).Type

WScript.Echo rs(0)
WScript.Echo rs(1)
WScript.Echo (rs("Create Table").Value)

rs.Close
cnxDatabase.Close

Set rs = nothing
Set cnxDatabase = nothing
------------
mysql> show create table clientes\G
*************************** 1. row ***************************
       Table: clientes
Create Table: CREATE TABLE `clientes` (
  `Codigo` int(10) unsigned NOT NULL auto_increment,
  `Nombre` varchar(255) default NULL,
  `Telefono` varchar(255) default NULL,
  `Observaciones` longtext,
  `Direccion` varchar(255) default NULL,
  `Dni` varchar(255) default NULL,
  `CP` int(11) default NULL,
  `Provincia` varchar(255) default NULL,
  `Poblacion` varchar(255) default NULL,
  PRIMARY KEY  (`Codigo`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
[16 May 2008 9:13] Tonci Grgin
Verified again on latest builds, both 3.51 & 5.1.
[10 Jun 2008 8:14] Tonci Grgin
Umm, we did hit several problems here:
  o) ADO appears to be truncating ECHO output
  o) There seems to be a server bug here. I opened Bug#37301 for this

Although we're convinced there's server bug here, c/ODBC team might decide to work around it.
[16 Jun 2008 18:26] Lawrenty Novitsky
Patch for this bug in 3.51 driver have been pushed as revision 1120 (reviewed/approved by jimw and jbalint via email). Problem in 5.1 is yet to be solved.
[9 Jul 2008 19:11] Jim Winstead
This bug is fixed in 3.51.26.
[11 Jul 2008 14:45] Tony Bedford
An entry has been add to the 3.51.26 changelog:

When SHOW CREATE TABLE was invoked and then the field values read, the result was truncated and unusable if the table had many rows and indexes.
[11 Nov 2008 19:13] Jess Balint
bugfix (for reference)

Attachment: bug24131.diff (application/octet-stream, text), 2.76 KiB.