Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Have you verified that the table definitions are identical on master and slave by doing SHOW CREATE TABLE on both? Based on the only place in the source that seems like it could generate this message, that seems like the first thing to verify. –  Michael - sqlbot Sep 25 '13 at 2:51
    
@Michael-sqlbot yes, I verify that they are identical on both. –  itsmatt Sep 25 '13 at 12:05

1 Answer 1

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.

share|improve this answer
    
+1 for finding your own solution. That almost sounds like you found a bug... MySQL is silently coercing the value into the table on the master, but writing it to the binlog as the original data type, which wouldn't work in this case, since the only kind of coercion of BIT types possible on the slave from a row event would be into a BIT type of a different size. –  Michael - sqlbot Sep 27 '13 at 20:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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