Bug #46293 Conditional comments not working in FOREIGN KEY definition

• Submitted: 19 Jul 2009 18:25
• Modified: 7 Oct 2019 17:49
• Reporter: Aleksander Machniak
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.0.51a-3ubuntu5.4, 4.1, 5.0, 5.1 bzr, azalea
• OS: Linux
• CPU Architecture: Any
• Tags: foreign key comment

[19 Jul 2009 18:25] Aleksander Machniak

Description:
For:

CREATE TABLE `messages` (
 `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`message_id`),
 CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
   REFERENCES `users` (`user_id`)
   /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */
) /*!40000 ENGINE=INNODB */

foreign key is created without ON DELETE and ON UPDATE clauses. If I remove comment everything's fine. The second conditional comment for table engine is working without problem. 

How to repeat:
CREATE TABLE `users` (
 `user_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(`user_id`)
);
CREATE TABLE `messages` (
 `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
 PRIMARY KEY(`message_id`),
 CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
   REFERENCES `users` (`user_id`)
   /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */
) /*!40000 ENGINE=INNODB */;

[19 Jul 2009 20:06] Peter Laursen

Same for me with server 5.1.36 on Windows7 RC (64 bit)

[20 Jul 2009 5:32] Sveta Smirnova

Thank you for the report.

Verified as described.

[17 Sep 2019 10:27] Dmitry Lenev

Posted by developer:
 
Hello!

The problem has been partially solved by code changes related to transition to new
data-dictionary in 8.0 (so some scenarios started to work correctly in 8.0.3).

The problem has been completely solved by removal of InnoDB foreign key definition
parser in a fix for bug#30287895 "REMOVE DICT_CREATE_FOREIGN_CONSTRAINTS()
AND FRIENDS". Instead we now fully rely on normal SQL-layer parser which handles
conditional comments correctly.

So I am closing this bug as fixed in 8.0.19 and move it to Documenting state to
let Documentation team to update Release Notes accordingly.

[7 Oct 2019 17:49] Paul DuBois

Posted by developer:
 
Fixed in 8.0.19.

Conditional comments in foreign key definitions were ignored.