I have set up two replication slaves, one on CentOS(x86_64 5.1.37), the other on Ubuntu LTS(x86_64 mysql 5.5.38). Both of them seem to replicate most of the Master db (CentOS i686, mysql 5.5.37) correctly, except for one column in a table.

The problematic column was created as int(11) DEFAULT NULL, recording time stamps in Unix epoch, which should not have reached year 2038 yet. However, when I tried to compare data in the Master and Slave, I found recently replicated rows on Slaves have turned into 2147483647, or INT32_MAX. BTW, this is not the case for the Master.

Is binary log format architecture specific? How can I resolve this? Or at least make sure that the DB on Slaves would not record any more abnormal timestamps once switched into Master?

  • 1
    make sure that master/slave are the same major version e.g. 5.5 or else unexpected bad things might happen. – thanasisk Oct 9 '14 at 12:35
  • Yes, I did set up these in a hurry, but the binlog on the slaves does not seem to have inserted a wrong value or an integer overflown. And at least one of the slaves have mysql version near enough from the master. – ZhangChn Oct 9 '14 at 12:59

The cause of problem was stupid.

I double checked the MySQL binary log, and found that insert statements were suspicious.

The column in question has got values like 0x3134...30 as time stamp values, which seems to be a string value rather than intended integer.

And following this clue, I found that the parameter binding calls in PHP side was using a wrong type signature for that column. The Master server silently accepted the wrong type and corrected it by its own, while the Slave servers failed to realize the type issues, and resulted in overflown integers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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