MySQL 1366 Error on Replication Slave

MySQLmysql-5.5replication

I've got two pairs of servers (each identical hardware and OS) with replication running on both pairs.
Running MySQL 5.5.34 on Windows Server 2008 R2.

I'm trying to track down a problem that has occurred twice now on on of the pairs.

I get a 1366 error "Incorrect integer value '\x00' for column Flag at row 1" for the following call:

UPDATE mytable SET Flag = 0 where Id = 10

When I issue the show slave status command.

On the other pair I don't ever get this error when replicating.

The type of Flag is tinyint(1).

I haven't been able to identify any differences between the two pairs yet.

Ideas?

Update:
I posted an answer to this below. I believe it has something to do with the serialization of the value when sent from the master to the slave. The representation of the BIT value when replicated is not in a form that can be applied to a column of type TINYINT.

In this case the stored procedure took a flag value as a BIT and used that to update a TINYINT column. While we can agree that those datatypes should match, it is the case that on the master a call to that stored procedure works just fine, meaning the BIT parameter's value is properly converted over to the TINYINT column.

Best Answer

An answer:

After looking into this further, I determined that the call:

UPDATE mytable SET Flag = 0 where Id = 10

was being called also in a stored procedure which took a parameter of type BIT and did something like so:

UPDATE mytable SET Flag = theFlagValue where Id = theId;

While this worked fine on the master - the mytable would be updated with the correct flag value for any rows matching the Id - it wouldn't work on the slave.

My thought on this is that it involves how the data is serialized when sent to the slave. That type mismatch was fine on the master but not on the slave.

I was able to modify that stored procedure to take a TINYINT instead and it works flawlessly now.

Related Question