MySQL Bugs: #47799: Could not modify sql_mode within stored procedure

Bug #47799	Could not modify sql_mode within stored procedure
Submitted:	2 Oct 2009 16:54	Modified:	7 Oct 2009 14:06
Reporter:	virgile crevon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Linux (RH4U6)
Assigned to:		CPU Architecture:	Any
Tags:	autocommit, logon_trigger, routine, SQL_MODE

Description:
I want to create a pseudo logon trigger (using init_connect="call mycode()") to modify autocommit properties and others properties.
But, before achieving that, my procedure, beign called directly under user session, is not able to modify sql_mode (autocommit is functionning properly).

How to repeat:
create a procedure under root and db mysql :
DROP PROCEDURE IF EXISTS mysql.trigger_logon_any_user;
CREATE PROCEDURE mysql."trigger_logon_any_user"()
BEGIN
-- Procedure de positionnement de variables specifiques
-- pour certains utilisateurs
-- par Virgile CREVON
DECLARE v_user CHAR(15) ;
SET v_user = LEFT(user(),instr(user(),'@')-1) ;
	IF v_user in ('vic_adm') THEN
    SET autocommit=0;
		SET sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO' ;
		select 'boucle 1';
	  ELSE
    SET autocommit=0;
		select 'boucle 2';
	  END IF;
END;

grant execute on PROCEDURE mysql.trigger_logon_any_user to 'vic_adm'@'%' ;

then connect onto user (this vic_adm in this exemple) and do :
call mysql.mysql.trigger_logon_any_user() ;

then 
show variables like '%sql_mode%' ;
show session variables like '%sql_mode%' ;
select @@autocommit;

=> you see that just the autocommit part is functionning. The SQL_MODE is not.
if you just type :
SET sql_mode="blabla"; on the command line (or within toad for mysql), i works.

Suggested fix:
Do the SQL_MODE, being called in a procedure, work.....

sorry, the procedure is this one (i mislead the first autocommit part while doing testing) :

DROP PROCEDURE IF EXISTS mysql.trigger_logon_any_user;
CREATE PROCEDURE mysql."trigger_logon_any_user"()
BEGIN
-- Procedure de positionnement de variables specifiques
-- pour certains utilisateurs
-- par Virgile CREVON
DECLARE v_user CHAR(15) ;
SET v_user = LEFT(user(),instr(user(),'@')-1) ;
	IF v_user in ('vic_adm') THEN
          SET autocommit=1;
          SET sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO' ;
          select 'boucle 1';
        ELSE
          SET autocommit=0;
	  select 'boucle 2';
	END IF;
END;

grant execute on PROCEDURE mysql.trigger_logon_any_user to 'vic_adm'@'%' ;

I think that current behavior is intended and is somehow explained in http://dev.mysql.com/doc/refman/5.1/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."

One can assume from the above that server's SQL mode that was in effect before routine call is just restored after the call.

Hi,

Thank you for the answer. This is clearly stated in the documentation (i miss that point ;o). 
I understand clearly that this is the normal behavior of stored procedure (SP) in mysql.

But, it's really ashamed that i cannot "turn off" this mode of SP : 
- because i cannot develop my pseudo LOGON_TRIGGER and will be forced to have many instances of mysql in place of one.
- since the sql_mode is session updatable, it's hard to understand that a SP (despite the way of compiling it) cannot do it and reverse back this sql_mode after executing.

Kind regards,

Virgile

Note that you can use semicolon-separated statements in init-connect. Look:

77-52-242-160:5.1 openxs$ bin/mysqld_safe --init_connect="set autocommit=0; set sql_mode='REAL_AS_FLOAT'" &
[1] 43914
77-52-242-160:5.1 openxs$ 091005 19:51:22 mysqld_safe Logging to '/Users/openxs/dbs/5.1/var/77-52-7-205.dialup.umc.net.ua.err'.
chown: /Users/openxs/dbs/5.1/var/77-52-7-205.dialup.umc.net.ua.err: Operation not permitted
091005 19:51:22 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var

77-52-242-160:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show session variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

OK, it does not work for root user, but you should expect this. Let's create another user:
 
mysql> create user u1@localhost identified by 'u1';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
77-52-242-160:5.1 openxs$ bin/mysql -uu1 -pu1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql> show session variables like 'sql_mode';
+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| sql_mode      | REAL_AS_FLOAT |
+---------------+---------------+
1 row in set (0.00 sec)

Here we are :) Both sql_mode and autocommit is set as we wanted. Now, add some creative use of CASE expression on top (see http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#operator_case) and you may even get the solution you need.

Hi Valeriy,

As suggested i done what you said with case :

mysql> select @@session.sql_mode ;
+---------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select case when LEFT(user(),instr(user(),'@')-1)='vic_adm' then  @myvar:='REAL_AS_FLOAT'
    -> else @myvar:='ansi,no_zero_date,no_zero_in_date,error_for_division_by_zero' end ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| case when LEFT(user(),instr(user(),'@')-1)='vic_adm' then  @myvar:='REAL_AS_FLOAT'
else @myvar:='ansi,no_zero_date,no_zero_in_date,error_for_division_by_zero' end |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REAL_AS_FLOAT                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode=@myvar ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode ;
+--------------------+
| @@session.sql_mode |
+--------------------+
| REAL_AS_FLOAT      |
+--------------------+
1 row in set (0.00 sec)

But i cannot find a way to have it works in init_connect (in /etc/my.cnf) because of the quote mark i think :

init_connect="SET autocommit=0; select case when LEFT(user(),instr(user(),'@')-1)='vic_adm' then  @myvar:='REAL_AS_FLOAT' else @myvar:='ansi,no_zero_date,no_zero_in_date,error_for_division_by_zero' end; set sql_mode=@myvar"

(i gladly notice that you have sucessfully done it with mysqld-safe --init_connect="..... ").

I was not able to found any "init_connect" syntax in the manual (perhaps i blind ;o).

Kind regards,

Virgile