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.