"CREATE TABLE ... LIKE ..." fails if source table has a FK constraint

Bug #622465 reported by Hartmut Holzgraefe on 2010-08-22
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Fix Released
Medium
Brian Aker
7.0
Fix Released
Medium
Brian Aker

Bug Description

how to reproduce:

  drop table if exists `t3`;
  drop table if exists `t2`;
  drop table if exists `t1`;

  create table t1 (id int primary key);

  create table `t2` like `t1`;
  alter table `t2` add foreign key (`id`) references `t1`(`id`);

  create table `t3` like `t2`;

last statement fails with

  ERROR 1005 (HY000): Can't create table 'test.t3' (errno: 121)

Related branches

to run test in my linked branch do:

  cd tests; ./test-run --suite=broken create-table-like-with-fk-bug-lp622465

the test *should* pass without error but fails with:

  drizzletest: At line 18: query 'CREATE TABLE t3 LIKE t2' failed: 1005: Can't create table 'test.t3' (errno: 121)

description: updated
Travis Davies (travisdaveez) wrote :

I Confirm this.

// OUTPUT FROM TEST:
./test-run --suite=broken create-table-like-with-fk-bug-lp622465
Logging: ./test-run --suite=broken create-table-like-with-fk-bug-lp622465
Drizzle Version 2010.09.1727
Using MTR_BUILD_THREAD = -69.4
Using MASTER_MYPORT = 9306
Using MASTER_MYPORT1 = 9307
Using SLAVE_MYPORT = 9308
Using SLAVE_MYPORT1 = 9309
Using SLAVE_MYPORT2 = 9310
Using MC_PORT = 9316
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
================================================================================
DEFAULT STORAGE ENGINE: innodb
TEST RESULT TIME (ms)
--------------------------------------------------------------------------------

broken.create-table-like-with-fk-bug-lp622465 [ fail ]
drizzletest: At line 18: query 'CREATE TABLE t3 LIKE t2' failed: 1005: Can't create table 'test.t3' (errno: 121)

The result from queries just before the failure was:
DROP TABLE IF EXISTS `t3`;
DROP TABLE IF EXISTS `t2`;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id);
CREATE TABLE t3 LIKE t2;

More results from queries before failure can be found in /home/tdavies/hacking/dzzRepo/drizzle-bug622465/tests/var/log/create-table-like-with-fk-bug-lp622465.log

Aborting: broken.create-table-like-with-fk-bug-lp622465 failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers

// OUTPUT FROM WITHIN CLIENT:
drizzle> use test
Database changed
drizzle> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0 sec)

drizzle> drop table if exists `t2`;
Query OK, 0 rows affected, 1 warning (0 sec)

drizzle> drop table if exists `t3`;
Query OK, 0 rows affected, 1 warning (0 sec)

drizzle> create table t1 (id int primary key);
Query OK, 0 rows affected (0.04 sec)

drizzle> create table `t2` like `t1`;
Query OK, 0 rows affected (0.04 sec)

drizzle> alter table `t2` add foreign key (`id`) references `t1`(`id`);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

drizzle> create table `t3` like `t2`;
ERROR 1005 (HY000): Can't create table 'test.t3' (errno: 121)
drizzle>

Changed in drizzle:
status: New → Confirmed
Changed in drizzle:
importance: Undecided → Medium
Changed in drizzle:
assignee: nobody → Travis Davies (travisdaveez)
Changed in drizzle:
assignee: Travis Davies (travisdaveez) → nobody
Changed in drizzle:
assignee: nobody → Brian Aker (brianaker)
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers