| Bug #51232 | Create multiple tables with foreign key in ONE query doesn't work | ||
|---|---|---|---|
| Submitted: | 17 Feb 2010 8:11 | Modified: | 17 Jan 2017 19:25 |
| Reporter: | francois pinchard | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.43, 5.1, 5.5.99-m3 | OS: | Any (MS Windows, Linux) |
| Assigned to: | Jimmy Yang | CPU Architecture: | Any |
| Tags: | CREATE TABLE, foreign key | ||
[17 Feb 2010 8:11]
francois pinchard
[18 Feb 2010 23:39]
MySQL Verification Team
Thank you for the bug report. Could you please try with mysql command client and print here what you got. Thanks in advance.
[19 Feb 2010 9:42]
francois pinchard
hi, and thanx for your reply. the script works with Mysql command line, because it executes each create statment separatly : -------------------------- mysql> \. script.txt Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> --------------------------- Perhaps it is because it's forbiden to execute multiple CREATE statment in one shot. And perhaps its a problem with "WinDev" Calls to "libmySQL.dll". But it's strange that it worked with V5.0.xx Of mysql server, and that it works in v5.1 if I remove foreign key constraint...
[11 Mar 2010 7:23]
Sveta Smirnova
Thank you for the feedback. Verified as described, although I think this is rather C API bug than server one. C test case to repeat the problem: $cat bug51232.c #include <stdio.h> #include <mysql.h> #include <assert.h> #include <string.h> #include <time.h> int main(int argc, char **argv) { MYSQL conn; MYSQL_RES *result; MYSQL_FIELD *field; int OK; int count = 0; int timeout = 10; struct tm *current; time_t now; const char* query1= "SET NAMES 'utf8'; DROP TABLE IF EXISTS `T_COMPTEPARAM_CPP`; CREATE TABLE `T_COMPTEPARAM_CPP` ( `CPP_ID` int(11) NOT NULL auto_increment, `CPP_NumeroCompte` varchar(20) default NULL, `CPP_Inactif` tinyint(4) default NULL, `CPP_BDD` int(11) default NULL, `CPP_CPT` int(11) default NULL, `CPP_Lib` varchar(50) default NULL, `CPP_Abrege` varchar(10) default NULL, `CPP_Bord` tinyint(4) NOT NULL, PRIMARY KEY (`CPP_ID`), UNIQUE KEY `CPP_ID` (`CPP_ID`), UNIQUE KEY `IX_CL_UNIK_CPP_CPT_BDD` (`CPP_CPT`,`CPP_BDD`), KEY `WDIDX120306542748` (`CPP_BDD`), KEY `WDIDX120306542849` (`CPP_CPT`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `T_ECRITURE_ECR`; CREATE TABLE `T_ECRITURE_ECR` ( `ECR_ID` int(11) NOT NULL auto_increment, `ECR_NumCpt` int(11) default NULL, `ECR_DateCpt` datetime default NULL, `ECR_NumDepRec` int(11) default NULL, `ECR_DateDepRec` datetime default NULL, `ECR_Lib` varchar(80) default NULL, `ECR_LibObs` varchar(80) default NULL, `ECR_LibRectif` varchar(80) default NULL, `ECR_TEC` varchar(20) default NULL, `ECR_NumQuittance` int(11) default NULL, `ECR_NumServ` int(11) default NULL, `ECR_DateQuittance` datetime default NULL, `ECR_Valide` tinyint(4) default NULL, `ECR_Devers` int(11) default NULL, `ECR_GUI` int(11) default NULL, `ECR_NoFichTitre` int(11) default NULL, `ECR_DateNoFicheTitre` datetime default NULL, `ECR_NoLot` int(11) default NULL, `ECR_DateLot` datetime default NULL, `ECR_FIC` int(11) default NULL, `ECR_ADR` int(11) default NULL, `ECR_DateArrive` datetime default NULL, `ECR_BDD` int(11) default NULL, `ECR_LibMotif` varchar(80) default NULL, `ECR_LibNature` varchar(80) default NULL, `ECR_Anouveau` varchar(1) default NULL, `ECR_ID_Contrepassation` int(11) default NULL, `ECR_Contrepassation` tinyint(4) NOT NULL default '0', `ECR_Compta_Autorise` tinyint(4) default NULL, PRIMARY KEY (`ECR_ID`), UNIQUE KEY `ECR_ID` (`ECR_ID`), KEY `WDIDX120306542636` (`ECR_TEC`), KEY `WDIDX120306542637` (`ECR_GUI`), KEY `WDIDX120306542638` (`ECR_FIC`), KEY `WDIDX120306542739` (`ECR_ADR`), KEY `WDIDX120306542740` (`ECR_BDD`), KEY `ECR_ID_Contrepassation` (`ECR_ID_Contrepassation`), CONSTRAINT `T_ECRITURE_ECR_ibfk_3` FOREIGN KEY (`ECR_ID_Contrepassation`) REFERENCES `T_ECRITURE_ECR` (`ECR_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;"; int query1len = strlen(query1); mysql_init(&conn); if (!mysql_real_connect(&conn, "127.0.0.1", "root", "", "test", 33051, NULL,CLIENT_MULTI_STATEMENTS)) { printf("Error connect: %s\n", mysql_error(&conn)); exit(1); } OK = mysql_real_query (&conn, query1, query1len); if (0 != OK) { printf("Error query: %s\n", mysql_error(&conn)); exit(1); } do { result = mysql_store_result(&conn); if (result) mysql_free_result(result); else { printf("Error result: %s\n", mysql_error(&conn)); //exit(1); } OK = mysql_next_result(&conn); if (OK > 0) { printf("Error next: %s\n", mysql_error(&conn)); exit(1); } } while (0 == OK); mysql_close(&conn); }
[27 Sep 2010 16:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/119193 3507 Dmitry Shulga 2010-09-27 Added a test case for Bug#51232. The bug itself can not be repeated.
[27 Sep 2010 16:57]
Dmitry Shulga
Bug can't be repeated.
[13 Jan 2011 15:05]
Davi Arnaut
Dmitry's test case reveals a valgrind warning within InnoDB: ==2130== Thread 11: ==2130== Conditional jump or move depends on uninitialised value(s) ==2130== at 0x8FF1EC: dict_load_foreign (dict0load.c:1242) ==2130== by 0x8FF609: dict_load_foreigns (dict0load.c:1389) ==2130== by 0x94AF6E: row_table_add_foreign_constraints (row0mysql.c:2163) ==2130== by 0x8D82D1: ha_innobase::create(char const*, st_table*, st_ha_create_information*) (ha_innodb.cc:5739) ==2130== by 0x7C07A5: handler::ha_create(char const*, st_table*, st_ha_create_information*) (handler.cc:3421) ==2130== by 0x7C0F5F: ha_create_table(THD*, char const*, char const*, char const*, st_ha_create_information*, bool) (handler.cc:3628) ==2130== by 0x76D4AE: rea_create_table(THD*, char const*, char const*, char const*, st_ha_create_information*, List<Create_field>&, unsigned int, st_key*, handler*) (unireg.cc:424) ==2130== by 0x7E7CD4: mysql_create_table_no_lock(THD*, char const*, char const*, st_ha_create_information*, Alter_info*, bool, unsigned int) (sql_table.cc:3981) ==2130== by 0x7E814F: mysql_create_table(THD*, char const*, char const*, st_ha_create_information*, Alter_info*, bool, unsigned int) (sql_table.cc:4076) ==2130== by 0x67E438: mysql_execute_command(THD*) (sql_parse.cc:2806) ==2130== by 0x68855E: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6075) ==2130== by 0x67AA46: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1300) ==2130== by 0x67991E: do_command(THD*) (sql_parse.cc:889)
[20 Jan 2011 0:49]
Davi Arnaut
Hum, the Valgrind warning looks like a duplicate of Bug#59157.
