Bug #22877 replication character sets get out of sync using replicate-wild-ignore-table
Submitted: 2 Oct 2006 4:07 Modified: 17 Nov 2006 18:41
Reporter: Sean Pringle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.24a OS:Linux (Linux)
Assigned to: Alexander Barkov CPU Architecture:Any

[2 Oct 2006 4:07] Sean Pringle 
Description:
replicate-wild-ignore-table appears to allow character set to change from Master to Slave across replication.

How to repeat:
Start replication Slave with:

replicate-wild-ignore-table=%.tmptbl%

All character set variables are identical on both servers.

On the Master:

CREATE DATABASE foo;

USE foo;

CREATE TABLE `t1` (
`word` varchar(50) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SET @@session.character_set_client=33,@@session.collation_connection=192;

CREATE TEMPORARY TABLE tmptbl504451f4258$1 (id INT NOT NULL, weight INT NULL, privil INT NULL) ENGINE=MEMORY ;

INSERT INTO t1 (word)  VALUES ('TEST’');

Note trailing character above ^^^

Then on the Master:

mysql> select * from t1;
+-------+
| word  |
+-------+
| TEST� |
+-------+
1 row in set (0.00 sec)

On the Slave:

mysql> select * from t1;
+---------+
| word    |
+---------+
| TEST’ |
+---------+
1 row in set (0.00 sec)

Note trailing characters are no different.

Binary and relay logs appear identical:

Master Binary Log:

#061002 13:30:28 server id 1  end_log_pos 258   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759828;
create database foo;
# at 258
#061002 13:31:28 server id 1  end_log_pos 472   Query   thread_id=2     exec_time=1     error_code=0
use foo;
SET TIMESTAMP=1159759888;
CREATE TABLE `t1` (
`word` varchar(50) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
# at 472
#061002 13:31:29 server id 1  end_log_pos 642   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759889;
SET @@session.pseudo_thread_id=2;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=192,@@session.collation_server=8;
CREATE TEMPORARY TABLE tmptbl504451f4258$1 (id INT NOT NULL, weight INT NULL, privil INT NULL) ENGINE=MEMORY;
# at 642
#061002 13:31:29 server id 1  end_log_pos 114   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759889;
INSERT INTO t1 (word)  VALUES ('TEST’');
# at 756
#061002 13:31:29 server id 1  end_log_pos 783   Xid = 15
COMMIT;
# at 783
#061002 13:33:04 server id 1  end_log_pos 915   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759984;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `foo`.`tmptbl504451f4258$1`;
# End of log file

Slave Binary Log:

#061002 13:30:28 server id 1  end_log_pos 258   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759828;
create database foo;
# at 395
#061002 13:31:28 server id 1  end_log_pos 472   Query   thread_id=2     exec_time=1     error_code=0
use foo;
SET TIMESTAMP=1159759888;
CREATE TABLE `t1` (
`word` varchar(50) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
# at 609
#061002 13:31:29 server id 1  end_log_pos 642   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759889;
SET @@session.pseudo_thread_id=2;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=192,@@session.collation_server=8;
CREATE TEMPORARY TABLE tmptbl504451f4258$1 (id INT NOT NULL, weight INT NULL, privil INT NULL) ENGINE=MEMORY;
# at 779
#061002 13:31:29 server id 1  end_log_pos 114   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759889;
INSERT INTO t1 (word)  VALUES ('TEST’');
# at 893
#061002 13:31:29 server id 1  end_log_pos 783   Xid = 15
COMMIT;
# at 920
#061002 13:33:04 server id 1  end_log_pos 915   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1159759984;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `foo`.`tmptbl504451f4258$1`;
# End of log file
[2 Oct 2006 4:22] Sean Pringle 
Test Case

Attachment: test_case.sql (text/x-sql), 403 bytes.
[2 Oct 2006 11:37] Sveta Smirnova 
Thank you for the report.

Verified on Linux as described using last BK sources.
[2 Oct 2006 11:49] Sveta Smirnova 
Character sets both on master and slave before test were in default latin1.
[20 Oct 2006 10:40] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14043

ChangeSet@1.2299, 2006-10-20 15:40:48+05:00, bar@mysql.com +4 -0
  Bug#22877 replication character sets get out of
  sync using replicate-wild-ignore-table
  Problem: changes in character set variables
  before an action on an replication-ignored table
  makes slave to forget new variable values.
  Fix: don't initialize one_shot variables when
  all_tables_not_ok() - new variable values must
  affect further queries as well.
[1 Nov 2006 8:29] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14651

ChangeSet@1.2299, 2006-11-01 12:30:01+04:00, bar@mysql.com +5 -0
  Bug#22877 replication character sets get out of
  sync using replicate-wild-ignore-table
  Problem: changes in character set variables
  before an action on an replication-ignored table
  makes slave to forget new variable values.
  Fix: initialize one_shot variables only when
  4.1 -> 5.x replication is running.
[3 Nov 2006 9:12] Alexander Barkov 
Merged into 5.1.13-rpl
[16 Nov 2006 16:47] Alexander Barkov 
Appeared in 5.0.32 common
Appeared in 5.1.14 common
[17 Nov 2006 18:41] Paul DuBois 
Noted in 5.0.32, 5.1.14 changelogs.

Changes to character set variables prior to an action on a
replication-ignored table were forgotten by slave servers.

I also marked ONE_SHOT as deprecated in the SET OPTION page.