| Bug #35741 | SHOW CREATE TABLE returns strange characters with ADO 2.8, Connector 3.51 andVB6 | ||
|---|---|---|---|
| Submitted: | 1 Apr 2008 14:52 | Modified: | 17 Nov 2008 7:48 |
| Reporter: | Leonardo Onieva | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.23 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | SHOW CREATE TABLE | ||
[1 Apr 2008 14:56]
Leonardo Onieva
Server Backup
Attachment: MySQL 20080401 1638.rar (application/octet-stream, text), 85.39 KiB.
[1 Apr 2008 20:53]
Valeriy Kravchuk
Thank you for a problem report. What exact version of Connector/ODBC, 3.51.xx, do you use?
[1 Apr 2008 23:07]
Leonardo Onieva
Connector/ODBC version 3.51.23
[11 Apr 2008 7:06]
Tonci Grgin
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.
[11 Apr 2008 7:08]
Tonci Grgin
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.
[14 Apr 2008 23:09]
Leonardo Onieva
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
[16 Apr 2008 9:27]
Tonci Grgin
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.
[16 Apr 2008 16:18]
Leonardo Onieva
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.
[29 Apr 2008 10:45]
Tonci Grgin
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.
[29 Apr 2008 10:49]
Tonci Grgin
Leonardo, this could be duplicate of Bug#35741, will see with devs.
[29 Apr 2008 15:54]
Leonardo Onieva
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
[29 Apr 2008 15:55]
Leonardo Onieva
odbcte32 Test file
Attachment: test.txt (text/plain), 8.79 KiB.
[30 Apr 2008 8:55]
Tonci Grgin
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?
[4 May 2008 12:00]
Leonardo Onieva
Tonci, I don't know how to do what you want. Please tell me what I have to do. Thank you
[4 May 2008 21:53]
Gerald Schade
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.
[4 May 2008 23:00]
Gerald Schade
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
[5 May 2008 19:38]
Gerald 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.
[16 May 2008 9:16]
Tonci Grgin
This is a duplicate of Bug#24131 which I already verified. Gerald, I think you'll find the answer there.
[17 Nov 2008 7:48]
Leonardo Onieva
Hi, do you think there will be a solition soon for this problem? Thank you
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 Ž Œ ;