foreign key constraints not set by CREATE TABLE statements

[iezzip]

In SQL/mysql.initial.sql foreign key constraints get set using version dependent comments, e.g.

 CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
   /*!40008
     ON DELETE CASCADE
     ON UPDATE CASCADE */

This does not seem to work on MySQL 5.0.51a (tested on Debian Lenny). The CASCADE actions simply get ignored. Consistency is garanteed, but we are not able to e.g. delete a user directly in the database.

To fix the foreign key constraints on a production server without dumping the database, run:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE messages DROP FOREIGN KEY `user_id_fk_messages`;
ALTER TABLE messages ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE;

ALTER TABLE cache DROP FOREIGN KEY `user_id_fk_cache`;
ALTER TABLE cache ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE;

ALTER TABLE contacts DROP FOREIGN KEY `user_id_fk_contacts`;
ALTER TABLE contacts ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE;

ALTER TABLE identities DROP FOREIGN KEY `user_id_fk_identities`;
ALTER TABLE identities ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
   REFERENCES `users`(`user_id`)
     ON DELETE CASCADE
     ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS=1;

[alec]

Known MySQL issue: ​http://bugs.mysql.com/bug.php?id=46293

[alec]

Fixed in [8248e4a6]. Looks like conditional comments are working in ALTER TABLE statements.

[han]

I just dropped the db and recreated it with the latest version of mysql.initial.sql from svn (my users will love me) and yet...

% mysql --version
mysql Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (x86_64) using readline 6.1

mysql> delete from users where username='han@…';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (roundcubemail/messages, CONSTRAINT user_id_fk_messages FOREIGN KEY (user_id) REFERENCES users (user_id))

[alec]

Are you sure? Works for me with 5.0.51a.

[alec]

No, once it works once not. Mysql sucks.

[alec]

Fixed in [94fe9ca1].

[han]

Confirmed. Now it works. I couldn't reimport the old data alas.

[iezzip]

Thanks Alec! That's what I just wanted to propose... simply drop support for MySQL < 4.0.8 - anyway, nobody on earth is using it anymore.

[brandond]

The MySQL syntax commited in [94fe9ca1] is invalid.

ALTER TABLE `identities` INDEX `user_identities_index` (`user_id`, `del`);

should be

ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);

[alec]

Fixed in [ac756e88].