MySQL Bugs: #7717: replication fails on update/delete with timestamp field in "where"

Bug #7717	replication fails on update/delete with timestamp field in "where"
Submitted:	6 Jan 2005 21:06	Modified:	18 Jan 2005 20:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1.8, 5.0.2a	OS:	Windows (Windows 2000)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Update and delete statement are not replicated if timestamp field is present in where clause. It fails to replicate for both InnoDB and MyISAM engines. Strangely, it is recorded on a slave's relay binlog, but slave's table is not updated. Replication fails for both InnoDB and MyISAM engines. Didn't test it on other platforms. Master is Solaris 8 with 4.1.8 standard. Slave is Windows 2000.

How to repeat:
Configure Master-Slave replication.

# on master:

CREATE TABLE `test` (
`id` int NOT NULL auto_increment,
`test` varchar(100),
`time_stamp` timestamp,
PRIMARY KEY(`id`));

insert into test(test) values ('A'),('B'),('C');

select * from test;

+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-06 12:55:17 |
|  2 | B    | 2005-01-06 12:55:17 |
|  3 | C    | 2005-01-06 12:55:17 |
+----+------+---------------------+
3 rows in set (0.10 sec)

# on slave result of the select will be the same

# on master
delete from test where id=2 and time_stamp='2005-01-06 12:55:17';
# Query OK, 1 row affected (0.14 sec)
update test set test='*' where id=1 and time_stamp='2005-01-06 12:55:17';
# Query OK, 1 row affected (0.14 sec)
# Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | *    | 2005-01-06 13:01:46 |
|  3 | C    | 2005-01-06 12:55:17 |
+----+------+---------------------+
2 rows in set (0.09 sec)

# on slave - NO CHANGES!
mysql> select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-06 15:55:17 |
|  2 | B    | 2005-01-06 15:55:17 |
|  3 | C    | 2005-01-06 15:55:17 |
+----+------+---------------------+
3 rows in set (0.21 sec)

Suggested fix:
Do not use timestamp field in where statements for update and deletes. It is problematic for MS Access clients connected to a master server over ODBC because MS Access or MyODBC add timestamp condition on updates and deletes.

Hi,
Tested with 4.1.8 on Linux/Linux, and got in the slave the good results.
Something I don't understand in your testcase:
# on master:
select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-06 12:55:17 |
|  2 | B    | 2005-01-06 12:55:17 |
|  3 | C    | 2005-01-06 12:55:17 |
+----+------+---------------------+
# on slave result of the select will be the same
# on master
delete from test where id=2 and time_stamp='2005-01-06 12:55:17';
update test set test='*' where id=1 and time_stamp='2005-01-06 12:55:17';
# on slave - NO CHANGES!
mysql> select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-06 15:55:17 |
|  2 | B    | 2005-01-06 15:55:17 |
|  3 | C    | 2005-01-06 15:55:17 |
+----+------+---------------------+

You say "no changes", but at least the time_stamp changed in slave,  it used to be
12:55:17 and now it's 15:55:17........

There is nothing strange in this case. The master is located on West coast and time in PST, and the slave is located on East coast and time in EST. Could it be the reason for replication problem?

I test it for the same time zone for master and slave, and it works fine, but won't work for master and slave in different time zones. Please try to run the same test when master and slave are located in different tz. Meanwhile I will make master and slave tz the same in my setup to make replication work. Thanks!

