[MDEV-10769] Incorrect time returned by now() in trigger, in replication Created: 2016-09-08  Updated: 2016-10-12  Resolved: 2016-10-12

Status: Closed
Project: MariaDB Server
Component/s: Replication, Time zones, Triggers
Affects Version/s: 10.1.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Triton Ho Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Master DB: MySQL 5.6@Amazon EC2
Slave DB: MariaDB 10.1.14@Amazon RDS


 Description   

We have setup a replication from MySQL Master, to MariaDB.
if we perform "select now();" in mysql console, both master and mariadb return correct time.

However, the now() in trigger is incorrect.
(Remarks: the timezone in Mariadb is "US/Eastern", the timezone in MySQL is "America/New_York")

------------------------------------------------------------------------------------
Here is the script to reproduce the bug:

select now();
 
/*both mysql and mariadb return '2016-09-07 22:09:47' */
 
 
 
/*In mysql: */
 
CREATE TABLE `table_a` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `price` int(11) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;
 
 
 
CREATE TABLE `table_a_log` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `price` int(11) NOT NULL,
 
  `logged_at` datetime NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;
 
 
 
DELIMITER ;;
 
CREATE TRIGGER table_a_trigger AFTER UPDATE ON table_a
 
  FOR EACH ROW
 
  BEGIN
 
      INSERT INTO table_a_log (id, price, logged_at)
 
      VALUES (NEW.id, NEW.price, NOW());
 
  END;;
 
DELIMITER ;
 
 
 
insert into table_a(id, price) values (90, 9999);
 
update table_a set price = 8888;

Result

 
The table_a_log in mysql has time '2016-09-07 22:09:47'.
 
The table_a_log in mariadb has time '2016-09-08 02:09:47'.



 Comments   
Comment by Elena Stepanova [ 2016-09-14 ]

Are you using statement-based replication?

When you're saying the timezone in Mariadb is "US/Eastern" or the timezone in MySQL is "America/New_York", what does it mean, exactly? That the timezones on the systems running the servers are configured this way, or that the timezones in MariaDB/MySQL servers themselves are set explicitly? In other words, what are the values of @@time_zone on these machines?

If you are running statement-based replication and using system time zones, the behavior that you observe is expected. Otherwise please attach the output of show variables from both servers.

Comment by Elena Stepanova [ 2016-10-12 ]

Please comment to re-open if you have further information on the issue.

Generated at Thu Feb 08 07:44:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.