Modifying the "settings" of the MySQL stored procedures

Question

According to the MySQL documentation, stored procedures are executed with the sql_mode in which they were created.

I need to change the sql_mode parameter for a specific stored procedure

SHOW CREATE PROCEDURE command - shows the current sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

How can it be changed, specifically - remove strict mode

Thanks

Answer 1

1. Export the procedure's definition with SHOW CREATE PROCEDURE.
   • Save it to a .sql file on-disk for safe-keeping and ensure it matches the procedure in your project's source-control (git, SVN, TFS, etc).
     • ...and you are using source-control for your database project, right?
2. Then DROP PROCEDURE.
3. Then create a new MySQL session and ensure sql_mode is set correctly.
4. Then CREATE PROCEDURE with the .sql file you saved in step 1.