Description:
mysql> use test;
Database changed
mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| sql_mode | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql> show variables like 'collation_connection';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> show variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
->
-> set sql_mode = '';
-> set collation_connection = 'utf8_bin';
-> set lock_wait_timeout = 100;
->
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call dorepeat(10);
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| sql_mode | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql> show variables like 'collation_connection';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.01 sec)
mysql> show variables like 'lock_wait_timeout';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| lock_wait_timeout | 100 |
+-------------------+-------+
1 row in set (0.01 sec)
As can be seen that, only lock_wait_timeout has been changed after the routine finished while, collation_connection and sql_mode both stay the same.
Why? is it a bug ?
How to repeat:
show variables like 'sql_mode';
show variables like 'collation_connection';
show variables like 'lock_wait_timeout';
delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
set sql_mode = '';
set collation_connection = 'utf8_bin';
set lock_wait_timeout = 100;
END //
delimiter ;
call dorepeat(10);
show variables like 'sql_mode';
show variables like 'collation_connection';
show variables like 'lock_wait_timeout';
Suggested fix:
all are changed
or
all stay the same
Description: mysql> use test; Database changed mysql> show variables like 'sql_mode'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | sql_mode | STRICT_ALL_TABLES | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> show variables like 'collation_connection'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_general_ci | +----------------------+--------------------+ 1 row in set (0.00 sec) mysql> show variables like 'lock_wait_timeout'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | lock_wait_timeout | 31536000 | +-------------------+----------+ 1 row in set (0.00 sec) mysql> delimiter // mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> -> set sql_mode = ''; -> set collation_connection = 'utf8_bin'; -> set lock_wait_timeout = 100; -> -> END // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> call dorepeat(10); Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_mode'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | sql_mode | STRICT_ALL_TABLES | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> show variables like 'collation_connection'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_general_ci | +----------------------+--------------------+ 1 row in set (0.01 sec) mysql> show variables like 'lock_wait_timeout'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | lock_wait_timeout | 100 | +-------------------+-------+ 1 row in set (0.01 sec) As can be seen that, only lock_wait_timeout has been changed after the routine finished while, collation_connection and sql_mode both stay the same. Why? is it a bug ? How to repeat: show variables like 'sql_mode'; show variables like 'collation_connection'; show variables like 'lock_wait_timeout'; delimiter // CREATE PROCEDURE dorepeat(p1 INT) BEGIN set sql_mode = ''; set collation_connection = 'utf8_bin'; set lock_wait_timeout = 100; END // delimiter ; call dorepeat(10); show variables like 'sql_mode'; show variables like 'collation_connection'; show variables like 'lock_wait_timeout'; Suggested fix: all are changed or all stay the same