MySQL Bugs: #69062: Foreign Key id modified during export

Bug #69062	Foreign Key id modified during export
Submitted:	25 Apr 2013 8:04	Modified:	15 May 2013 16:36
Reporter:	Nico Niepraschk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Any
Assigned to:	Daniel So	CPU Architecture:	Any
Tags:	regression

Description:
Some id characters of a foreign key will be modified during export

How to repeat:
After foreign key creation

ALTER TABLE tab ADD CONSTRAINT 'tab:col' FOREIGN KEY ( `col` ) REFERENCES `tab2`( `col2` );

SHOW CREATE TABLE displays

... CONSTRAINT `tab?col` FOREIGN KEY (`col`) REFERENCES `tab2` (`col2`) ...

Hello Nico,

Thank you for the report.
Verified as described.

Thanks,
Umesh

How to repeat
================

drop table if exists t2;
drop table if exists t1;

create table t1(id int not null primary key)engine=innodb;
create table t2(id int not null primary key)engine=innodb;
ALTER TABLE t2 ADD CONSTRAINT `t2:id` FOREIGN KEY (id) REFERENCES t1(id);
show create table t2\G

####

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.10-debug-log |
+------------------+
1 row in set (0.00 sec)

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> create table t1(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> create table t2(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER TABLE t2 ADD CONSTRAINT `t2:id` FOREIGN KEY (id) REFERENCES t1(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `t2:id` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

#####

mysql> drop table if exists t2;
Query OK, 0 rows affected (0.04 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.14 sec)

mysql> create table t1(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> create table t2(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> 
mysql> ALTER TABLE t2 ADD CONSTRAINT `t2.id` FOREIGN KEY (id) REFERENCES t1(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `t2?id` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.11-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql>

http://bugs.mysql.com/bug.php?id=69079 marked as duplicate of this one.

## 5.5.31 is also affected

[root@ushastry mysql-5.5.31]# bin/mysql -u root -p --port=3306 --protocol=TCP test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.31-debug-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create table t1(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql> create table t2(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> ALTER TABLE t2 ADD CONSTRAINT `t2:id` FOREIGN KEY (id) REFERENCES t1(id);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `t2?id` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

recent regression in 5.1.69, 5.5.31, 5.6.11.
I guess this fix for this could have been to blame:
Bug #11753153 INNODB GENERATES SYMBOLS THAT ARE TOO LONG, INVALID DDL FROM SHOW CREATE

select * from REFERENTIAL_CONSTRAINTS; shows the correct names.

the bug is that function dict_print_info_on_foreign_key_in_create_format
calls:

innobase_convert_from_id(&my_charset_filename, constraint_name,
			 stripped_id, MAX_TABLE_NAME_LEN);

Why is it using my_charset_filename and not utf8?

Added a changelog entry for 5.1.71, 5.5.33, 5.6.13:

"Some characters in the identifier for a foreign key constraint are modified during table exports."