| Bug #69079 | MySQL when using spaces in name of foreign key converts them to question marks | ||
|---|---|---|---|
| Submitted: | 26 Apr 2013 6:50 | Modified: | 26 Apr 2013 7:27 |
| Reporter: | Виталий Евсюков | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.5.31,5.6.11 | OS: | Linux (Ubuntu 12.04) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | constraint, foreign key, names, spaces | ||
[26 Apr 2013 7:25]
MySQL Verification Team
Hello Виталий Евсюков, Thank you for the report. Verified as described on reported and later versions. Thanks, Umesh
[26 Apr 2013 7:26]
MySQL Verification Team
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.11-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.06 sec)
mysql> drop table if exists t1;
Query OK, 0 rows affected (2.64 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.10 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>
#########
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.5.31-debug |
+--------------+
1 row in set (0.00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 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.10 sec)
mysql> create table t2(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE t2 ADD CONSTRAINT `t2_ id` FOREIGN KEY (id) REFERENCES t1(id);
Query OK, 0 rows affected (0.08 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>
[26 Apr 2013 7:27]
MySQL Verification Team
Duplicate of http://bugs.mysql.com/bug.php?id=69062
Description: I tried to add foreign key to my tables which include spaces. In MySQL 5.5.30 it successfully created and has same name I provide by ALTER TABLE or CREATE TABLE statements. But I was wondered when I tried to create same FK constraint in upgrade by apt-get upgrade MySQL with next version (5.5.31) - it converts all spaces from FK's name to question marks! How to repeat: guyfawkes@guyfawkes-VirtualBox:~$ mysql -u root -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.31-0ubuntu0.12.10.1 (Ubuntu) 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> create schema qtest; Query OK, 1 row affected (0.00 sec) mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> use qtest Database changed mysql> CREATE TABLE `testtable` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> rid int(11) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`), -> CONSTRAINT `fk_ my_rid` FOREIGN KEY (`rid`) REFERENCES `somtable2` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251; Query OK, 0 rows affected (0.18 sec) mysql> show create table testtable; +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | testtable | CREATE TABLE `testtable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rid` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `fk_ my_rid` (`rid`), CONSTRAINT `fk_?my_rid` FOREIGN KEY (`rid`) REFERENCES `somtable2` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 | +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) Please see in fk_?my_rid FK (i provide "fk_ my_rid" name)