RDS Replication Error (Apply Error 1406 / Truncation)

Question

I have a MySQL RDS instance as a master, created a Read Replica from it, and ran some schema change operations on it. To be specific, I changed the charset and collation of all the tables and columns from utf8 to utf8mb4. Things were replicating fine, but an error just occurred.

Apply Error 1406: Error; Data too long for column... etc

This is due to lowering the varchar length on some columns from 255 to 191.

I read that you can run some commands to skip replication errors, as described here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_skip_repl_error.html

However, would this "skip" the insert, or, just truncate the data and proceed with the insert?

I'd like the data to be truncated and still added to the table rather than aborting the entire operation, but I'm not sure if that is going to happen or not. Any suggestions would be welcome!

Answer 1

Get 5.6.3 (or later). As I understand it, these need to be applied to the Replica:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED

That should allow you to keep 255 (instead of 191), thereby avoiding the truncation.

Answer 2

Take a look at slave_type_conversions parameter in your parameter group on the replica. There's an option to allow truncation due to data type difference between master and replica. Please note this is a static parameter, so it will require a reboot of the RDS instance to take effect.

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_type_conversions

https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html