can anyone explain mysql 5.5 handling of replication between two servers.
This is a structure of one of my table.
id int(10) unsigned NOT NULL PRIMARY KEY DEFAULT 'NULL' AUTO_INCREMENT
kot_no varchar(45) NOT NULL DEFAULT 'NULL'
emp_id int(10) unsigned NOT NULL FOREIGN KEY DEFAULT '0'
description varchar(45) NULL DEFAULT 'NULL'
created_date timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
created_by int(10) unsigned NOT NULL DEFAULT '0'
modified_date timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
modified_by int(10) unsigned NULL DEFAULT '0'
state tinyint(1) unsigned NOT NULL DEFAULT '1'
In my database every table has last 5 columns and my binlog format is STATEMENT BASED REPLICATION
When I'm executing INSERT INTO kot (kot_no, emp_id, created_date) VALUES (1, 1, NOW()) on Server A these are the results on two servers.
on SERVER A
id kot_no emp_id description created_date created_by modified_date modified_by state
1 1 1 null 2015-11-01 22:51:57 0 2015-11-01 22:51:57 0 1
on SERVER B
id kot_no emp_id description created_date created_by modified_date modified_by state
1 1 1 null 2015-11-02 12:21:57 0 2015-11-02 12:21:57 0 1
I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here and tell me solution for this without affecting server data?
Problem is MySQL timezone is different in both server
check your timezone for this query :-
SELECT @@global.time_zone;
or you can set timezone like that :-
SET GLOBAL time_zone = '+8:00';//your timezone
SET GLOBAL time_zone = 'Europe/Helsinki';////your timezone
SET @@global.time_zone='+00:00';////your timezone