MySQL Bugs: #67475: Restoring routines from dump file fails

Bug #67475	Restoring routines from dump file fails
Submitted:	5 Nov 2012 15:39	Modified:	28 Jan 2013 13:42
Reporter:	Judy Whiteside	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.44	OS:	Windows (Win 7 Pro x64)
Assigned to:		CPU Architecture:	Any
Tags:	dump, restore

Description:
Using 
Win7 x64 Pro SP2 
MySQL 5.1.45-community 
Mysql workbench 5.2.44 

Could not restore routines using mysql workbench SQL Editor.
The SQL was created by running mysqldump --routines 
The dump worked without errors, and the sql file looked fine, except that everything looked like a comment. When I ran it, there were no errors, but nothing was done. 

I could restore data tables OK that resulted from mysqldump

I then used the mysql workbench server administration connection, and selected Data Import/Restore. 
(For the mysqldump, I had of course selected Data Export on this server panel.) 

It worked! The routines came in just fine. 

Another test:
I dropped the routines and then ran the script from the mysql command line and it worked fine. 

But when I ran the script from mysql workbench (using "open script file in a new query tab"), it did not work. 

It executed the create database line (with the warning that the STATES db already existed, which is ok) 
It executed the 'use states' line ok. 

No other errors or warnings were issued. No function was created. 

The forum thread for my entry "Restoring routines from dump file" advised opening a bug report.

How to repeat:
See attached script to run. It should create one function. 
It does not.

sql script from dumping routines to test restore action

Attachment: restoreRoutinesBug.sql (application/octet-stream, text), 2.42 KiB.

Do you have log-bin enabled? Try using the mysql.exe client and look for the error. Thanks.

I turned on log-bin and ran everything again.

From mysql workbench: same results, no errors, no function created.

From mysql command line, AND from mysql workbench Server panel Data Import area, I got the following error:

15:38:30 Restoring C:\Users\Judy\Downloads\restoreRoutinesBug.sql

Running: mysql.exe --defaults-extra-file="c:\windows\temp\tmpzqjhat.cnf"  --host=127.0.0.1 --user=root --port=3306 --default-character-set=utf8 --comments --database=states < "C:\\Users\\Judy\\Downloads\\restoreRoutinesBug.sql"

ERROR 1418 (HY000) at line 34: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Operation failed with exitcode 1

15:38:30 Import of C:\Users\Judy\Downloads\restoreRoutinesBug.sql has finished with 1 errors

I am also attaching a screenshot of the output from the mysql command line run. It has the same errors, but just slightly different output.

screenshot of mysql cmd line run of restore script

Attachment: restoreRoutinesBug-screenshot.PNG (image/png, text), 151.12 KiB.

Please try version 5.2.45. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".