It could indeed be the reason.
Please run:
# on master:
CREATE TABLE `test` (
`id` int NOT NULL auto_increment,
`test` varchar(100),
`time_stamp` timestamp,
PRIMARY KEY(`id`));
insert into test(test) values ('A'),('B'),('C');
select * from test;
# on slave
select * from test;
and compare the output of the two SELECTs (and please paste it here by the way). If you visually see a difference, that could indeed be the reason (we'll investigate more after we know if you see a difference). Thanks!

ah, sorry! I didn't get your last post when I wrote mine. Ok, I read that when timezones match it works.
Please take a look at these two links:
http://dev.mysql.com/doc/mysql/en/Timezone_problems.html
http://dev.mysql.com/doc/mysql/en/Time_zone_support.html
I think it explains it. If you find a solution there, or not, please notify.

Bigo! Finnaly I know exactly how to repeat it. First, queries you have requested:

# ######### master

mysql> CREATE TABLE `test` (
    ->
    -> `id` int NOT NULL auto_increment,
    ->
    -> `test` varchar(100),
    ->
    -> `time_stamp` timestamp,
    ->
    -> PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.23 sec)

mysql>
mysql> insert into test(test) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-07 21:09:06 |
|  2 | B    | 2005-01-07 21:09:06 |
|  3 | C    | 2005-01-07 21:09:06 |
+----+------+---------------------+
3 rows in set (0.20 sec)

# ########## slave

mysql> select * from test;
+----+------+---------------------+
| id | test | time_stamp          |
+----+------+---------------------+
|  1 | A    | 2005-01-08 00:09:06 |
|  2 | B    | 2005-01-08 00:09:06 |
|  3 | C    | 2005-01-08 00:09:06 |
+----+------+---------------------+
3 rows in set (0.00 sec)

Time_stamp on slave is off by 3 hours. Here are %time% variables from master and slave:

# ###### master

mysql> show global variables like '%time%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| connect_timeout          | 5                     |
| datetime_format          | %Y-%m-%d %H:%i:%s     |
| delayed_insert_timeout   | 300                   |
| flush_time               | 1800                  |
| innodb_lock_wait_timeout | 50                    |
| interactive_timeout      | 28800                 |
| long_query_time          | 10                    |
| net_read_timeout         | 30                    |
| net_write_timeout        | 60                    |
| slave_net_timeout        | 3600                  |
| slow_launch_time         | 2                     |
| system_time_zone         | Pacific Standard Time |
| time_format              | %H:%i:%s              |
| time_zone                | SYSTEM                |
| wait_timeout             | 28800                 |
+--------------------------+-----------------------+
15 rows in set (0.20 sec)

# ########## slave

mysql> show global variables like '%time%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| connect_timeout          | 5                     |
| datetime_format          | %Y-%m-%d %H:%i:%s     |
| delayed_insert_timeout   | 300                   |
| flush_time               | 1800                  |
| innodb_lock_wait_timeout | 50                    |
| interactive_timeout      | 28800                 |
| long_query_time          | 10                    |
| net_read_timeout         | 30                    |
| net_write_timeout        | 60                    |
| slave_net_timeout        | 3600                  |
| slow_launch_time         | 2                     |
| system_time_zone         | Eastern Standard Time |
| time_format              | %H:%i:%s              |
| time_zone                | SYSTEM                |
| wait_timeout             | 28800                 |
+--------------------------+-----------------------+
15 rows in set (0.00 sec)

The bug can be repeated only if system_time_zone variables are different on master and slave and time_zone on both is SYSTEM. It was tested for master PST (Solaris 8 and W2k) and slave EST (W2k).

Work around: 
Set default-time-zone variable on master and slave to the same correct values, for example US/Pacific. The only proble that "select now()" will give current time matching to time_zone regardless system_time_zone.

Yes, it's a limitation of the current replication (which we apparently forgot to document :-( ): master and slave must have the same global timezone; then we support if a connection uses  a different session timezone on master; but the global values have to be the same.
I'll make sure to document it. This limitation will be lift in 5.x.

Also for replication to work correctly default-time-zone has to be set the same zone for master and server if system_time_zone are different on master and slave. It will fail if left to default SYSTEM values. Would be great to have some error or warning message on slave if default-time-zone is not set.

Thanks!

Hi! Thank you for you comments!

I have added note about time zone settings required for replication to "Replication Features and Known Problems" section of our manual (http://dev.mysql.com/doc/mysql/en/Replication_Features.html).

BTW I would like to point out that it is sensible to use the same system time zone for both Master and Slave servers. Otherwise you may encounter problems with replication of statements which are executed from connections with time zone is set to 'SYSTEM'.
(The other option would be completely avoid usage of 'SYSTEM' time zone but this seems a bit too restrictive to me...)

You can set system time zone in which MySQL server is run using --timezone option
of mysqld_safe script.