MySQL Bugs: #24610: session SQL_MODE can affect other sessions through triggers

Bug #24610	session SQL_MODE can affect other sessions through triggers
Submitted:	27 Nov 2006 8:08	Modified:	11 Jan 2007 20:34
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.1.14,5.0.27	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	Docs, SQL_MODE, triggers

Description:
A trigger created under a session SQL_MODE is executed in another session under the SQL mode of the definer.
Thus, the same operation, within the same session, can lead to different results.

For example:

# SESSION 1

set sql_mode = '';
drop table if exists t1;
create table t1 (
x tinyint unsigned not null default 0,
y tinyint unsigned not null default 0,
z tinyint not null default 0,
primary key (x,z)
);
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

create trigger t1_bi before insert on t1 for each row
set new.z = new.x - new.y;
insert into t1 (x, y) values (1,2);

select * from t1;
x       y       z
1       2       -1

# SESSION 2
drop trigger t1_bi;
set sql_mode = '';
create trigger t1_bi before insert on t1 for each row
set new.z = new.x - new.y;

# SESSION 1
insert into t1 (x, y) values (3,4);
select * from t1;
x       y       z
1       2       -1
3       4       127

How to repeat:
--disable_warnings
drop table if exists t1;
--enable_warnings

set sql_mode = '';
create table t1 (
    x tinyint unsigned not null default 0,
    y tinyint unsigned not null default 0,
    z tinyint not null default 0,
    primary key (x,z)
);

connect (session1,localhost,root,,);
connect (session2,localhost,root,,);

--enable_info
connection session1;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
create trigger t1_bi before insert
on t1
for each row
set new.z = new.x - new.y;

select @@sql_mode;
insert into t1 (x, y) values (1,2);
select * from t1;

connection session2;

drop trigger t1_bi;
create trigger t1_bi before insert
on t1
for each row
set new.z = new.x - new.y;

connection session1;
select @@sql_mode;

--disable_warnings
insert into t1 (x, y) values (3,4);
--enable_warnings
select * from t1;

Suggested fix:
none

After lots of discussion on this subject, it turns out that this is not a bug.
However, it would be beneficial to add this example in the documentation, to show the risks of changing SQL mode.

In the documentation, we should use Giuseppe's new example: http://datacharmer.blogspot.com/2006/11/hidden-risks-of-sql-mode.html

I guess we can copy it almost verbatim -- thanks, Giuseppe!

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have updated the manual to indicate that, as for stored routines,
triggers execute with the sql_mode value in effect at the time of
object creation. This is also true for events, so I have noted that
as well.