MySQL Bugs: #68367: Portion of ALTER TABLE statement ignored when COMMENT quoted with backslash

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:	Windows (Windows 8)
Assigned to:		CPU Architecture:	Any

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

Corrected the version number.

Changed version. The WB version was used to report a server bug.

Hello Rasmus,

Thank you for the report.

Verified as described on reported and later versions.

Thanks,
Umesh

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..

#### 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

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.

Isn't this bug a duplicate of Bug #67881 ?

Fixed by Bug#61656.