MySQL Incorrect String value replication error.
We use ANT for releasing versions of our PHP applications. Its very smart and takes a lot of the problems related to releases out of the equation.
However, everytime we performed a release, our replication server would break, and I’d have to skip a load of database delta error messages with this command:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1
The error we received is:
Error 'Incorrect string value: 'x9CxF37x12[k...' for column 'applied_by' at row 1' on query. Default database: '<databasename>'.
Query: 'INSERT INTO changelog (change_number, delta_set, complete_dt, applied_by, description)
VALUES (35, 'Main', CURRENT_TIMESTAMP, USER(), '<database_delta_filename>')'.
I’d been getting these errors for a while.
Basically, the MySQL server was replicating the changelog table down to the slave, but for some reason it just would not accept the replicated query.
After a bit of scrummaging around, and changing field values/character sets, I discovered that in the string was “x80” which is a padding character as set out in http://en.wikipedia.org/wiki/ISO_8859-1 and shouldn’t be used in a string. I’m not really sure how it got there, or why, but I basically changed the “applied_by” field to be a BLOB – which is generically a VARCHAR with no character set and there fore ignores this.