Drizzle

Drizzledump not preserving FOREIGN KEY information during migration from MySQL

Bug #683940 reported by Patrick Crews
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Fix Released
Medium
Andrew Hutchings
Drizzle 2010-12-06
7.0
Fix Released
Medium
Andrew Hutchings
Drizzle 2010-12-06

Bug Description

This MySQL table:
CREATE TABLE t2(a INT NOT NULL, b INT , PRIMARY KEY(a), KEY b_key (b),
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b)
ON DELETE SET NULL
ON UPDATE CASCADE) ENGINE=Innodb;

Ends up as this in Drizzle post-migration:
SHOW CREATE TABLE drizzledump_migrate_test.t2;
Table Create Table
t2 CREATE TABLE `t2` (
  `a` INT NOT NULL,
  `b` INT DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b_key` (`b`)
) ENGINE=InnoDB COLLATE = utf8_general_ci

To repeat:
./test-run --suite=mysql_migrate foreign_key
The .result file is incorrect at the moment.

Please check the README file in the drizzle/tests/mysql_migrate directory for setup instructions.

Related branches

lp:~patrick-crews/drizzle/mysql-migrate-tests
Drizzle Developers: Pending requested
Diff: 4505 lines (+4052/-0)
90 files modified
tests/std_data/mysqlmigrate_basic.dat (+3/-0)
tests/std_data/mysqlmigrate_binary_type.dat (+14/-0)
tests/std_data/mysqlmigrate_bit_type.dat (+18/-0)
tests/std_data/mysqlmigrate_blob_type.dat (+41/-0)
tests/std_data/mysqlmigrate_char_type.dat (+14/-0)
tests/std_data/mysqlmigrate_comments.dat (+10/-0)
tests/std_data/mysqlmigrate_databases.dat (+10/-0)
tests/std_data/mysqlmigrate_date_type.dat (+14/-0)
tests/std_data/mysqlmigrate_datetime_type.dat (+14/-0)
tests/std_data/mysqlmigrate_decimal_type.dat (+18/-0)
tests/std_data/mysqlmigrate_double_type.dat (+14/-0)
tests/std_data/mysqlmigrate_engines.dat (+6/-0)
tests/std_data/mysqlmigrate_enum_type.dat (+15/-0)
tests/std_data/mysqlmigrate_float_type.dat (+21/-0)
tests/std_data/mysqlmigrate_foreign_key.dat (+4/-0)
tests/std_data/mysqlmigrate_indexes.dat (+3/-0)
tests/std_data/mysqlmigrate_integer_type.dat (+67/-0)
tests/std_data/mysqlmigrate_large_data.dat (+230/-0)
tests/std_data/mysqlmigrate_numeric_type.dat (+18/-0)
tests/std_data/mysqlmigrate_partitions.dat (+2/-0)
tests/std_data/mysqlmigrate_real_type.dat (+18/-0)
tests/std_data/mysqlmigrate_set_type.dat (+15/-0)
tests/std_data/mysqlmigrate_text_type.dat (+41/-0)
tests/std_data/mysqlmigrate_time_type.dat (+16/-0)
tests/std_data/mysqlmigrate_timestamp_type.dat (+12/-0)
tests/std_data/mysqlmigrate_varbinary_type.dat (+14/-0)
tests/std_data/mysqlmigrate_varchar_type.dat (+14/-0)
tests/std_data/mysqlmigrate_year_type.dat (+18/-0)
tests/suite/mysql_migrate/README (+7/-0)
tests/suite/mysql_migrate/include/drizzledump_migrate.inc (+45/-0)
tests/suite/mysql_migrate/include/mysql_cleanup.inc (+4/-0)
tests/suite/mysql_migrate/r/all_databases.result (+84/-0)
tests/suite/mysql_migrate/r/basic.result (+20/-0)
tests/suite/mysql_migrate/r/binary_type.result (+26/-0)
tests/suite/mysql_migrate/r/bit_type.result (+29/-0)
tests/suite/mysql_migrate/r/blob_type.result (+67/-0)
tests/suite/mysql_migrate/r/char_type.result (+25/-0)
tests/suite/mysql_migrate/r/comments.result (+33/-0)
tests/suite/mysql_migrate/r/databases.result (+70/-0)
tests/suite/mysql_migrate/r/date_type.result (+25/-0)
tests/suite/mysql_migrate/r/datetime_type.result (+25/-0)
tests/suite/mysql_migrate/r/decimal_type.result (+27/-0)
tests/suite/mysql_migrate/r/destination-database.result (+35/-0)
tests/suite/mysql_migrate/r/engines.result (+29/-0)
tests/suite/mysql_migrate/r/enum_type.result (+26/-0)
tests/suite/mysql_migrate/r/float_type.result (+30/-0)
tests/suite/mysql_migrate/r/foreign_key.result (+28/-0)
tests/suite/mysql_migrate/r/indexes.result (+29/-0)
tests/suite/mysql_migrate/r/integer_type.result (+103/-0)
tests/suite/mysql_migrate/r/large_data.result (+1601/-0)
tests/suite/mysql_migrate/r/numeric_type.result (+27/-0)
tests/suite/mysql_migrate/r/partitions.result (+21/-0)
tests/suite/mysql_migrate/r/real_type.result (+27/-0)
tests/suite/mysql_migrate/r/set_type.result (+27/-0)
tests/suite/mysql_migrate/r/text_type.result (+67/-0)
tests/suite/mysql_migrate/r/time_type.result (+27/-0)
tests/suite/mysql_migrate/r/timestamp_type.result (+23/-0)
tests/suite/mysql_migrate/r/varchar_type.result (+25/-0)
tests/suite/mysql_migrate/r/year_type.result (+30/-0)
tests/suite/mysql_migrate/t/all_databases.test (+90/-0)
tests/suite/mysql_migrate/t/basic.test (+16/-0)
tests/suite/mysql_migrate/t/binary_type.test (+16/-0)
tests/suite/mysql_migrate/t/bit_type.test (+16/-0)
tests/suite/mysql_migrate/t/blob_type.test (+31/-0)
tests/suite/mysql_migrate/t/char_type.test (+16/-0)
tests/suite/mysql_migrate/t/comments.test (+18/-0)
tests/suite/mysql_migrate/t/databases.test (+78/-0)
tests/suite/mysql_migrate/t/date_type.test (+16/-0)
tests/suite/mysql_migrate/t/datetime_type.test (+16/-0)
tests/suite/mysql_migrate/t/decimal_type.test (+16/-0)
tests/suite/mysql_migrate/t/destination-database.test (+56/-0)
tests/suite/mysql_migrate/t/double_type.test (+16/-0)
tests/suite/mysql_migrate/t/engines.test (+21/-0)
tests/suite/mysql_migrate/t/enum_type.test (+16/-0)
tests/suite/mysql_migrate/t/float_type.test (+16/-0)
tests/suite/mysql_migrate/t/foreign_key.test (+17/-0)
tests/suite/mysql_migrate/t/indexes.test (+17/-0)
tests/suite/mysql_migrate/t/integer_type.test (+36/-0)
tests/suite/mysql_migrate/t/large_data.test (+26/-0)
tests/suite/mysql_migrate/t/master.opt (+1/-0)
tests/suite/mysql_migrate/t/numeric_type.test (+16/-0)
tests/suite/mysql_migrate/t/partitions.test (+16/-0)
tests/suite/mysql_migrate/t/real_type.test (+16/-0)
tests/suite/mysql_migrate/t/set_type.test (+16/-0)
tests/suite/mysql_migrate/t/text_type.test (+31/-0)
tests/suite/mysql_migrate/t/time_type.test (+16/-0)
tests/suite/mysql_migrate/t/timestamp_type.test (+16/-0)
tests/suite/mysql_migrate/t/varbinary_type.test (+16/-0)
tests/suite/mysql_migrate/t/varchar_type.test (+16/-0)
tests/suite/mysql_migrate/t/year_type.test (+16/-0)
lp:~linuxjedi/drizzle/trunk-bug-683940
Drizzle Developers: Pending requested
Diff: 140 lines (+40/-81)
1 file modified
client/drizzledump_mysql.cc (+40/-81)
Patrick Crews (patrick-crews)
Changed in drizzle:
status: New → Confirmed
assignee: nobody → Andrew Hutchings (linuxjedi)
Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

ok, this is bad as I added support for this not too long ago :/

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

Something is wrong with this query around the last where condition for this foreign key (other test ones from the MySQL manual still work):

select rc.constraint_name, rc.referenced_table_name, group_concat(distinct concat('`',kc.column_name,'`')), rc.update_rule, rc.delete_rule, rc.match_option, group_concat(distinct concat('`',kt.column_name,'`')) from information_schema.referential_constraints rc join information_schema.key_column_usage kt on (rc.constraint_schema = kt.constraint_schema and rc.constraint_name = kt.constraint_name) join information_schema.key_column_usage kc on (rc.constraint_schema = kc.constraint_schema and rc.referenced_table_name = kc.table_name and rc.unique_constraint_name = kc.constraint_name) where rc.constraint_schema='SCHEMA' and rc.table_name='TABLE' group by rc.constraint_name

So headaches ahoy!

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

See also http://bugs.mysql.com/bug.php?id=58656 <- this is the way I wanted to do it

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

testing this one:

select kc.constraint_name, kc.table_name, group_concat(distinct concat('`',kc.column_name,'`')), kc.referenced_table_name, group_concat(distinct concat('`',kc.referenced_column_name,'`')), update_rule, delete_rule, match_option from information_schema.key_column_usage kc join information_schema.referential_constraints rc on (rc.constraint_schema = kc.constraint_schema and rc.constraint_name = kc.constraint_name) where kc.referenced_table_name is not null group by rc.constraint_name\G

Revision history for this message
Andrew Hutchings (linuxjedi) wrote :

This query hits this in MySQL 5.5:
http://bugs.mysql.com/?id=58658

Also in Leithal's words "it performs like ass".

Switching to a regex on SHOW CREATE TABLE

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.