MySQL Bugs: #42910: Triggers override strict sql

Bug #42910	Triggers override strict sql_mode
Submitted:	17 Feb 2009 6:31	Modified:	25 Sep 2014 15:33
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.67 & 5.1.30, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SQL_MODE, trigger

Description:
When working with a strict sql_mode, triggers may behave as if the sql_mode is non-strict.
Specifically, they are allowed performing actions not allowed by STRICT_ALL_TABLES.
An example is provided.

As shown below, the required parameter in my.cnf is:
...
[mysqld]
sql_mode=TRADITIONAL
...

While the code below demonstrates on MyISAM, this happens on InnoDB as well.

Searching through the bug list, I suspect this bug *may* be related to #36677. I'm not sure, though, and this case seems to be different.

How to repeat:
-- Verify sql_mode is TRADITIONAL. Important part is 'STRICT_ALL_TABLES'

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

USE test;

DROP TABLE IF EXISTS a;
CREATE TABLE a (
   id TINYINT UNSIGNED NOT NULL DEFAULT 0
  ) ENGINE=MyISAM;
>> Query OK, 0 rows affected (0.01 sec)

-- We make sure the strict sql_mode works:
INSERT INTO a VALUES (-1);
>> ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1
-- this error is good.

DROP TRIGGER IF EXISTS a_BI;
CREATE TRIGGER a_BI BEFORE INSERT ON a
FOR EACH ROW
  SET NEW.id := -1;
>> Query OK, 0 rows affected (0.02 sec)

-- Here's the problem:
INSERT INTO a VALUES (18);
SELECT * FROM a;

-- We get:
+----+
| id |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

-- whoops!
-- The trigger has changed value to -1, and this has been truncated to 0 instead of prompting with error.

Suggested fix:
Triggers must honor sql_mode.

Thank you for the report.

Verified as described.

Triggers (or any stored program for this matter) are dependent on the SQL mode that was in effect at the time when the trigger was created, which remains in effect for this trigger whenever it is invoked, regardless of the current server SQL mode.

Futhermore, traditional mode only applies to INSERT and UPDATE statements but the bug here is probably that the warning is not being elevated to a error once its propagated back to the diagnostics area of the calling statement.

This seems like a duplicate of Bug#23001

Davi,

please notice that I have shown sql_mode to be TRADITIONAL *before* creating the trigger.

Davi,

"...the warning is not propagated to an error..."
I wouldn't mind that so much, if that were the case, but the case is an invalid value is inserted to the table. So I'm afraid your diagnostic is incorrect.
Regards

SQL_MODE was set before creating trigger in my case too:

set sql_mode='STRICT_ALL_TABLES';

DROP TABLE IF EXISTS a;
CREATE TABLE a (
   id TINYINT UNSIGNED NOT NULL DEFAULT 0
  ) ENGINE=MyISAM;

--error 1264
INSERT INTO a VALUES (-1);

DROP TRIGGER IF EXISTS a_BI;
CREATE TRIGGER a_BI BEFORE INSERT ON a
FOR EACH ROW
  SET NEW.id := -1;

INSERT INTO a VALUES (18);
SELECT * FROM a;

Hi,

To clarify, I further set the sql_mode in my.cnf, and have restarted the server prior to the above test, so the server has started with TRADITIONAL.

I'm aware of the context here, I was just stating some facts about what happens internally and which will help whoever take this bug. The root issue here is that the server currently only aborts for INSERT and UPDATE statements, so if you do something like SET .. = 1/0 it won't abort either.. and this needs to be revisited. From a user perspective the problem may seem that invalidate data ends up in the table but internally the means to avoid this are a bit different as is implemented by a checks performed during INSERT and UPDATE statements that are responsible for propagating (or elevating) any warnings to errors.. and this check is not performed for a SET.

I see. Thanks for explaining!
Cheers

Noted in 5.7.5 changelog.

In strict SQL mode, triggers could permit operations not permitted in
strict mode.