[MDEV-6363] OLD_MODE and stored programs Created: 2014-06-19  Updated: 2014-06-21  Resolved: 2014-06-21

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

Type: Task Priority: Major
Reporter: Federico Razzoli Assignee: Michael Widenius
Resolution: Won't Fix Votes: 0
Labels: old-mode


 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)



 Comments   
Comment by Elena Stepanova [ 2014-06-19 ]

It's the documented behavior: https://mariadb.com/kb/en/old_mode/
Could you please explain in which way it is unreliable? I mean, there are many session's settings that are applicable to a stored program execution, why OLD_MODE makes it worse?

Comment by Federico Razzoli [ 2014-06-19 ]

Sorry, I didn't notice, so this is not a bug. But still, this difference between OLD_MODE and SQL_MODE confuses me. Maybe I'm missing something, but I want my procedure to always or never fail on division by zero (sql_mode), and similarly I want it to always/never generate duplicate key warnings (old_mode).

Comment by Elena Stepanova [ 2014-06-19 ]

I will convert it into a feature request and assign to Monty (who added OLD_MODE), in case he wants to reconsider, or at least to comment on this.

Comment by Michael Widenius [ 2014-06-19 ]

It's a bit complex storing all variables that may affect usage stored procedures.
For each variable, we have to create a syntax for it and also create a new entry in mysql.proc. Adding a variable between releases is not desirable as it would make mysql.proc incompatible with previous versions.

old_mode is especially problematic as this is used to emulate behavior from a few previous MySQL/MariaDB versions to allow easy upgrades. Over time we plan to deprecate options as applications has catched on to the new behavior.
If there is old behavior that is desirable for many, we will add it to SQL_MODE.

If this is very important for you, what we could do is consider moving NO_DUP_KEY_WARNINGS_WITH_IGNORE to SQL_MODE. Would that be an acceptable solution for you?

Regards,
Monty

Comment by Sergei Golubchik [ 2014-06-20 ]

f_razzoli, the difference between SQL_MODE and OLD_MODE is conceptual.

Every value in the SQL_MODE tells you that a server can work in one of the two modes (e.g. ANSI_QUOTES and no-ANSI_QUOTES), and you can choose either way. Sometimes these modes affect stored routines, that's why a definition of the stored routine includes the active SQL_MODE at the moment of the routine creation.

OLD_MODE, while similar, is nothing more than a glorified --old command line switch. Something that helps to upgrade but still have the old behavior temporarily, until you fix your application. Just like enabling or disabling --old might affect stored routines, so might OLD_MODE. Just like --old it is not stored per stored routine, and just like --old it's not replicated to slaves. You aren't really supposed to be using it on a permanent basis, it's like a small spare tire, it can get you going, but you should replace it as soon as possible.

Comment by Federico Razzoli [ 2014-06-21 ]

I understand. Also, I didn't consider the possible upgrade problems is you add a field in mysql.proc. No, I don't really need NO_DUP_KEY_WARNINGS_WITH_IGNORE in SQL_MODE, I'll just modify my procedures to suppress the warnings. Thank you.

Generated at Thu Feb 08 07:11:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.