I've set up replication between an EC2 instance as master and a MySQL 5.5 RDS instance as slave. Everything is (mostly) fine except for one odd thing: the dates in a transaction table are either correct or have a +4 offset and the only correlation I can find is in the application code.
EC2 Master is running MySQL 5.5 with time_zone = SYSTEM. The 'date' command shows the master is in EDT (US/Eastern).
RDS Slave is running MySQL 5.5 with time_zone = US/Eastern.
binlog_format is MIXED.
The transaction records are written from two different points in the code because the data being transacted comes from two different vendors, A and B. They are written to the same transactions table. The datatype of field, create_time, is TIME.
When processing Vendor A's data, the application uses
$create_time = Carbon::now()->toTimeString()
to generate the time. The time gets saved to the database and properly replicated to the slave.
When processing Vendor B's data, the application uses NOW() in the SQL statement for create_time. The time gets saved to the master database properly, but is replicated with a +4 hour offset.
I don't understand how correct data in the master is replicated differently based on the application code; once the correct data is written to disk, it's written to disk and that's what is replicated, right?
I wonder if this bug is applicable to my situation.
BTW, I have some other tables where ALL of the dates are either correct or have a +4 offset. No correlations in the application code found there. Yet.
My suspicion is it's the bin_log format. I'm about to dig through the binlogs to see what I can find. If it is, do I need to tear down my replication and start from scratch?
After replication synced up, I ran pt-table-checksum on all databases. No errors were found.