Bug #56143 | too many foreign keys causes output of show create table to become invalid | ||
---|---|---|---|
Submitted: | 20 Aug 2010 13:29 | Modified: | 10 Dec 2010 0:39 |
Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
Version: | 5.0, 5.1, 5.5 | OS: | Any |
Assigned to: | Vasil Dimov | CPU Architecture: | Any |
[20 Aug 2010 13:29]
Shane Bester
[20 Aug 2010 13:31]
MySQL Verification Team
import this to create the table
Attachment: bug56143_create_the_fk_table.sql (application/octet-stream, text), 78.25 KiB.
[20 Aug 2010 13:33]
MySQL Verification Team
mysqldump output, cannot be restored due to syntax errors
Attachment: bug56143_faulty_show_create_table_output.sql (application/octet-stream, text), 64.64 KiB.
[20 Aug 2010 13:41]
MySQL Verification Team
well, 5.5.5 can't create a foreign key it seems. this fails: set foreign_key_checks=0; drop table if exists t1; create table t1(b int,key(b))engine=innodb; alter table `t1` add constraint `a0` foreign key (`b`) references `t1`(`b`) on update set null;
[20 Aug 2010 14:04]
MySQL Verification Team
Thank you for the bug report. CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa55` FO ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 5.0.92-debug | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+---------------------+ 5 rows in set (0.00 sec)
[24 Aug 2010 20:40]
Tushar Mahale
This is due to the following condition in ha_innodb.cc: char* ha_innobase::get_foreign_key_create_info(void) { ... if (flen < 0) { flen = 0; } else if (flen > 64000 - 1) { flen = 64000 - 1; } ... } The FK comment is truncated to 63999 characters and hence the truncated show create table comment.
[13 Oct 2010 17:21]
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/120711 3626 Vasil Dimov 2010-10-13 Fix Bug#56143 too many foreign keys causes output of show create table to become invalid Just remove the check whether the file is "too big". A similar code exists in ha_innobase::update_table_comment() but that method does not seem to be used.
[14 Oct 2010 9:32]
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/120757 3627 Vasil Dimov 2010-10-14 Fix Bug#56143 too many foreign keys causes output of show create table to become invalid This is a port of the following changeset from 5.1/storage/innobase to 5.1/storage/innodb_plugin: ------------------------------------------------------------ revno: 3626 revision-id: vasil.dimov@oracle.com-20101013171859-gi9n558yj89x9v3w parent: klewis@mysql.com-20101012175933-ce9kkgl0z8oeqffa committer: Vasil Dimov <vasil.dimov@oracle.com> branch nick: mysql-5.1-innodb timestamp: Wed 2010-10-13 20:18:59 +0300 message: Fix Bug#56143 too many foreign keys causes output of show create table to become invalid Just remove the check whether the file is "too big". A similar code exists in ha_innobase::update_table_comment() but that method does not seem to be used. Also use a CREATE statement with all the FKs instead of ALTERing the table 550 times because it is faster.
[14 Oct 2010 9:36]
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/120758 3628 Vasil Dimov 2010-10-14 Tune the test for Bug#56143 too many foreign keys causes output of show create table to become invalid Use a CREATE statement with all the FKs instead of ALTERing the table many times because it is faster (11 seconds vs 3 seconds).
[14 Oct 2010 9:41]
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/120759 3628 Vasil Dimov 2010-10-13 Fix Bug#56143 too many foreign keys causes output of show create table to become invalid Just remove the check whether the file is "too big". A similar code exists in ha_innobase::update_table_comment() but that method does not seem to be used.
[14 Oct 2010 9:42]
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/120760 3629 Vasil Dimov 2010-10-14 Fix Bug#56143 too many foreign keys causes output of show create table to become invalid This is a port of the following changeset from 5.1/storage/innobase to 5.1/storage/innodb_plugin: ------------------------------------------------------------ revno: 3626 revision-id: vasil.dimov@oracle.com-20101013171859-gi9n558yj89x9v3w parent: klewis@mysql.com-20101012175933-ce9kkgl0z8oeqffa committer: Vasil Dimov <vasil.dimov@oracle.com> branch nick: mysql-5.1-innodb timestamp: Wed 2010-10-13 20:18:59 +0300 message: Fix Bug#56143 too many foreign keys causes output of show create table to become invalid Just remove the check whether the file is "too big". A similar code exists in ha_innobase::update_table_comment() but that method does not seem to be used. Also use a CREATE statement with all the FKs instead of ALTERing the table 550 times because it is faster.
[14 Oct 2010 9:48]
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/120762 3630 Vasil Dimov 2010-10-14 Tune the test for Bug#56143 too many foreign keys causes output of show create table to become invalid Use a CREATE statement with all the FKs instead of ALTERing the table many times because it is faster (11 seconds vs 3 seconds).
[15 Oct 2010 14:31]
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/120853 3633 Vasil Dimov 2010-10-15 Add InnoDB Plugin ChangeLog entry for Bug#56143
[13 Nov 2010 16:14]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:39]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[18 Nov 2010 15:53]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[10 Dec 2010 0:39]
John Russell
Added to change log: A large number of foreign key declarations could cause the output of the SHOW CREATE STATEMENT statement to be truncated.
[16 Dec 2010 22:32]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)