| 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: | |
| 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 12:11]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
Changing category, as this turns out to be a code bug .......
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.