Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Changing Server SQL Mode not apply to stored procedures
SET sql_mode='NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query: insert into bonus_results (calc_date, id_prod_type, id_user) value ('2012-08-00', 1, 2)
Error Code: 1292
Incorrect date value: '2012-08-00' for column 'calc_date' at row 1
It's right, but same SQL in stored procedures continue work 
Workaround only recreate all stored procedures
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Changing Server SQL Mode not apply to stored procedures
Hi,
Please check MySQL documentation, specifically http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html:
"MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked."
Re: Changing Server SQL Mode not apply to stored procedures
That's too bad it is a potential source of errors and glitches in serious applications using MySQL. I think this behavior should be changed. sql_mode specified by the application itself must also apply to the code inside the stored procedures.
Re: Changing Server SQL Mode not apply to stored procedures
Or, in your variant it can become a breach of security, if a DBA creates a routine which is supposed to be executed with certain SQL_MODE, and then any user can redefine this mode.
Anyway, you can always file a feature request for the change in MySQL (or in MariaDB, for that matter).
Re: Changing Server SQL Mode not apply to stored procedures
Thanks Elena. When I can file a feature request?
Re: Changing Server SQL Mode not apply to stored procedures
Thanks Elena. Where I can file a feature request?
Re: Changing Server SQL Mode not apply to stored procedures
Hi,
I would say the most appropriate place is http://bugs.mysql.com/.
If you, however, want to file it specifically for MariaDB, you can convert this one to a feature request (change the importance to 'Wishlst'). But to make it worthwhile, you will also need to provide good enough reasoning why the current behavior, when a DBA defines SQL_MODE upon a storage procedure creation, is more dangerous than you suggestion, when any user can override SQL_MODE defined by the DBA and execute the procedure as they please.
Better still, and more efficient too, would it be if you provided a patch which implements the behavior you are after and takes care about possible security problems.
Thanks.
Launchpad bug id: 1028296
Re: Changing Server SQL Mode not apply to stored procedures
The situation is very bad, because that is created by the developers of stored procedures from a wrong configured session will lead to their procedures will not work properly in a production environment.