sql_mode='STRICT_ALL_TABLES' not being enforced when insert via trigger?

[Johann Vincent Paul Tagle]

Using Percona server 5.1.57-rel12.8.  When I joined my current employer one of the first things I noticed is that many of the string columns were using varchar(255), even if real values were much shorter than that.  Finally have the time to make adjustments so I took a look at these columns and set maximum character lengths for each, leaving allowances for some, but actually converting some to char.  But because this is a live site with quite a large database, I want to err on the paranoid side.  What I did is to fire up another slave that will get the updates of production data to the modified tables.  Because replication won't allow the slave tables to have different types and lengths, I did the following:

1.  created copies of the tables to be changed (named them table_name_new), 

2.  modified the "_new" tables to reflect the new varchar lengths

3.  created insert, update and delete triggers so that any change to say, table_1 will be replicated to table_1_new.

4.  modified my.cnf of the new slave to have sql_mode='STRICT_ALL_TABLES'.  Restarted the slave database.

5.  Turned on replication.  

In theory, replication should stop if it encounters an error due to data being too long for a column.  However, I noticed it doesn't work as planned.  To test, I did the following:

On the master: 

create table temp_test_varchar (string varchar(255));

on the slave:

create table temp_test_varchar_new (string varchar(10));

Tested STRICT_ALL_TABLES enforcement:

mysql> insert into tmp_test_varchar_new values ('aaaaa0123456789bb');

ERROR 1406 (22001): Data too long for column 'string' at row 1  --> GOOD

Created insert trigger:

delimiter |

create trigger trunc_test 

  after insert on tmp_test_varchar 

  for each row begin 

    insert into tmp_test_varchar_new select * from tmp_test_varchar where string=NEW.string; 

end;|

Back on the master:

mysql> insert into tmp_test_varchar values ('ll0123456789zz');

Query OK, 1 row affected (0.00 sec)

On the slave:

mysql> select * from tmp_test_varchar_new;

+------------+

| string     |

+------------+

| ll01234567 |

+------------+

1 row in set (0.00 sec)

--> NOT GOOD

Turns out it if I insert the same long data to tmp_test_varchar on the slave, it will give a warning regarding data truncation but will still proceed:

mysql> insert into tmp_test_varchar values ('aaaaa0123456789bb');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+---------------------------------------------+

| Level   | Code | Message                                     |

+---------+------+---------------------------------------------+

| Warning | 1265 | Data truncated for column 'string' at row 2 |

+---------+------+---------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from tmp_test_varchar_new;

+------------+

| string     |

+------------+

| ll01234567 |

| aaaaa01234 |

+------------+

2 rows in set (0.00 sec)

So sql_mode='STRICT_ALL_TABLES' seems to not be really enforced when the insert is done via a trigger.  Any ideas?  Bottom-line I just want to be told if data was truncated.  I would actually prefer that replication doesn't stop so I will catch all instances of data truncation, but log_warnings doesn't seem to cover data truncation warnings.

Thanks

Johann