Bug #92958 Event Creation not respecting SQL_MODE settings
Submitted: 26 Oct 2018 9:21 Modified: 16 Nov 2018 16:26
Reporter: IGG t Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.7.22 OS:Debian
Assigned to: CPU Architecture:x86
Tags: Event, SQL_MODE

[26 Oct 2018 9:21] IGG t
Description:
When creating a Scheduled Event it doesn't appear to be respecting the sql_mode settings. 

My sql_mode is currently set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.

According to the documentation:

"Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled,"

If I try and insert data into a table that is too long for a column I get a warning (as expected). 
But if I try and insert an event where the data is too long for a column it generates an error. 

As I am currently replicating from a MySQL 5.5 Master, this breaks replication, as MySQL 5.5 acts as expected and simply truncates the data.

How to repeat:
SET @@session.sql_mode= 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

CREATE TABLE `test5` (`col1` char(2) NOT NULL);
insert into test.test5 (col1) values ('ccc');

1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'col1' at row 1

CREATE 
    EVENT `ev_test` 
    ON SCHEDULE EVERY 1 DAY 
    STARTS NOW() 
    COMMENT 'THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST ' 
    DO 
    SELECT 1+1;

Error Code: 1552. Data for column 'comment' too long

SET @@session.sql_mode= 'STRICT_TRANS_TABLES';

insert into test.test5 (col1) values ('ccc');

Error Code: 1406. Data too long for column 'col1' at row 1

Suggested fix:
Make Events respect the sql_mode.
[26 Oct 2018 12:11] Sinisa Milivojevic
HI,

This inconsistency in behaviour is expected, since EVENTS are always functioning in the strict mode, regardless of how the global mode is set.

However, this is not fully documented in our Referenced Manual, which must be addressed.

Verified as a documentation bug.
[14 Nov 2018 12:57] Sinisa Milivojevic
Hi,

I have got the information from our documentation department that this behaviour is already documented.

Hence, not a bug.
[15 Nov 2018 14:09] Sinisa Milivojevic
This is also described in our manual:

https://dev.mysql.com/doc/refman/8.0/en/create-event.html

where you can read (among other important info):

"
MySQL stores the sql_mode system variable setting in effect when an event is created or altered, and always executes the event with this setting in force, regardless of the current server SQL mode when the event begins executing.
"
[15 Nov 2018 14:40] IGG t
However, my original point was about Creating an event, not executing:

"My sql_mode is currently set to NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.

If I try and insert data into a table that is too long for a column I get a warning (as expected). 

But if I try and insert [CREATE] an event where the data is too long for a column it generates an error.

CREATE 
    EVENT `ev_test` 
    ON SCHEDULE EVERY 1 DAY 
    STARTS NOW() 
    COMMENT 'THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST ' 
    DO 
    SELECT 1+1;

Error Code: 1552. Data for column 'comment' too long"
[15 Nov 2018 15:57] Sinisa Milivojevic
Hi,

Try putting COMMENT in a single row.
[16 Nov 2018 14:35] IGG t
Putting it all on a single line makes no difference:

SET @@GLOBAL.sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET @@SESSION.sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

CREATE TABLE `test5` (`col1` char(2) NOT NULL);
insert into test.test5 (col1) values ('ccc');
#1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'col1' at row 1 

CREATE EVENT `ev_test` ON SCHEDULE EVERY 1 DAY STARTS NOW() COMMENT 'THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST' DO SELECT 1+1;
#Error Code: 1552. Data for column 'comment' too long

SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES';
SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES';

CREATE TABLE `test6` (`col1` char(2) NOT NULL);
insert into test.test6 (col1) values ('ccc');
#Error Code: 1406. Data too long for column 'col1' at row 1

CREATE EVENT `ev_test` ON SCHEDULE EVERY 1 DAY STARTS NOW() COMMENT 'THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST THIS IS A TEST' DO SELECT 1+1;
#Error Code: 1552. Data for column 'comment' too long
[16 Nov 2018 16:26] Sinisa Milivojevic
Hi,

I have tested your test case and repeated it. I have even set both sql_modes to empty character, id est ''.

Hence, strict sql_mode is always enforced on that column.

We have yet to check and investigate whether it is a code bug .....

Verified again ...
[20 Nov 2018 14:30] Sinisa Milivojevic
Changing category, as this turns out to be a code bug .......