[MDEV-3769] LP:1028296 - Changing Server SQL Mode not apply to stored procedures Created: 2012-07-24  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: NT Man (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1028296.xml    

 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



 Comments   
Comment by NT Man (Inactive) [ 2012-07-24 ]

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.

Comment by Elena Stepanova [ 2012-07-24 ]

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."

Comment by NT Man (Inactive) [ 2012-07-26 ]

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.

Comment by Elena Stepanova [ 2012-07-30 ]

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).

Comment by NT Man (Inactive) [ 2012-07-31 ]

Re: Changing Server SQL Mode not apply to stored procedures
Thanks Elena. When I can file a feature request?

Comment by NT Man (Inactive) [ 2012-07-31 ]

Re: Changing Server SQL Mode not apply to stored procedures
Thanks Elena. Where I can file a feature request?

Comment by Rasmus Johansson (Inactive) [ 2012-07-31 ]

Launchpad bug id: 1028296

Comment by Elena Stepanova [ 2012-07-31 ]

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.

Generated at Thu Feb 08 06:51:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.