Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
None
Description
As you know, when a procedure/function/trigger/event is executed, the current SQL_MODE is ignored: the routine runs using the SQL_MODE that was in effect at definition time.
The same should happen with the OLD_MODE. The stored programs are unreliable, if they depend on current session's settings.
MariaDB [test]> \W
|
Show warnings enabled.
|
MariaDB [test]> CREATE OR REPLACE TABLE t ( |
-> id INT PRIMARY KEY |
-> ) ENGINE = InnoDB;
|
Query OK, 0 rows affected (0.53 sec) |
|
|
MariaDB [test]> INSERT INTO t VALUES (1); |
Query OK, 1 row affected (0.08 sec)
|
|
|
MariaDB [test]> DROP PROCEDURE IF EXISTS p; |
Query OK, 0 rows affected (0.02 sec) |
|
|
MariaDB [test]> SET @@global.old_mode = ''; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> DELIMITER ||
|
MariaDB [test]> CREATE PROCEDURE p() |
-> BEGIN |
-> -- duplicate |
-> INSERT IGNORE INTO t VALUES (1); |
-> END || |
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [test]> DELIMITER ;
|
MariaDB [test]> CALL p();
|
Query OK, 0 rows affected, 1 warning (0.00 sec) |
|
|
Warning (Code 1062): Duplicate entry '1' for key 'PRIMARY' |
MariaDB [test]> SET @@global.old_mode = 'NO_DUP_KEY_WARNINGS_WITH_IGNORE'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> CALL p();
|
Query OK, 0 rows affected, 1 warning (0.00 sec) |
|
|
Warning (Code 1062): Duplicate entry '1' for key 'PRIMARY' |
MariaDB [test]> SHOW CREATE PROCEDURE p \G |
*************************** 1. row ***************************
|
Procedure: p |
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p`() |
BEGIN
|
-- duplicate
|
INSERT IGNORE INTO t VALUES (1); |
END
|
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
Database Collation: latin1_swedish_ci |
1 row in set (0.00 sec) |