| Bug #8100 | SQLForeignKeys returns bad results | ||
|---|---|---|---|
| Submitted: | 24 Jan 2005 3:57 | Modified: | 12 Aug 2005 9:15 |
| Reporter: | Luke Arms | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51.11-2 | OS: | Windows (Windows) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[24 Jan 2005 3:57]
Luke Arms
[25 Jan 2005 16:32]
Harun Eren
Hi, Thank you for your bug report. I have checked this and it is a reproduceable in myodbc3 3.51.10. Die ODBC-Funktion SQLForeignKeys() bringt ein schlechtes Resultat, ich haben geprüft dieses mit den folgenden Tabellen zurück: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; And the returns results by Connector/ODBC is: TRUNC: "", "", "parent`", "`id`", TRUNC: "", "", "child", "`parent_id`", 1, 1, 1, TRUNC: "", TRUNC: "", 7 The backticks are incorrectly returning in the result for identification the foreign keys. I am working on an solution for this bug. Thanks. Best Regards.
[25 Jan 2005 16:49]
Harun Eren
(sorry, new text) Hi, Thank you for your bug report. I have checked this and it is a reproduceable in myodbc3 3.51.10. The ODBC function SQLForeignKeys() returns a bad result, I have tested this with the following tables: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; And the returns results by Connector/ODBC is: TRUNC: "", "", "parent`", "`id`", TRUNC: "", "", "child", "`parent_id`", 1, 1, 1, TRUNC: "", TRUNC: "", 7 The backticks are incorrectly returning in the result for identification the foreign keys. I am working on an solution for this bug. Thanks.
[5 Feb 2005 13:01]
Luke Arms
Ok, for your information, I've tested the win32 binaries of MyODBC 3.51.06, 3.51.07, 3.51.10, and 3.51.10-2 against both win32 and linux binaries of MySQL 4.0 and 4.1 and get exactly the same result. This means that SQLForeignKeys is broken for all current database versions, so I'm escalating this to S1. Apparently InnoDB returns comments in a different format now than what it did in MySQL 3.23. As per example given, 3.23.49 returns the following comment field on SHOW TABLE STATUS: "InnoDB free: 4096 kB; (parent_id) REFER test/parent(id)" From 4.0, however, results look like this: "InnoDB free: 4096 kB; (`parent_id`) REFER `test/parent`(`id`)" As MyODBC uses SHOW TABLE STATUS within its SQLForeignKeys handler, I guess it isn't handling this difference correctly.
[26 Jun 2005 7:30]
Luke Arms
Has there been any progress on this one? It's been months since there was an update, and this bug completely breaks MyODBC's foreign key compliance with all current MySQL server versions. This means that all ODBC aware design/admin tools do not function correctly with MySQL!! If such interoperability remains unavailable for much longer, it'll be time for me (and therefore all of my clients) to shop for another database. All that's needed is some backtick handling ... :-S If I can help any further with this bug fix, please let me know. Thanks.
[12 Aug 2005 9:15]
Bogdan Degtyariov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
MyODBC driver executes "show table status" query and reads the information about foreign keys from the field "comment":
InnoDB free: 10240 kB; (`fk1`) REFER `dbtest/par`(`id`) ON UPDATE CASCADE;
Solution:
remove unclosed twigs from the table name:
par` ---> par
This fix will be applied in MyODBC 3.51.12
[29 May 2006 11:47]
Giovanni Gatto
Hi i have some problem with importing foreign keys in MS Visio 2003. I tried MySQL ODBC Driver Version 3.51.12 on Windows, which CHANGELOG states: "fixed issue with SQLForeignKeys() and quotes" but I still cannot import foreign keys. I don't know if that fix refers to this bug or not. Has this bug been fixed or what?
[7 Jun 2006 12:09]
Bogdan Degtyariov
Giovanni, I have verified this bug again with MyODBC 3.51.12. Everything works well. Probably your situation depends on the table structures. Could you upload a simplified SQL file with table schema and some data that illustrates the problem? Thank you.
[30 Aug 2006 7:08]
Giovanni Gatto
Hi, Bogdan. Sorry I couldn't answer your question earlier... So, here we go with my tables: ------------------- CREATE TABLE `lingue` ( `id` int(11) NOT NULL auto_increment, `nome` varchar(20) NOT NULL default '', `nome_estero` varchar(50) NOT NULL default '', `bandiera` varchar(25) NOT NULL default '', `pubblicato` tinyint(4) NOT NULL default '0', `sigla` char(3) NOT NULL default '', `fascia` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB AUTO_INCREMENT=1 ; -- -- Dump dei dati per la tabella `lingue` -- -- -------------------------------------------------------- -- -- Struttura della tabella `mod_news` -- CREATE TABLE `mod_news` ( `id` int(11) NOT NULL auto_increment, `nome` text NOT NULL, `stato` tinyint(4) NOT NULL default '0', `data_creazione` datetime NOT NULL default '0000-00-00 00:00:00', `data_inizio` datetime default NULL, `data_fine` datetime default NULL, `posizione` tinyint(4) NOT NULL default '0', `immagine_dettaglio` varchar(255) default NULL, `immagine_piccola` varchar(255) default NULL, `data_modifica` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) TYPE=InnoDB AUTO_INCREMENT=1 ; -- -- Dump dei dati per la tabella `mod_news` -- -- -------------------------------------------------------- -- -- Struttura della tabella `mod_news_desc` -- CREATE TABLE `mod_news_desc` ( `id_news` int(11) NOT NULL default '0', `id_lingua` int(11) NOT NULL default '0', `titolo` varchar(255) default NULL, `testo` mediumtext, `attiva` int(11) NOT NULL default '0', PRIMARY KEY (`id_news`,`id_lingua`), KEY `id_lingua` (`id_lingua`) ) TYPE=InnoDB; -- -- Dump dei dati per la tabella `mod_news_desc` -- -- -- Limiti per le tabelle scaricate -- -- -- Limiti per la tabella `mod_news_desc` -- ALTER TABLE `mod_news_desc` ADD CONSTRAINT `mod_news_desc_ibfk_1` FOREIGN KEY (`id_lingua`) REFERENCES `lingue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `mod_news_desc_ibfk_2` FOREIGN KEY (`id_news`) REFERENCES `mod_news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ----------------------------------------------- When I try to import in MS Visio 2003 with reverse eneineering via ODBC I'm getting an "Error in function sequence" for every foreign key it tries to extract and none gets extracted... Please let me know if this works for you (I'm starting to suspect the error could be in Visio). T.I.A. Giovanni
[31 Aug 2006 10:07]
Giovanni Gatto
Moreover here's what i found in the odbc trace file. ------------------------------------------------------- VISIO f74-b08 ENTER SQLForeignKeysW HSTMT 04941938 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x04942290 [ -3] "INNOD" SWORD -3 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x04941CA0 [ -3] "mod_new" SWORD -3 VISIO f74-b08 EXIT SQLForeignKeysW with return code 0 (SQL_SUCCESS) HSTMT 04941938 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x04942290 [ -3] "INNOD" SWORD -3 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x04941CA0 [ -3] "mod_new" SWORD -3 ---------------------------------------------------- It seems like the arguments to SQLForeignKeysW are somewhat truncated (???) Hope this can help you to get an idea of what the problem may be. By the way, i'm having some problem trying to install/test the Connect/ODBC 5.0 driver too... :(
[1 Sep 2006 12:58]
Bogdan Degtyariov
Giovanni,
here is output from mysql command line client:
mysql> show table status like "mod_news_desc%"\G
*************************** 1. row ***************************
Name: mod_news_desc
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2006-09-01 15:46:31
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 32768 kB; (`id_lingua`) REFER `test2/lingue`(`id`) ON UPDATE CASCAD
1 row in set (0.00 sec)
as you can see Comment doesn't contain full string. It is a restriction of innodb tables as TABLE_COMMENT field in INFORMATION_SCHEMA.TABLES is varchar(80). This causes MyODBC to parse this string wrongly. I'll push on developers in order to provide full and correct metadata information.
[4 Sep 2006 6:43]
Giovanni Gatto
Thank you, Bogdan. Personally I don't think storing fk relations in the comment field has been a good choice by the developers... So my problem doesn't seem to be a real Connector/ODBC problem, does it? Is this problem still present even in Mysql 5.0 or just in older releases?
[29 Nov 2006 10:03]
Lasse Steensgard
It says status = closed, but the problem still exists, doesn't it?! I experience the exact same things as described in this thread - now November 2006... What is the solution?
[30 Nov 2006 11:26]
Bogdan Degtyariov
This bug is related to the restrictions of InnoDB Engine, which has only 80 symbols for comment. As MyODBC connector gets information about Foreign Keys from SHOW TABLE STATUS query, when key names are long, they may exceed 80 characters limit and the result of SQLForeignKeys() can be unpredictable. MyODBC 5 should get this information in other way, so the bug #8100 has been solved in newer version.
[13 Nov 2007 0:52]
eric s
so has the innodb engine been corrected to resolve this behaviour?
