Using the word ‘function’ in a comment in a MySQL stored procedure causes the RESTORE command to fail

Recently our support team had a call from a customer who suspected that their nightly MySQL backup/restore job wasn’t working.

At most of our customer sites, once the nightly backup has been run, there is a job to restore the backup to a read-only copy of the live database that is used for reports.

Examining the logs confirmed that the RESTORE command was failing with this error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘deficiency if result data is available */’ at line 35212

The syntax being rejected looked suspiciously like a user comment in a stored procedure or user defined function and I vaguely recalled that we’d encountered a similar problem a few years ago where the use of the word ‘function’ caused problems with MySQL’s BACKUP and RESTORE commands. At the time we’d circumvented the problem by changing the comment to remove the word ‘function’.

I ran the following MySQL query to identify the stored procedure that contained the comment:

SELECT * FROM `information_schema`.`routines` WHERE `routine_definition` LIKE ‘%deficiency if result data%’ AND `routine_schema`=DATABASE();

The stored procedure did indeed contain the following comment:

/* Check for functional deficiency if result data is available */

I needed to see what was in the BACKUP file created by mysqldump but opening it in Notepad.exe wasn’t an option as it was 10Gb. Instead, I used a file splitting utility that I wrote a few years ago to extract 1000 lines from the backup file starting at 900 lines before the line number in the error message. I then looked for the start of the stored procedure in the backup extract. I expected to see this:

CREATE DEFINER=`root`@`%` PROCEDURE `sp_analyze_results`()
BEGIN
/* Check for functional deficiency if result data is available */
…

but instead the backup file contained this:

/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`%` PROCEDURE `sp_test`()
BEGIN
/* Check for*/ /*!50003 functional deficiency if result data is available */
…

It appears that the BACKUP command is incorrectly parsing the string ‘function’ when it appears in a user comment.

I experimented to see precisely what triggers the bug and it appears to be the exact string ‘ function’, i.e. a space character followed by the eight characters ‘function’ followed by anything else. For example, ‘ function’, ‘ functional’, ‘ functionality’ will all trigger the bug but ‘*function’ won’t.

Note that although it’s the RESTORE command that fails, it’s the invalid backup file data written by the BACKUP command that is the problem.

I thought it would be worthwhile checking to see if the problem is limited to the FUNCTION keyword or whether other keywords are also affected. I created a test stored procedure that contained each of the several hundred MySQL keywords in its own comment on its own line. The bug is only triggered by the FUNCTION keyword but in the process of conducting the test I also found that it’s only the first occurrence of the FUNCTION keyword that triggers the bug.

Now that I had a reasonably good understanding of what triggers the bug, the next step was to search the MySQL bug reports for a match.

The affected customer has MySQL 5.5.16 installed and MySQL bug report 66035 (applies to MySQL 5.5.23 / .26) and describes the issue exactly. Note the comment in the report, “This is a regression bug, as mysqldump from 5.1.64 does not have this problem.” https://bugs.mysql.com/bug.php?id=66035

Through trial and error I’ve found that the bug is fixed in mysqldump version 5.5.30.