Bug #35526 Foreign keys: CREATE TABLE LIKE copies what it shouldn't
Submitted: 24 Mar 2008 17:40 Modified: 19 Aug 2008 12:35
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.1.0-fk-debug OS:Linux (SUSE 10 | 32-bit)
Assigned to: Dmitry Lenev CPU Architecture:Any

[24 Mar 2008 17:40] Peter Gulutzan
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

WL#148 says:

"CREATE TABLE t1 LIKE t2" does not copy t2's foreign keys,
so no special code is needed for this.

But "CREATE TABLE t1 LIKE t2" does copy t2's foreign keys.

How to repeat:
create table t2 (s1 int references t2(s1));
create table t1 like t2;
show create table t1;
[24 Mar 2008 21:56] MySQL Verification Team
Thank you for the bug report.

[miguel@mirador dbs]$ 6.1fk/bin/mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t2 (s1 int references t2(s1));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 like t2;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1;
| Table | Create Table                                                                                                                            |
| t1    | CREATE TABLE `t1` (
  `s1` int(11) DEFAULT NULL CONSTRAINT `fk_t2_2_7756` REFERENCES `t2` (`s1`)
1 row in set (0.00 sec)

[19 Aug 2008 12:27] 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:


2682 Dmitry Lenev	2008-08-19
      Patch for the 7th milestone of WL#148 "Foreign keys"
      ("DDL checks and changes CREATE, CREATE TABLE SELECT,
      CREATE TABLE LIKE") implementing necessary changes in
      CREATE TABLE LIKE (and thus fixing bug #35526 "Foreign
      keys: CREATE TABLE LIKE copies what it shouldn't").
      Per LLD CREATE TABLE LIKE statement should not copy foreign keys
      from source table to the new table.
      Since we store information about foreign keys in .FRM files
      and there is no simple way to modify this information without
      recreating .FRM file we have to change our implementation of
      CREATE TABLE LIKE. Instead of directly copying .FRMs file we
      now use the same code as simple CREATE TABLE.
      I.e. we generate structures describing table being created
      from source table and the pass these structures after minor
      tweaks to the mysql_create_table_no_lock() function.
      A side effect of this change is that CREATE TABLE LIKE now
      follows the same rules as CREATE/ALTER TABLE and thus bug
      #22909 "Using CREATE ... LIKE is possible to create field
      with invalid default value" and bug #37371 "CREATE TABLE
      LIKE merge loses UNION parameter" are solved.
      Another similar side-effects are that CREATE TABLE LIKE no
      longer preserves .FRM version and converts old varchar fields
      to new varchar fields.
[19 Aug 2008 12:35] Dmitry Lenev
Patch fixing this bug was pushed into mysql-6.1-fk tree. Since this bug is
reported against tree which is not publicly available I am simply closing
this bug.