AaronCrane.co.uk

Insanity with dumping MySQL stored procedures

Suppose you have a MySQL database containing stored procedures (which were added in MySQL 5.0.3). Now you dump that database using mysqldump, take the dump, and load it into a different server.

Oops, the dump didn’t contain the stored-procedure definitions, so your code fails. So you read the mysqldump manual, and discover that you need to add a -R (--routines) unbreak-me option. You regenerate the dump with -R, and load it into your other server. That worked; phew. (Assuming the user you dumped as has read permissions on the mysql.proc table, anyway.)

Then you load the dump into a third server. But you weren’t thinking very clearly at this point, and this third server is running 5.0.2, or 4.1, or some other version N−k. What should happen now?

Well, clearly the right thing is for the dump to abort with an error at the point it reaches the first CREATE PROCEDURE statement. After all, you’ve used the magic -R option, which is a pretty clear indication that the code using this database just won’t work without the stored procedures.

But sadly, that’s not what happens.

Instead, MySQL silently ignores it, continuing to load the rest of the file. Specifically, mysqldump generates this excrescence in the dump file:

/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/
/*!50003 FUNCTION `item_valid`(fetched_at int, failed_at int)
  RETURNS tinyint(1)
  NO SQL
  DETERMINISTIC
  RETURN IfNull(fetched_at - failed_at >= 604800, FALSE) */;

That is:

  • You have to specify an unbreak-me option to get the routines to be dumped at all, so if you have routines in your dump, you’ve said that you do actually need them

  • But the dump is specifically engineered to cause your database to break silently when loaded into a server of the wrong version!

Nice one, MySQL.