#At file:///home/dlenev/src/bzr/mysql-6.1-bg35532/
2675 Dmitry Lenev 2008-06-24
Fix for #35532 "Foreign keys: errors with automatic constraint
names".
Name conflict occured and apropriate error was emitted when one
tried to perform thousands of add/drop foreign key operations
without specifying explicit foreign key names on the same table
or used explicit foreign key names in the same name-space as
automatically generated foreign key names.
This problem is caused by the fact that our current approach
to generating unique names for foreign keys without explicit
names relies on using table name prefix, connection id and
value from pseudo-random generator and that we do not try
to generate another name when we find out our generated name
is already occupied.
Since we can't easily implement retry logic due to current
limitations of metadata locking protocol we try to alleviate
this problem by increasing range for the random component of
the name by 3 orders. Also we have switched to base-36 system
for encoding of this component to keep generated names
shorter and got rid of connection id component in the names
(the latter didn't help too much and complicated code/tests).
This patch also ensures that generated name always stays
within NAME_CHAR_LEN character limit (otherwise such names
are going to be truncated in I_S table and we won't be able
to restore them from mysqldump dumps).
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/fk_dd.cc
per-file comments:
mysql-test/r/foreign_key_all_engines.result
Adjusted test case to the changes in the approach to generation
of foreign key names. Added coverage for cases when table name
has border-line length so it can't be used in automatically
generated name as a whole.
mysql-test/t/foreign_key_all_engines.test
Adjusted test case to the changes in the approach to generation
of foreign key names. Added coverage for cases when table name
has border-line length so it can't be used in automatically
generated name as a whole.
sql/fk_dd.cc
Foreign_key::generate_name_if_needed():
- Increased range for the random component by 3 orders. Also
switched to base-36 system for encoding of this component to
keep names shorter.
- Got rid of connection id component.
- Changed code to truncate table name component if it is
necessary to keep the whole generated name within
NAME_CHAR_LEN limit.
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2008-06-23 07:14:00 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2008-06-24 11:42:32 +0000
@@ -1,7 +1,6 @@
drop tables if exists t1, t2, t3;
drop database if exists mysqltest;
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
create table t2 (pk int primary key);
create table t3 (opk int primary key);
create table t1 (fk1 int references t2 (pk),
@@ -28,16 +27,16 @@ foreign key (fk19) references t2 (pk));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_288` REFERENCES `t2` (`pk`),
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_11f06` REFERENCES `t2` (`pk`),
`fk2` int(11) DEFAULT NULL CONSTRAINT `fk2` REFERENCES `t2` (`pk`),
- `fk3` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1164` REFERENCES `t2` (`pk`) MATCH FULL,
- `fk4` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_4954` REFERENCES `t2` (`pk`) ON DELETE CASCADE ON UPDATE RESTRICT,
- `fk5` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1280` REFERENCES `t2` (`pk`) ON DELETE RESTRICT ON UPDATE CASCADE,
- `fk6` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1539` REFERENCES `t2` (`pk`) ON DELETE SET NULL,
- `fk7` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_3856` REFERENCES `t2` (`pk`) ON UPDATE SET NULL,
- `fk8` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_4663` REFERENCES `t2` (`pk`) MATCH FULL ON UPDATE SET DEFAULT,
- `fk9` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1746` REFERENCES `t2` (`pk`) ON DELETE SET DEFAULT,
- `fk10` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_4743` REFERENCES `t2` (`pk`) CONSTRAINT `fk_t1_1_8476` REFERENCES `t3` (`opk`),
+ `fk3` int(11) DEFAULT NULL CONSTRAINT `fk_t1_46vh3` REFERENCES `t2` (`pk`) MATCH FULL,
+ `fk4` int(11) DEFAULT NULL CONSTRAINT `fk_t1_hu4cw` REFERENCES `t2` (`pk`) ON DELETE CASCADE ON UPDATE RESTRICT,
+ `fk5` int(11) DEFAULT NULL CONSTRAINT `fk_t1_4lzdd` REFERENCES `t2` (`pk`) ON DELETE RESTRICT ON UPDATE CASCADE,
+ `fk6` int(11) DEFAULT NULL CONSTRAINT `fk_t1_5jjs5` REFERENCES `t2` (`pk`) ON DELETE SET NULL,
+ `fk7` int(11) DEFAULT NULL CONSTRAINT `fk_t1_dvssn` REFERENCES `t2` (`pk`) ON UPDATE SET NULL,
+ `fk8` int(11) DEFAULT NULL CONSTRAINT `fk_t1_gscmx` REFERENCES `t2` (`pk`) MATCH FULL ON UPDATE SET DEFAULT,
+ `fk9` int(11) DEFAULT NULL CONSTRAINT `fk_t1_6acsr` REFERENCES `t2` (`pk`) ON DELETE SET DEFAULT,
+ `fk10` int(11) DEFAULT NULL CONSTRAINT `fk_t1_h2q32` REFERENCES `t2` (`pk`) CONSTRAINT `fk_t1_uik15` REFERENCES `t3` (`opk`),
`fk11` int(11) DEFAULT NULL,
`fk12` int(11) DEFAULT NULL,
`fk13` int(11) DEFAULT NULL,
@@ -48,84 +47,84 @@ t1 CREATE TABLE `t1` (
`fk18` int(11) DEFAULT NULL,
`fk19` int(11) DEFAULT NULL,
CONSTRAINT `fk11` FOREIGN KEY (`fk11`) REFERENCES `t2` (`pk`),
- CONSTRAINT `fk_t1_1_8152` FOREIGN KEY (`fk12`) REFERENCES `t2` (`pk`) MATCH FULL,
- CONSTRAINT `fk_t1_1_5334` FOREIGN KEY (`fk13`) REFERENCES `t2` (`pk`) ON DELETE CASCADE ON UPDATE RESTRICT,
- CONSTRAINT `fk_t1_1_2214` FOREIGN KEY (`fk14`) REFERENCES `t2` (`pk`) ON DELETE RESTRICT ON UPDATE CASCADE,
- CONSTRAINT `fk_t1_1_5070` FOREIGN KEY (`fk15`) REFERENCES `t2` (`pk`) ON DELETE SET NULL,
- CONSTRAINT `fk_t1_1_8706` FOREIGN KEY (`fk16`) REFERENCES `t2` (`pk`) ON UPDATE SET NULL,
- CONSTRAINT `fk_t1_1_8320` FOREIGN KEY (`fk17`) REFERENCES `t2` (`pk`) MATCH FULL ON UPDATE SET DEFAULT,
- CONSTRAINT `fk_t1_1_5483` FOREIGN KEY (`fk18`) REFERENCES `t2` (`pk`) ON DELETE SET DEFAULT,
- CONSTRAINT `fk_t1_1_2454` FOREIGN KEY (`fk19`) REFERENCES `t2` (`pk`)
+ CONSTRAINT `fk_t1_tclwo` FOREIGN KEY (`fk12`) REFERENCES `t2` (`pk`) MATCH FULL,
+ CONSTRAINT `fk_t1_j7dfz` FOREIGN KEY (`fk13`) REFERENCES `t2` (`pk`) ON DELETE CASCADE ON UPDATE RESTRICT,
+ CONSTRAINT `fk_t1_7z1hz` FOREIGN KEY (`fk14`) REFERENCES `t2` (`pk`) ON DELETE RESTRICT ON UPDATE CASCADE,
+ CONSTRAINT `fk_t1_i935z` FOREIGN KEY (`fk15`) REFERENCES `t2` (`pk`) ON DELETE SET NULL,
+ CONSTRAINT `fk_t1_vcbc1` FOREIGN KEY (`fk16`) REFERENCES `t2` (`pk`) ON UPDATE SET NULL,
+ CONSTRAINT `fk_t1_tyb6c` FOREIGN KEY (`fk17`) REFERENCES `t2` (`pk`) MATCH FULL ON UPDATE SET DEFAULT,
+ CONSTRAINT `fk_t1_jqlvq` FOREIGN KEY (`fk18`) REFERENCES `t2` (`pk`) ON DELETE SET DEFAULT,
+ CONSTRAINT `fk_t1_8u3vp` FOREIGN KEY (`fk19`) REFERENCES `t2` (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.referential_constraints
where constraint_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
-NULL test fk_t1_1_288 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_11f06 NULL test NONE NO ACTION NO ACTION t1 t2
NULL test fk2 NULL test NONE NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_1164 NULL test FULL NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_4954 NULL test NONE RESTRICT CASCADE t1 t2
-NULL test fk_t1_1_1280 NULL test NONE CASCADE RESTRICT t1 t2
-NULL test fk_t1_1_1539 NULL test NONE NO ACTION SET NULL t1 t2
-NULL test fk_t1_1_3856 NULL test NONE SET NULL NO ACTION t1 t2
-NULL test fk_t1_1_4663 NULL test FULL SET DEFAULT NO ACTION t1 t2
-NULL test fk_t1_1_1746 NULL test NONE NO ACTION SET DEFAULT t1 t2
-NULL test fk_t1_1_4743 NULL test NONE NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_8476 NULL test NONE NO ACTION NO ACTION t1 t3
+NULL test fk_t1_46vh3 NULL test FULL NO ACTION NO ACTION t1 t2
+NULL test fk_t1_hu4cw NULL test NONE RESTRICT CASCADE t1 t2
+NULL test fk_t1_4lzdd NULL test NONE CASCADE RESTRICT t1 t2
+NULL test fk_t1_5jjs5 NULL test NONE NO ACTION SET NULL t1 t2
+NULL test fk_t1_dvssn NULL test NONE SET NULL NO ACTION t1 t2
+NULL test fk_t1_gscmx NULL test FULL SET DEFAULT NO ACTION t1 t2
+NULL test fk_t1_6acsr NULL test NONE NO ACTION SET DEFAULT t1 t2
+NULL test fk_t1_h2q32 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_uik15 NULL test NONE NO ACTION NO ACTION t1 t3
NULL test fk11 NULL test NONE NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_8152 NULL test FULL NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_5334 NULL test NONE RESTRICT CASCADE t1 t2
-NULL test fk_t1_1_2214 NULL test NONE CASCADE RESTRICT t1 t2
-NULL test fk_t1_1_5070 NULL test NONE NO ACTION SET NULL t1 t2
-NULL test fk_t1_1_8706 NULL test NONE SET NULL NO ACTION t1 t2
-NULL test fk_t1_1_8320 NULL test FULL SET DEFAULT NO ACTION t1 t2
-NULL test fk_t1_1_5483 NULL test NONE NO ACTION SET DEFAULT t1 t2
-NULL test fk_t1_1_2454 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_tclwo NULL test FULL NO ACTION NO ACTION t1 t2
+NULL test fk_t1_j7dfz NULL test NONE RESTRICT CASCADE t1 t2
+NULL test fk_t1_7z1hz NULL test NONE CASCADE RESTRICT t1 t2
+NULL test fk_t1_i935z NULL test NONE NO ACTION SET NULL t1 t2
+NULL test fk_t1_vcbc1 NULL test NONE SET NULL NO ACTION t1 t2
+NULL test fk_t1_tyb6c NULL test FULL SET DEFAULT NO ACTION t1 t2
+NULL test fk_t1_jqlvq NULL test NONE NO ACTION SET DEFAULT t1 t2
+NULL test fk_t1_8u3vp NULL test NONE NO ACTION NO ACTION t1 t2
select * from information_schema.table_constraints
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
-NULL test fk_t1_1_288 test t1 FOREIGN KEY
+NULL test fk_t1_11f06 test t1 FOREIGN KEY
NULL test fk2 test t1 FOREIGN KEY
-NULL test fk_t1_1_1164 test t1 FOREIGN KEY
-NULL test fk_t1_1_4954 test t1 FOREIGN KEY
-NULL test fk_t1_1_1280 test t1 FOREIGN KEY
-NULL test fk_t1_1_1539 test t1 FOREIGN KEY
-NULL test fk_t1_1_3856 test t1 FOREIGN KEY
-NULL test fk_t1_1_4663 test t1 FOREIGN KEY
-NULL test fk_t1_1_1746 test t1 FOREIGN KEY
-NULL test fk_t1_1_4743 test t1 FOREIGN KEY
-NULL test fk_t1_1_8476 test t1 FOREIGN KEY
+NULL test fk_t1_46vh3 test t1 FOREIGN KEY
+NULL test fk_t1_hu4cw test t1 FOREIGN KEY
+NULL test fk_t1_4lzdd test t1 FOREIGN KEY
+NULL test fk_t1_5jjs5 test t1 FOREIGN KEY
+NULL test fk_t1_dvssn test t1 FOREIGN KEY
+NULL test fk_t1_gscmx test t1 FOREIGN KEY
+NULL test fk_t1_6acsr test t1 FOREIGN KEY
+NULL test fk_t1_h2q32 test t1 FOREIGN KEY
+NULL test fk_t1_uik15 test t1 FOREIGN KEY
NULL test fk11 test t1 FOREIGN KEY
-NULL test fk_t1_1_8152 test t1 FOREIGN KEY
-NULL test fk_t1_1_5334 test t1 FOREIGN KEY
-NULL test fk_t1_1_2214 test t1 FOREIGN KEY
-NULL test fk_t1_1_5070 test t1 FOREIGN KEY
-NULL test fk_t1_1_8706 test t1 FOREIGN KEY
-NULL test fk_t1_1_8320 test t1 FOREIGN KEY
-NULL test fk_t1_1_5483 test t1 FOREIGN KEY
-NULL test fk_t1_1_2454 test t1 FOREIGN KEY
+NULL test fk_t1_tclwo test t1 FOREIGN KEY
+NULL test fk_t1_j7dfz test t1 FOREIGN KEY
+NULL test fk_t1_7z1hz test t1 FOREIGN KEY
+NULL test fk_t1_i935z test t1 FOREIGN KEY
+NULL test fk_t1_vcbc1 test t1 FOREIGN KEY
+NULL test fk_t1_tyb6c test t1 FOREIGN KEY
+NULL test fk_t1_jqlvq test t1 FOREIGN KEY
+NULL test fk_t1_8u3vp test t1 FOREIGN KEY
select * from information_schema.key_column_usage
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-NULL test fk_t1_1_288 NULL test t1 fk1 1 1 test t2 pk
+NULL test fk_t1_11f06 NULL test t1 fk1 1 1 test t2 pk
NULL test fk2 NULL test t1 fk2 1 1 test t2 pk
-NULL test fk_t1_1_1164 NULL test t1 fk3 1 1 test t2 pk
-NULL test fk_t1_1_4954 NULL test t1 fk4 1 1 test t2 pk
-NULL test fk_t1_1_1280 NULL test t1 fk5 1 1 test t2 pk
-NULL test fk_t1_1_1539 NULL test t1 fk6 1 1 test t2 pk
-NULL test fk_t1_1_3856 NULL test t1 fk7 1 1 test t2 pk
-NULL test fk_t1_1_4663 NULL test t1 fk8 1 1 test t2 pk
-NULL test fk_t1_1_1746 NULL test t1 fk9 1 1 test t2 pk
-NULL test fk_t1_1_4743 NULL test t1 fk10 1 1 test t2 pk
-NULL test fk_t1_1_8476 NULL test t1 fk10 1 1 test t3 opk
+NULL test fk_t1_46vh3 NULL test t1 fk3 1 1 test t2 pk
+NULL test fk_t1_hu4cw NULL test t1 fk4 1 1 test t2 pk
+NULL test fk_t1_4lzdd NULL test t1 fk5 1 1 test t2 pk
+NULL test fk_t1_5jjs5 NULL test t1 fk6 1 1 test t2 pk
+NULL test fk_t1_dvssn NULL test t1 fk7 1 1 test t2 pk
+NULL test fk_t1_gscmx NULL test t1 fk8 1 1 test t2 pk
+NULL test fk_t1_6acsr NULL test t1 fk9 1 1 test t2 pk
+NULL test fk_t1_h2q32 NULL test t1 fk10 1 1 test t2 pk
+NULL test fk_t1_uik15 NULL test t1 fk10 1 1 test t3 opk
NULL test fk11 NULL test t1 fk11 1 1 test t2 pk
-NULL test fk_t1_1_8152 NULL test t1 fk12 1 1 test t2 pk
-NULL test fk_t1_1_5334 NULL test t1 fk13 1 1 test t2 pk
-NULL test fk_t1_1_2214 NULL test t1 fk14 1 1 test t2 pk
-NULL test fk_t1_1_5070 NULL test t1 fk15 1 1 test t2 pk
-NULL test fk_t1_1_8706 NULL test t1 fk16 1 1 test t2 pk
-NULL test fk_t1_1_8320 NULL test t1 fk17 1 1 test t2 pk
-NULL test fk_t1_1_5483 NULL test t1 fk18 1 1 test t2 pk
-NULL test fk_t1_1_2454 NULL test t1 fk19 1 1 test t2 pk
+NULL test fk_t1_tclwo NULL test t1 fk12 1 1 test t2 pk
+NULL test fk_t1_j7dfz NULL test t1 fk13 1 1 test t2 pk
+NULL test fk_t1_7z1hz NULL test t1 fk14 1 1 test t2 pk
+NULL test fk_t1_i935z NULL test t1 fk15 1 1 test t2 pk
+NULL test fk_t1_vcbc1 NULL test t1 fk16 1 1 test t2 pk
+NULL test fk_t1_tyb6c NULL test t1 fk17 1 1 test t2 pk
+NULL test fk_t1_jqlvq NULL test t1 fk18 1 1 test t2 pk
+NULL test fk_t1_8u3vp NULL test t1 fk19 1 1 test t2 pk
drop tables t1, t2, t3;
create table t2 (a int, b int, primary key (a, b));
create table t1 (fk1 int, fk2 int, fk3 int, fk4 int,
@@ -139,26 +138,26 @@ t1 CREATE TABLE `t1` (
`fk2` int(11) DEFAULT NULL,
`fk3` int(11) DEFAULT NULL,
`fk4` int(11) DEFAULT NULL,
- CONSTRAINT `fk_t1_1_5823` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `t2` (`a`, `b`),
- CONSTRAINT `fk_t1_1_1753` FOREIGN KEY (`fk4`, `fk3`) REFERENCES `t2` (`a`, `b`) MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT
+ CONSTRAINT `fk_t1_kypr9` FOREIGN KEY (`fk1`, `fk2`) REFERENCES `t2` (`a`, `b`),
+ CONSTRAINT `fk_t1_6b95c` FOREIGN KEY (`fk4`, `fk3`) REFERENCES `t2` (`a`, `b`) MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.referential_constraints
where constraint_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
-NULL test fk_t1_1_5823 NULL test NONE NO ACTION NO ACTION t1 t2
-NULL test fk_t1_1_1753 NULL test FULL RESTRICT RESTRICT t1 t2
+NULL test fk_t1_kypr9 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_6b95c NULL test FULL RESTRICT RESTRICT t1 t2
select * from information_schema.table_constraints
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
-NULL test fk_t1_1_5823 test t1 FOREIGN KEY
-NULL test fk_t1_1_1753 test t1 FOREIGN KEY
+NULL test fk_t1_kypr9 test t1 FOREIGN KEY
+NULL test fk_t1_6b95c test t1 FOREIGN KEY
select * from information_schema.key_column_usage
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-NULL test fk_t1_1_5823 NULL test t1 fk1 1 1 test t2 a
-NULL test fk_t1_1_5823 NULL test t1 fk2 2 2 test t2 b
-NULL test fk_t1_1_1753 NULL test t1 fk4 1 1 test t2 a
-NULL test fk_t1_1_1753 NULL test t1 fk3 2 2 test t2 b
+NULL test fk_t1_kypr9 NULL test t1 fk1 1 1 test t2 a
+NULL test fk_t1_kypr9 NULL test t1 fk2 2 2 test t2 b
+NULL test fk_t1_6b95c NULL test t1 fk4 1 1 test t2 a
+NULL test fk_t1_6b95c NULL test t1 fk3 2 2 test t2 b
drop tables t1, t2;
create database mysqltest;
use mysqltest;
@@ -167,70 +166,70 @@ create table test.t1 (fk1 int references
show create table test.t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1297` REFERENCES `mysqltest`.`t2` (`pk`)
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_4o4h0` REFERENCES `mysqltest`.`t2` (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.referential_constraints
where constraint_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
-NULL test fk_t1_1_1297 NULL mysqltest NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_4o4h0 NULL mysqltest NONE NO ACTION NO ACTION t1 t2
select * from information_schema.table_constraints
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
-NULL test fk_t1_1_1297 test t1 FOREIGN KEY
+NULL test fk_t1_4o4h0 test t1 FOREIGN KEY
select * from information_schema.key_column_usage
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-NULL test fk_t1_1_1297 NULL test t1 fk1 1 1 mysqltest t2 pk
+NULL test fk_t1_4o4h0 NULL test t1 fk1 1 1 mysqltest t2 pk
drop table test.t1, t2;
create table test.t2 (pk int primary key);
create table t1 (fk1 int references test.t2 (pk));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_1225` REFERENCES `test`.`t2` (`pk`)
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_4eux3` REFERENCES `test`.`t2` (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.referential_constraints
where constraint_schema = 'mysqltest' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
-NULL mysqltest fk_t1_1_1225 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL mysqltest fk_t1_4eux3 NULL test NONE NO ACTION NO ACTION t1 t2
select * from information_schema.table_constraints
where table_schema = 'mysqltest' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
-NULL mysqltest fk_t1_1_1225 mysqltest t1 FOREIGN KEY
+NULL mysqltest fk_t1_4eux3 mysqltest t1 FOREIGN KEY
select * from information_schema.key_column_usage
where table_schema = 'mysqltest' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-NULL mysqltest fk_t1_1_1225 NULL mysqltest t1 fk1 1 1 test t2 pk
+NULL mysqltest fk_t1_4eux3 NULL mysqltest t1 fk1 1 1 test t2 pk
drop table t1, test.t2;
create table test.t2 (pk int primary key);
create table test.t1 (fk1 int references test.t2 (pk));
show create table test.t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_1_2237` REFERENCES `t2` (`pk`)
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_t1_81x6f` REFERENCES `t2` (`pk`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from information_schema.referential_constraints
where constraint_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
-NULL test fk_t1_1_2237 NULL test NONE NO ACTION NO ACTION t1 t2
+NULL test fk_t1_81x6f NULL test NONE NO ACTION NO ACTION t1 t2
select * from information_schema.table_constraints
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
-NULL test fk_t1_1_2237 test t1 FOREIGN KEY
+NULL test fk_t1_81x6f test t1 FOREIGN KEY
select * from information_schema.key_column_usage
where table_schema = 'test' and table_name = 't1';
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
-NULL test fk_t1_1_2237 NULL test t1 fk1 1 1 test t2 pk
+NULL test fk_t1_81x6f NULL test t1 fk1 1 1 test t2 pk
use test;
drop table t1, t2;
drop database mysqltest;
create table t2 (a int, b int, primary key (a,b));
create table t1 (fk1 int, fk2 int, foreign key (fk1, fk2) references t2);
-ERROR 42000: Foreign key error: Constraint 'fk_t1_1_7507': '(parent column list)' is mandatory in MySQL
+ERROR 42000: Foreign key error: Constraint 'fk_t1_r114w': '(parent column list)' is mandatory in MySQL
create table t1 (fk1 int, fk2 int, foreign key (fk1, fk2) references t2 (a, b) match partial);
-ERROR 42000: Foreign key error: Constraint 'fk_t1_1_828': MATCH PARTIAL not supported
+ERROR 42000: Foreign key error: Constraint 'fk_t1_2ze5c': MATCH PARTIAL not supported
create table t1 (fk1 int references t2 (a, b));
-ERROR 42000: Foreign key error: Constraint 'fk_t1_1_1619': Parent column count (2) not equal to child column count (1)
+ERROR 42000: Foreign key error: Constraint 'fk_t1_5tvc0': Parent column count (2) not equal to child column count (1)
drop table t2;
create table t1 (a int primary key);
create table t2 (fk int
@@ -282,13 +281,12 @@ use test;
drop database mysqltest;
drop table t1, t2;
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
drop tables if exists t1, t2;
create table t2 (a int primary key);
create table t1 (fk1 int references t2 (a, b));
-ERROR 42000: Foreign key error: Constraint 'fk_t1_1_288': Parent column count (2) not equal to child column count (1)
+ERROR 42000: Foreign key error: Constraint 'fk_t1_11f06': Parent column count (2) not equal to child column count (1)
create table t1 (fk1 int, foreign key (fk1) references t2 (a, b));
-ERROR 42000: Foreign key error: Constraint 'fk_t1_1_1164': Parent column count (2) not equal to child column count (1)
+ERROR 42000: Foreign key error: Constraint 'fk_t1_46vh3': Parent column count (2) not equal to child column count (1)
drop table t2;
show variables like 'foreign_key_all_engines';
Variable_name Value
@@ -329,6 +327,54 @@ t3 CREATE TABLE `t3` (
`fk` int(11) DEFAULT NULL COMMENT 'my comment' CONSTRAINT `c1` REFERENCES `t1` (`i`) CONSTRAINT `c2` REFERENCES `t2` (`j`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop tables t1, t2, t3;
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+set @save_character_set_client=@@character_set_client,
+@save_character_set_results=@@character_set_results,
+@save_character_set_connection=@@character_set_connection;
+set @@character_set_client= utf8, @@character_set_results= utf8,
+@@character_set_connection= utf8;
+drop tables if exists
+t1, test123456789012345678901234567890123456789012345678901234567890,
+`тест12345678901234567890123456789012345678901234567890`;
+create table t1 (pk int primary key);
+create table test123456789012345678901234567890123456789012345678901234567890
+(fk1 int references t1 (pk), fk2 int, foreign key (fk2) references t1 (pk));
+show create table
+test123456789012345678901234567890123456789012345678901234567890;
+Table Create Table
+test123456789012345678901234567890123456789012345678901234567890 CREATE TABLE `test123456789012345678901234567890123456789012345678901234567890` (
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_test123456789012345678901234567890123456789012345678901_11f06` REFERENCES `t1` (`pk`),
+ `fk2` int(11) DEFAULT NULL,
+ CONSTRAINT `fk_test123456789012345678901234567890123456789012345678901_46vh3` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select table_name, constraint_name from information_schema.referential_constraints
+where constraint_schema = 'test' and
+table_name = 'test123456789012345678901234567890123456789012345678901234567890';
+table_name constraint_name
+test123456789012345678901234567890123456789012345678901234567890 fk_test123456789012345678901234567890123456789012345678901_11f06
+test123456789012345678901234567890123456789012345678901234567890 fk_test123456789012345678901234567890123456789012345678901_46vh3
+drop table test123456789012345678901234567890123456789012345678901234567890;
+create table `тест12345678901234567890123456789012345678901234567890`
+ (fk1 int references t1 (pk), fk2 int, foreign key (fk2) references t1 (pk));
+show create table
+`тест12345678901234567890123456789012345678901234567890`;
+Table Create Table
+тест12345678901234567890123456789012345678901234567890 CREATE TABLE `тест12345678901234567890123456789012345678901234567890` (
+ `fk1` int(11) DEFAULT NULL CONSTRAINT `fk_тест12345678901234567890123456789012345678901234567890_hu4cw` REFERENCES `t1` (`pk`),
+ `fk2` int(11) DEFAULT NULL,
+ CONSTRAINT `fk_тест12345678901234567890123456789012345678901234567890_4lzdd` FOREIGN KEY (`fk2`) REFERENCES `t1` (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select table_name, constraint_name from information_schema.referential_constraints
+where constraint_schema = 'test' and
+table_name = 'тест12345678901234567890123456789012345678901234567890';
+table_name constraint_name
+тест12345678901234567890123456789012345678901234567890 fk_тест12345678901234567890123456789012345678901234567890_hu4cw
+тест12345678901234567890123456789012345678901234567890 fk_тест12345678901234567890123456789012345678901234567890_4lzdd
+drop tables
+t1, `тест12345678901234567890123456789012345678901234567890`;
+set @@character_set_client=@save_character_set_client,
+@@character_set_results=@save_character_set_results,
+@@character_set_connection=@save_character_set_connection;
drop database if exists mysqltest;
create database mysqltest;
use mysqltest;
@@ -337,7 +383,6 @@ create table t2 (fk int references t1 (i
use test;
drop database mysqltest;
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
drop tables if exists t1, t2;
create table t1 (i int primary key);
create table t2 (fk1 int constraint c1 references t1 (i),
@@ -348,7 +393,7 @@ Table Create Table
t2 CREATE TABLE `t2` (
`fk1` int(11) DEFAULT NULL CONSTRAINT `c1` REFERENCES `t1` (`i`),
`fk2` int(11) DEFAULT NULL,
- CONSTRAINT `fk_t2_1_288` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
+ CONSTRAINT `fk_t2_11f06` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t2 add column a int;
show create table t2;
@@ -357,6 +402,6 @@ t2 CREATE TABLE `t2` (
`fk1` int(11) DEFAULT NULL CONSTRAINT `c1` REFERENCES `t1` (`i`),
`fk2` int(11) DEFAULT NULL,
`a` int(11) DEFAULT NULL,
- CONSTRAINT `fk_t2_1_288` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
+ CONSTRAINT `fk_t2_11f06` FOREIGN KEY (`fk2`) REFERENCES `t1` (`i`) ON DELETE SET NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2008-06-23 07:14:00 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2008-06-24 11:42:32 +0000
@@ -11,10 +11,9 @@ drop tables if exists t1, t2, t3;
drop database if exists mysqltest;
--enable_warnings
-# Initialize random number generator and connection_id to known values
-#to get repeatable constraint names.
+# Initialize random number generator to known value to get repeatable
+# constraint names.
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
# Let us test that we properly save and restore information from
# various clauses of foreign key definition.
@@ -203,7 +202,6 @@ drop table t1, t2;
# Ensure that automatically generated names are stable
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
--disable_warnings
drop tables if exists t1, t2;
--enable_warnings
@@ -259,6 +257,53 @@ drop tables t1, t2, t3;
#
+# Additional test for bug #35532 "Foreign keys: errors with automatic
+# constraint names" check that we properly generate foreign key names
+# for constraints on tables with border-line name lengths.
+#
+# Ensure that automatically generated names are stable
+set @@rand_seed1=10000000,@@rand_seed2=1000000;
+# Since this test uses utf8 table names we should temporarily
+# switch client/result character sets to utf8.
+set @save_character_set_client=@@character_set_client,
+ @save_character_set_results=@@character_set_results,
+ @save_character_set_connection=@@character_set_connection;
+set @@character_set_client= utf8, @@character_set_results= utf8,
+ @@character_set_connection= utf8;
+--disable_warnings
+drop tables if exists
+ t1, test123456789012345678901234567890123456789012345678901234567890,
+ `тест12345678901234567890123456789012345678901234567890`;
+--enable_warnings
+create table t1 (pk int primary key);
+# Table with a name which is too long to be used untruncated as a component
+# of foreign key name.
+create table test123456789012345678901234567890123456789012345678901234567890
+ (fk1 int references t1 (pk), fk2 int, foreign key (fk2) references t1 (pk));
+show create table
+ test123456789012345678901234567890123456789012345678901234567890;
+select table_name, constraint_name from information_schema.referential_constraints
+ where constraint_schema = 'test' and
+ table_name = 'test123456789012345678901234567890123456789012345678901234567890';
+drop table test123456789012345678901234567890123456789012345678901234567890;
+# Table with a name which length in _bytes_ is bigger than maximum
+# allowed number of _characters_, but which character length is fine
+# and allows untruncated usage.
+create table `тест12345678901234567890123456789012345678901234567890`
+ (fk1 int references t1 (pk), fk2 int, foreign key (fk2) references t1 (pk));
+show create table
+ `тест12345678901234567890123456789012345678901234567890`;
+select table_name, constraint_name from information_schema.referential_constraints
+ where constraint_schema = 'test' and
+ table_name = 'тест12345678901234567890123456789012345678901234567890';
+drop tables
+ t1, `тест12345678901234567890123456789012345678901234567890`;
+set @@character_set_client=@save_character_set_client,
+ @@character_set_results=@save_character_set_results,
+ @@character_set_connection=@save_character_set_connection;
+
+
+#
# Test for bug #35533 "Foreign keys: can't drop database with foreign keys".
#
--disable_warnings
@@ -280,7 +325,6 @@ drop database mysqltest;
#
# Make automatically generated constraint names repeatable
set @@rand_seed1=10000000,@@rand_seed2=1000000;
-set @@pseudo_thread_id= 1;
--disable_warnings
drop tables if exists t1, t2;
--enable_warnings
=== modified file 'sql/fk_dd.cc'
--- a/sql/fk_dd.cc 2008-06-23 07:14:00 +0000
+++ b/sql/fk_dd.cc 2008-06-24 11:42:32 +0000
@@ -565,24 +565,60 @@ void fk_get_table_constraints_for_create
void Foreign_key::generate_name_if_needed(THD *thd, const char *table_name)
{
- uint table_name_length= strlen(table_name);
-
- ulong randval;
-
if (!name.str || is_name_generated)
{
- name.str= (char *)alloc_root(thd->mem_root,
- 3 + table_name_length + 1 + 10 +
- 1 + 5 + 1);
+ const uint MAX_AFFIX_LEN= 3 + 1 + 5;
+ const uint MAX_5_DIGIT_NUMBER_FOR_RADIX_36= 60466175;
+ uint tab_name_len= strlen(table_name);
+ uint bytes_to_copy;
+ ulong randval;
+ char *pos;
+ if (tab_name_len <= NAME_CHAR_LEN - MAX_AFFIX_LEN)
+ {
+ /*
+ Since the length of table name in _bytes_ is smaller or equal than
+ (NAME_CHAR_LEN - max. length of parts to add) we can use full table
+ name as part of constraint name without further checks. This branch
+ is an optimization which in principle can be ommitted.
+ */
+ bytes_to_copy= tab_name_len;
+ }
+ else
+ {
+ /*
+ Let us find the point at which we should truncate table name to
+ produce component for constraint name.
+ */
+ if ((bytes_to_copy=
+ system_charset_info->cset->charpos(system_charset_info,
+ table_name,
+ table_name+tab_name_len,
+ NAME_CHAR_LEN-MAX_AFFIX_LEN)) >=
+ tab_name_len)
+ {
+ /*
+ Length of table name in _characters_ is smaller or equal than
+ (NAME_CHAR_LEN - max. num. of chars in parts to add) we still
+ can use full table name as part of constraint name.
+ */
+ bytes_to_copy= tab_name_len;
+ }
+ }
+
+ name.str= (char *)alloc_root(thd->mem_root,
+ MAX_AFFIX_LEN + bytes_to_copy + 1);
thd->save_rand_for_binlog();
- randval= (ulong)(my_rnd(&thd->rand)*10000);
- name.length= my_snprintf((char *)name.str,
- 3 + table_name_length + 1 + 10 + 1 + 5 + 1,
- "fk_%s_%lu_%lu", table_name,
- thd->variables.pseudo_thread_id,
- randval);
- thd->thread_specific_used= TRUE;
+ randval= (ulong)(my_rnd(&thd->rand) * MAX_5_DIGIT_NUMBER_FOR_RADIX_36);
+
+ memcpy(name.str, "fk_", 3);
+ pos= name.str + 3;
+ memcpy(pos, table_name, bytes_to_copy);
+ pos+= bytes_to_copy;
+ *pos= '_';
+ pos++;
+ pos= int2str(randval, pos, 36, 0);
+ name.length= pos - name.str;
is_name_generated= TRUE;
}
}
