Some dates replicated with a +4 offset

Question

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.

Details:

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?

Musings:

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?

Edit:

After replication synced up, I ran pt-table-checksum on all databases. No errors were found.

Answer 1

So, yes, the problem was the time_zone settings.

As mentioned in the docs, if time_zone=SYSTEM, MySQL will do a timezone calculation, otherwise, it (presumably) will not.

When you INSERT/UPDATE the data as a string, MySQL stores it "as is". If you INSERT/UPDATE the data with NOW(), it stores the current UTC value.

Since my slave had time_zone=US/Eastern, it never did the timezone calculation, so some of my dates were off by +4 hours.

This seems to have fixed the problem of dates in other tables as well.

That's my story and I'm sticking to it. :-)