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

[25 Jul 2014 18:32] Shamshinur Taktayeva
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
[25 Jul 2014 18:55] Sveta Smirnova
Thank you for the report.

Verified as described. Although I think that this can be mostly documentation bug.
[25 Jul 2014 18:56] Sveta Smirnova
test case for MTR

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