1

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!

3
  • Some setting is causing the Error instead of just a Warning.
    – Rick James
    Jul 2, 2015 at 5:30
  • You made schema changes on the replica, directly? Jul 2, 2015 at 8:06
  • Yeah, the schema changes were made on the replica.
    – dylst
    Jul 6, 2015 at 17:31

2 Answers 2

0

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.

6
  • I did this but ran into a duplicate key replication error. I've seen this error on our master, most likely due to an application error. Since this happened on the slave, it looks like it stopped replicating.
    – dylst
    Jul 6, 2015 at 17:30
  • Dup key occurred when you lowered it to 191?
    – Rick James
    Jul 6, 2015 at 20:48
  • Yes, exactly. After the schema changes.
    – dylst
    Jul 8, 2015 at 16:31
  • After which schema change? Shrink to 191, where you could expect truncated text and/or dup keys? Or after ROW_FORMAT=DYNAMIC?
    – Rick James
    Jul 8, 2015 at 16:46
  • After the truncation change to 191
    – dylst
    Jul 9, 2015 at 21:47
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.