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:
None 
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
Description:
a table can be created that cannot be shown correctly via SHOW CREATE TABLE.
hence, it cannot be restored from backup, since SHOW CREATE TABLE is truncated.
see attached files for table structure and resulting output.

-----
<cut>
  CONSTRAINT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa55` FO
) ENGINE=InnoDB DEFAULT CHARSET=latin1
----

How to repeat:
import attached .sql

Suggested fix:
get rid of legacy output limits, or prevent such tables being created in the first place.  this can break automated stress testing utils.
[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)