MySQL Bugs: #73399: BEFORE INSERT TRIGGER not fired

Bug #73399	BEFORE INSERT TRIGGER not fired
Submitted:	25 Jul 2014 18:32	Modified:	25 Jul 2014 18:55
Reporter:	Shamshinur Taktayeva	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.6.14, 5.0.97, 5.1.74, 5.5.39, 5.6.21, 5.7.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sql mode, trigger

Description:
When inserted data is longer than column length BEFORE INSERT trigger not fired
and insert fails with the following ERROR:
ERROR 1406 (22001): Data too long for column 'st' at row 8

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
+--------------------------------------------+--------------------------------------------+
| @@GLOBAL.sql_mode                          | @@SESSION.sql_mode                         |
+--------------------------------------------+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+--------------------------------------------+

Looks STRICT_TRANS_TABLES prevents the trigger firing

How to repeat:
CREATE TABLE `long_data_test` (
  `data_is` bigint(20) NOT NULL,
  `st` varchar(25) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create trigger long_values_test BEFORE INSERT ON long_data_test
FOR EACH ROW SET NEW.st=''

insert into log_data_test 
values(1111111111,'Test insert trigger AAAAAAAAAAAAAAAAAAA');

Suggested fix:
Triggers should be able fire regardless of SQL MODE

Thank you for the report.

Verified as described. Although I think that this can be mostly documentation bug.

test case for MTR

Attachment: bug73399.test (application/octet-stream, text), 480 bytes.