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
--routines) unbreak-me option. You regenerate the dump with
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) */;
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.