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:
None 
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 6:50] Виталий Евсюков
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)
[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