Bug #46293 Conditional comments not working in FOREIGN KEY definition
Submitted: 19 Jul 2009 18:25 Modified: 20 Jul 2009 5:32
Reporter: Aleksander Machniak Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.51a-3ubuntu5.4, 4.1, 5.0, 5.1 bzr, azalea OS:Linux
Assigned to: CPU Architecture:Any
Tags: foreign key comment
Triage: Triaged: D4 (Minor)

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