MySQL Bugs: #35526: Foreign keys: CREATE TABLE LIKE copies what it shouldn't

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:	None
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

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

WL#148 says:
"
CREATE TABLE ... LIKE
---------------------

"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;

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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

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/51937

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.

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.