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

You'll need to export the procedure's definition, then `DROP PROCEDURE` and then re-create it with `CREATE PROCEDURE` under the correct `sql_mode`. — Dai, Dec 12 '21 at 12:43
@Dai You should post that as an answer, otherwise this question will stay in the "unanswered" queue forever. — Bill Karwin, Dec 12 '21 at 15:20

score 1 · Accepted Answer · answered Dec 12 '21 at 15:41

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?
Then DROP PROCEDURE.
Then create a new MySQL session and ensure sql_mode is set correctly.
Then CREATE PROCEDURE with the .sql file you saved in step 1.

Modifying the "settings" of the MySQL stored procedures

1 Answers1