MySQL Bugs: #24131: SHOW CREATE TABLE result truncated with mysql 3.23 and ODBC driver 3.51.12.00

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

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)

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.

vb project to test show create and text field read bugs

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

My DBA gave me this, seems to be my.cnf!

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

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".

I reopen this because sample project and my.cnf files are already present...

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>

Test case

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

SQL log

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

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 !)

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?

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.

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.

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".

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

Verified again on latest builds, both 3.51 & 5.1.

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.

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.

This bug is fixed in 3.51.26.

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.

bugfix (for reference)

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