MySQL Bugs: #35741: SHOW CREATE TABLE returns strange characters with ADO 2.8, Connector 3.51 andVB6

Bug #35741	SHOW CREATE TABLE returns strange characters with ADO 2.8, Connector 3.51 andVB6
Submitted:	1 Apr 2008 16:52	Modified:	17 Nov 2008 8:48
Reporter:	Leonardo Onieva
Status:	Duplicate
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	3.51.23	OS:	Microsoft Windows (XP)
Assigned to:		Target Version:
Tags:	SHOW CREATE TABLE

Description:

When I execute command "SHOW CREATE TABLE" from an ADODB.connection returns strange
characters and do not return full sentence. This case only occurs with ADO 2.8 and VB6,
if try in Query Browser it works right.

Regards

How to repeat:
open a new VB6 Proyect
 
'Create a new Module and paste this code
Sub Main()

Dim Cn As New ADODB.Connection
Dim rssAux As New ADODB.Recordset

Cn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;
DATABASE=imprenta; UID=root;PWD=1234"
Cn.CursorLocation = adUseClient
Cn.Open , , , adConnectUnspecified

Cn.Execute "CREATE DATABASE `imprenta` /*!40100 DEFAULT CHARACTER SET latin1 */;"

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 AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
COLLATE=latin1_spanish_ci ROW_FORMAT=DYNAMIC;"

Cn.Execute "CREATE TABLE  `imprenta`.`productos` ( " & _
          "  `Codigo` varchar(255) NOT NULL, " & _
          "  `Producto` varchar(255) default NULL, " & _
          "  `NumTroquel` varchar(255) default NULL, " & _
          "  `Referencia` varchar(255) default NULL, " & _
          "  `OBS` longtext, " & _
          "  `Precio` varchar(255) default NULL, " & _
          "  `Prevision` int(11) default NULL, " & _
          "  `Medida` varchar(50) default NULL, " & _
          "  `Marca` varchar(50) default NULL, " & _
          "  PRIMARY KEY  (`Codigo`) " & _
          ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"

'Choose one of this 2 lines
'rssAux.Open "SHOW CREATE TABLE imprenta.clientes", Cn
Set rssAux = Cn.Execute("SHOW CREATE TABLE imprenta.clientes")

Debug.Print "Table Clientes"
Debug.Print rssAux.Fields.Item("Create Table").Value
Debug.Print ""

'Choose one of this lines
'rssAux.Open "SHOW CREATE TABLE imprenta.Productos", Cn
Set rssAux = Cn.Execute("SHOW CREATE TABLE imprenta.Productos")

Debug.Print "Table Productos"
Debug.Print rssAux.Fields.Item("Create Table").Value

Stop 'push Ctrl + G to show console window
End Sub

This is the result:

