Bug #68367 Portion of ALTER TABLE statement ignored when COMMENT quoted with backslash
Submitted: 13 Feb 2013 16:53 Modified: 7 Oct 2013 18:30
Reporter: Rasmus Schultz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.5 OS:Microsoft Windows (Windows 8)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[13 Feb 2013 16:53] Rasmus Schultz
Description:
When quoting a COMMENT in an ALTER TABLE statement with backslash, as opposed to using two single quotes, a FOREIGN KEY constraint in another part of the same ALTER TABLE statement may be ignored without error or warning.

I'm guessing this could be parser-related, but I can't be sure.

I'm filing this as S2 (Serious) due to the severe problems this could cause when e.g. deploying incremental schema-changes, when those changes may silently fail, and due to the time it could take someone to identify this problem when encountered.

This issue is repeatable on MySQL 5.5.27 under Windows 8, and MySQL 5.1.67 under Linux - probably on all versions and all operating systems.

How to repeat:
I have posted sample DDL/SQL to make it easy to repeat:

https://gist.github.com/mindplay-dk/4945767
[13 Feb 2013 18:32] Rasmus Schultz
Corrected the version number.
[14 Feb 2013 8:09] Mike Lischke
Changed version. The WB version was used to report a server bug.
[14 Feb 2013 10:20] Umesh Shastry
Hello Rasmus,

Thank you for the report.

Verified as described on reported and later versions.

Thanks,
Umesh
[14 Feb 2013 10:29] Umesh Shastry
Test case and the workarounds.

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.30-log |
+------------+
mysql> USE `test`;
mysql> DROP TABLE IF EXISTS `person`;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE IF NOT EXISTS `person` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
    ->   `password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
    ->   `password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
    ->   `first_name` varchar(64) DEFAULT NULL,
    ->   `last_name` varchar(64) DEFAULT NULL,
    ->   `region_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `email_UNIQUE` (`email`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.';

DROP TABLE IF EXISTS `region`;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DROP TABLE IF EXISTS `region`;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE IF NOT EXISTS `region` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `name` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `test`.`person`
    ->
    -> CHANGE COLUMN `password_hash` `password_hash` CHAR(40) NULL DEFAULT NULL COMMENT '40-character SHA1 of the user\'s password and password_salt.'  ,
    ->
    -> ADD CONSTRAINT `person_region_fk`
    -> FOREIGN KEY (`region_id` )
    -> REFERENCES `test`.`region` (`id` )
    -> ON DELETE SET NULL
    -> ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

^^ No warnings, error reported

mysql> SHOW CREATE TABLE person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
  `password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
  `password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
  `first_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `region_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  KEY `person_region_fk` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.'
1 row in set (0.00 sec)

mysql>

^^ Foreign key definition is lost

#### Workaround - I

### escaped with single quote(') instead of back slash(\)

mysql> DROP TABLE IF EXISTS `region`;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS `region` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `name` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `test`.`person`
    ->
    -> CHANGE COLUMN `password_hash` `password_hash` CHAR(40) NULL DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.'  ,
    ->
    -> ADD CONSTRAINT `person_region_fk`
    -> FOREIGN KEY (`region_id` )
    -> REFERENCES `test`.`region` (`id` )
    -> ON DELETE SET NULL
    -> ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
  `password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
  `password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
  `first_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `region_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  KEY `person_region_fk` (`region_id`),
  CONSTRAINT `person_region_fk` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.'
1 row in set (0.00 sec)

mysql>

^^ FK definition created when single quote is escaped with another single quote..
[14 Feb 2013 10:29] Umesh Shastry
#### Workaround - II
## Break the alter in 2 parts - ALTER TABLE `test`.`person` CHANGE COLUMN...., & ALTER TABLE `test`.`person`...ADD CONSTRAINT

mysql>
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS `person`;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS `person` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
    ->   `password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
    ->   `password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
    ->   `first_name` varchar(64) DEFAULT NULL,
    ->   `last_name` varchar(64) DEFAULT NULL,
    ->   `region_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `email_UNIQUE` (`email`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.';
DROP TABLE IF EXISTS `region`;

Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DROP TABLE IF EXISTS `region`;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS `region` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `name` varchar(50) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> ALTER TABLE `test`.`person`
    ->
    -> CHANGE COLUMN `password_hash` `password_hash` CHAR(40) NULL DEFAULT NULL COMMENT '40-character SHA1 of the user\'s password and password_salt.';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `test`.`person` ADD CONSTRAINT `person_region_fk`
    -> FOREIGN KEY (`region_id` )
    -> REFERENCES `test`.`region` (`id` )
    -> ON DELETE SET NULL
    -> ON UPDATE RESTRICT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) DEFAULT NULL COMMENT 'This Person''s unique, private e-mail address - this is used when logging in.',
  `password_salt` char(40) DEFAULT NULL COMMENT '40-character string used as salt when computing password_hash.',
  `password_hash` char(40) DEFAULT NULL COMMENT '40-character SHA1 of the user''s password and password_salt.',
  `first_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `region_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  KEY `person_region_fk` (`region_id`),
  CONSTRAINT `person_region_fk` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='A Person is any individual known to the CHESS organization.'
1 row in set (0.00 sec)

mysql>

^^ FK definition exists when ALTER is split into two statements
[14 Feb 2013 14:00] Rasmus Schultz
I know about these work-arounds, but they're only useful to someone who can actually even identify the problem - figuring out precisely what was happening took me almost half a day. There is no work-around for that.
[13 Mar 2013 15:17] Daniƫl van Eeden
Isn't this bug a duplicate of Bug #67881 ?
[7 Oct 2013 18:30] Paul Dubois
Fixed by Bug#61656.