Bug #12680 Timezone replication broken by replicate-wild-ignore-table=mysql.%

• Submitted: 19 Aug 2005 17:43
• Modified: 19 Aug 2005 18:00
• Reporter: Dean Ellis
• Status: Duplicate
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 4.1
• Assigned to: Assigned Account
• CPU Architecture: Any

[19 Aug 2005 17:43] Dean Ellis

Description:
When altering the session time zone during a transaction, replicating statements which affect timestamps will fail if replicate-wild-ignore-table=mysql.% is in effect.

How to repeat:
my.cnf:
[mysqld]
replicate-wild-ignore-table=mysql.%

master:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a TIMESTAMP PRIMARY KEY ) ENGINE=InnoDB;
SET TIME_ZONE=CST6CDT;
BEGIN;
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
SET TIME_ZONE=EST5EDT;
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
COMMIT;

slave:
SHOW SLAVE STATUS\G

SQL thread should stop, reporting duplicate key violation.
Removing replicate-wild-ignore-table option, and this transaction succeeds on the slave.

Suggested fix:
n/a

[19 Aug 2005 17:45] Dean Ellis

When the duplicate key violation occurs on the slave, try this as well:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE SQL_THREAD;

The duplicate key error will occur again.

SHOW SLAVE STATUS\G

Exec_Master_log_Pos will now point to a position which is invalid on the master, though Relay_Log_Pos remains accurate.

[19 Aug 2005 18:00] Dean Ellis

Duplicate of 12542; different test case but same problem.