CREATE TABLE `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                Ž                                         
                                                                                          
                                                                                          
                                                                 ;

CREATE TABLE `productos` (
  `Codigo` varchar(255) NOT NULL,
  `Producto` varchar(255) default NULL,
  `NumTroquel` varchar(255) default NULL,
  `Referencia` varchar(255) default NULL,
  `OBS` longtext,
  `Precio` varchar(255) default NULL,
  `Prevision` int(11) default NULL,
  `Medida` varchar(50) default NULL,
  `Marca` varchar(50) defau               Ž       Œ                                    
            ;

Server Backup

Attachment: MySQL 20080401 1638.rar (application/octet-stream, text), 85.39 KiB.

Thank you for a problem report. What exact version of Connector/ODBC, 3.51.xx, do you use?

Connector/ODBC version 3.51.23

Hi Leonardo.

Just noting, server version is 5.0.51a-community-nt.

Please, next time attach privately such long comments as a file, do not paste it please.

Now, I do not have VB6 available to me... sorry but we can't just have any 3rd party SW
there is and I can tell, from experience, VS2005 will work correctly :(

We have several problems at once here:
  1) Ad-hoc queries like "SHOW CREATE", please see problems listed in Bug#10491. In
short, such queries do not allow connector to distinguish between
"SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM
some_table", where it really should be binary. I think proper fix is still not in 5.0.51.
If you can, please build MySQL server from sources and retest.
  2) ADO: ADO is known to have a problem or two in this area.
  3) VB: Obviously there are problems here as almost all of test cases reported as
failing in VB work for me in VS2005.

Now for your problem. Can you please try same thing with MyODBC 5.1.3 and inform me of
result? Also, can you try using "odbcte32.exe" (with 3.51) or "odbcte32w.exe" (with 5.1)
and tell me what you see there? Those are default Microsoft ODBC clients and if there is
an error in driver they will show it. Next thing would be to explain your connection,
please paste output from mysql command line client when you execute: SHOW VARIABLES LIKE
"char%". And finally, it would be good to attach ODBC trace file generated during your
test case (search manual / bugsdb on how to get that).

My output from odbcte32.exe, MyODBC 3.51.24 & MySQL server 5.0.58PB shows no such error:
show create table a
				Return:	SQL_SUCCESS=0

Get Data All:
"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=31 DEFAULT CHARSET=utf8"
1 row fetched from 2 columns.

Leonardo, why did you attach mysql database? It's useless here. Next time you may attach
my.ini / my.cnf file, it will show more.

Hi Tonci 

Thank you for your comment.

This is my first bug report so, sorry me if I don't fill bug form properly.

Sometimes it's difficult to update old proyects in VB6 to VS2005. Anyway, I have tryed in
VS2005 too and I think this problem should be fixed cause there are still too many people
useing VB6 and MySQL, I don't think I'm the only one.

I have tryed MySQL 6.0.4-alpha community, MySQL Connector 5.1 with both VB6 and VS2005,
and the problems increases. Now if I use ADODB.CursorLocation = adUseClient, in both VB6
and VS2005 return error "Data provider or other service returned an E_FAIL status", this
problem is commented in other bug http://bugs.mysql.com/bug.php?id=1885 but I haven't see
relation to my problem.
If I use CursorLocation = adUseServer, VB6 directly crashes when I try to get EOF value
of a recordset, and VS2005 return error "Attempted to read or write protected memory.
This is often an indication that other memory is corrupt." when I try to get any field
value of returned query.

If yoy tryed with your test "CREATE TABLE `a` (  `id` int(10) unsigned NOT NULL
auto_increment,  `name` varchar(30) default NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM
AUTO_INCREMENT=31 DEFAULT CHARSET=utf8" It is sure you will not have problems because
this is a very simple table definition, I have tables with a small number of fields and
the SHOW CREATE TABLE command returns correct sentence, but if you try the example I have
provided (table Productos or Clientes) probably you will have the same problem like me,
the probability increases if you try with VB6 :)

About tests with odbcte32.exe and odbcte32w.exe I have tryed to connect to my server but
I don't know how. If you could explain the proccess I will try again.

Tahnk you

Regards

Leonardo, no problem.

It is not complexity of table that matters (I tested your tables too, no problems
detected) but, I think, data mapping as Microsoft does not recognize existence of *any*
type but their own... For example, Visual Basic with ADO can't handle big integers. This
means that some queries like SHOW PROCESSLIST do not work properly. The fix is to use
OPTION=16384 in the ODBC connect string or to select the Change BIGINT columns to INT
option in the Connector/ODBC connect screen. There are more problematic fields, like
LONGsomething, which are not recognized by MS tools/frameworks (they limit field size to
UINT32).

As for ODBCTE... programs, they are part of VS2005 installation but I think you can find
them in resource kits and / or as separate downloads on Microsoft site. Mine are in:
"C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\Bin\odbcte32.exe"

Please try repeating the problem with MyODBC 5.1 and inform me of results.

Tonci, thank you for your comments.

I have added option = 16384 in my connection string, but problem still persist. 

I have dropped fields with long data like Longtext, and as you can see in the example, I
haven't other fields like BigInt or LONGsomething, and same problem.

'productos', 'CREATE TABLE `productos` (
  `Codigo` varchar(255) NOT NULL,
  `Producto` varchar(255) default NULL,
  `NumTroquel` varchar(255) default NULL,
  `Referencia` varchar(255) default NULL,
  `Precio` varchar(255) default NULL,
  `Prevision` int(11) default NULL,
  `Medida` varchar(50) default NULL,
  `Marca` varchar(50) default NULL,
  PRIMARY KEY  (`Codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

