Bug: Wrong Sql_mode When Copying Functions/procedures

[broy_isoft]

I discovered a somewhat frustrating bug yesterday.  When using SQLYog’s Copy Database To Different Host/Database… dialogue, it produces an easily verified problem behavior when copying functions and stored procedures.  I have confirmed this behavior on SQLYog version 12.2.0 and 12.2.1 (the latest release).

 

To deal with people having explicit 0 values in autoincrement columns, mysqldump (and SQLYog) produce statements to set the session sql_mode to NO_AUTO_VALUE_ON_ZERO, which is correct for exporting the table data.  However, mysqldump resets the sql_mode to the original value after the table data is exported; something SQLYog fails to do.  This produces an issue where functions and procedures are created with a sql_mode of NO_AUTO_VALUE_ON_ZERO instead of whatever they’re supposed to be.  If you have a stored procedure which is supposed to generate rows in a table with an autoincrement column and uses 0 values for “get the next autoincrement sequence value” your procedure has suddenly and inexplicably broken, because functions and procedures use the sql_mode that they were created under, rather than the one that they are executed under.

 

Reproduction steps:

1. Run queries:

   
   
   CREATE DATABASE test_a;
   
   CREATE DATABASE test_b;
   
   USE test_a;
   
   CREATE FUNCTION `test_a`.`test`() RETURNS BOOL RETURN TRUE;

2. Right-Click on test_a in the object browser and choose “Copy Database to Different Host/Database”
3. Select test_b as the target database
4. Click the “Copy” button
5. Click the “Done” button
6. Run the following query:

   
   
   SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE FROM information_schema.ROUTINES
   
   WHERE ROUTINE_SCHEMA='test_a' OR ROUTINE_SCHEMA='test_b';

Those steps just demonstrate that the sql_mode has been changed, since that’s quick and easy; I’d be happy to add a table with an autoincrement problem so that the issue we experienced could be demonstrated; but the root problem is that SQLYog is failing to reset the sql_mode after copying the tables when doing a Copy Database operation.

 

The workaround is pretty easy; since routines inherit the sql_mode from the session that created them, the routine just needs re-created.  Right-click on the function or procedure in the object browser in SQLYog, choose “Alter Function” or “Alter Procedure” as appropriate, and then Execute All Queries in the resulting tab, which will drop the problem routine and re-create it with the current session’s sql_mode but otherwise unchanged.

[Author]

Posts