SHOW CREATE TABLE imprenta.productos;

CREATE TABLE `productos` (
  `Codigo` varchar(255) NOT NULL,
  `Producto` varchar(255) default NULL,
  `NumTroquel` varchar(255) default NULL,
  `Referencia` varchar(255) default NULL,
  `Precio` varchar(255) default NULL,
  `Prevision` int(11) default NULL,
  `Medida` varchar(50) default NULL,
  `Marca` varchar(50) default NULL,
  PRIMAR               Ž       Œ                              ;

I've seen bug 10491 you commented me from 11 Apr 9:06, and in this bug there is a patch
in http://lists.mysql.com/commits/29712, installed BitKeeper, but I can't find it, could
you please attach it in this post or tell me the easyest way to solve this problem
please? 

I have tryed too setting different character set like utf8, ascii, hp8, binary, SET
character_set_results = NULL ... and again same problem.

About ODBCTE, you didn't understood me or I didn't explain very well, sorry, I found it
when I send last post, but I don't know how to connect to my server beacuse there are to
many menu options, I've tryed put connection string in some options but can't connect.

Thank you

Regards.

Leonardo, click shaking hands (third button from the left) and point odbcte to DNS you
want to use.
Please use odbcte32w for MyODBC 5.1 as it's unicode capable.

Waiting on your result.

Leonardo, this could be duplicate of Bug#35741, will see with devs.

Hi, ok, I have connected to server version 5.0.51a with odbcte32 and connector 3.51.24,
this is the test. I hope this is more helpfull for you.

Regards

odbcte32 Test file

Attachment: test.txt (text/plain), 8.79 KiB.

Leonardo, this is a ODBC DM trace file, I was only interested if you can see entire
information in odbcte32 result window. My presumption is that VB (and VBS) cut part of
answer not the MyODBC driver. So, can you see all of create statement n odbcte32?

Tonci, I don't know how to do what you want. Please tell me what I have to do.
Thank you

I have just the same problem, with vb6, and with either actual connector 3.51 or 5.1.4.
Some months ago, the problem disappeared with some former beta-Version of Connector 5.1,
reappeared with 3.51 and disappeared again with 5.1.x, but I don't remember which
beta-Version and of course I don't know if it has anything to do with the Connector
itsself.

Now I couldn't reproduce the remission of the bug with odbc connectors 5.1.0, 5.1.1, 5.1.2
and 5.1.3 (and 5.1.4).
When alternatively I try to get the questioned information about the constraints by
"openschema(adSchemaForeignKeys, Array(Empty, Empty, UCase(tb), Empty, Empty, Empty))",
this gives me only one recordset with information on one constraint instead of three
existing for the table and in addition only the call "openschema" takes about 20 seconds
on a fast computer.
With kind regards, G.Schade

P.S.:
In Visual Basic 2008 Express Edition, the "show create table"-error persits, using a
Connection String like:
"Provider=MSDASQL.1;Extended Properties=""DRIVER={MySQL ODBC 5.1
Driver};UID=...;PWD=...;server=LINUX;option=3;database=...;"" ".
Besides, my above mentioned openschema-insufficiency also persists.

This is a duplicate of Bug#24131 which I already verified.

Gerald, I think you'll find the answer there.

Hi, do you think there will be a solition soon for this problem?
Thank you