MySQL Bugs: #15126: character_set_database is not replicated (LOAD DATA INFILE need it)

Bug #15126	character_set_database is not replicated (LOAD DATA INFILE need it)
Submitted:	22 Nov 2005 9:46	Modified:	10 Sep 2008 14:46
Reporter:	Guilhem Bichot
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.27-BK	OS:	Linux (Linux)
Assigned to:	Paul DuBois	Target Version:
Tags:	regression

Description:
If you change the session value of collation_database or character_set_database, this
info is not stored into the binary log (whereas character_set_client, collation_server,
collation_connection are in 5.0), so any statement dependent on this variable, like LOAD
DATA INFILE, will fail to replicate properly.

How to repeat:
set collation_database on master, load a file with strange characters using LOAD DATA
INFILE, compare results on master and slave.

Here is a testcase against 5.0, inspired from the one for BUG#12123 in mysqldump.test:

source include/master-slave.inc;

--echo #
--echo # BUG #12123
--echo #

create table t1 (a text character set utf8, b text character set latin1);
insert t1 values (0x4F736E616272C3BC636B, 0x4BF66C6E);
select hex(a),hex(b) from t1;
select * from t1;
--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ test
--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/t1.sql
--exec $MYSQL_IMPORT test $MYSQLTEST_VARDIR/tmp/t1.txt
select hex(a),hex(b) from t1;
select * from t1;

sync_slave_with_master;
select hex(a),hex(b) from t1;
select * from t1;

connection master;
drop table t1;
sync_slave_with_master;

Results I see:
/m/mysql-5.0/mysql-test $] ./mysql-test-run.pl --skip-ndb rpl_bug 
...
+ create table t1 (a text character set utf8, b text character set latin1);
+ insert t1 values (0x4F736E616272C3BC636B, 0x4BF66C6E);
+ select hex(a),hex(b) from t1;
+ hex(a)        hex(b)
+ 4F736E616272C3BC636B  4BF66C6E
+ select * from t1;
+ a     b
+ Osnabr�ck     K�ln
+ test.t1: Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
+ select hex(a),hex(b) from t1;
+ hex(a)        hex(b)
+ 4F736E616272C3BC636B  4BF66C6E
+ select * from t1;
+ a     b
+ Osnabr�ck     K�ln
+ select hex(a),hex(b) from t1;
+ hex(a)        hex(b)
+ 4F736E616272C383C2BC636B      4BF66C6E
+ select * from t1;
+ a     b
+ Osnabrück     K�ln

See: dump/import gave correct results on master, but import replicated badly on slave.
I have tried to apply this testcase to 4.1, but then slave failed to sync_with_master
with this in the error log:
061004 17:14:43 [ERROR] Slave: Error in Create_file event: could not open file
'../../var/tmp/SQL_LOAD
-2-1-1.info', Error_code: 2

Now you should have enough info to do the needed tests and set it to Verified, please.

Verified just as described with 5.0.27-BK on Linux.

Bug #22109 was marked as a duplicate of this one.

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/16915

ChangeSet@1.2377, 2006-12-13 21:39:50+01:00, jmiller@mysql.com +2 -0
  rpl_collation_db.test, rpl_collation_db.result:
    Test Case for bug#15126. I was not able to reproduce the issue listed in the bug
report, but I am wondering about the show database for Test #1. It shows to be 40100
DEFAULT CHARACTER SET latin1 eventhough both collation_database & character_set_database
is set to utf8

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/16917

ChangeSet@1.2378, 2006-12-13 21:45:18+01:00, jmiller@mysql.com +2 -0
  rpl_collation_db.test, rpl_collation_db.result:
    Test Case for Bug#15126. I was not able to reproduce the issue listed in the bug
report, but I am wondering about the show database for Test #1. It shows to be 40100
DEFAULT CHARACTER SET latin1 eventhough both collation_database & character_set_database
is set to utf8

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/19944

*Suggestion*
It might even be possible to not add any Q_* flag to Query_log_event:
non-LOAD queries use Query_log_event,
LOAD uses Execute_load_query_log_event which inherits from Query_log_event,
so you just need to modify Execute_load_query_log_event (always store charset_db into
it). That should not affect Query_log_event.

Guilhem, will not it break replication from new server to old server?
I.e. upgrade on master side will need upgrade on slave side.
Or did I misunderstood your idea?

Bar,
you may be right. Then I suggest adding a Q_COLLATION_DB, storing that into the
Query_log_event only if this is a LOAD DATA INFILE.
It will not make non-LOAD Query events any bigger.

Good to push

Pushed into 5.1.17-rpl

fixed in 5.0.38,5.1.17-beta

"fixed" above meant pushed

A note has been added to the 5.0.38 and 5.1.17 changelogs.

Dear doc team,

Can you please also document that the CHARACTER SET
clause was added into LOAD DATA syntax:

LOAD DATA INFILE 'loaddata6.dat' INTO TABLE t1 CHARACTER SET koi8r;

The new way, with explicit CHARACTER SET clause in LOAD DATA INFILE,
should be now the recommended way to load data from a file with an
alternative character set.

The old way using "SET @@character_set_database=xxx",
should be gradually depricated. Most likely, character_set_database
will be changed to "read only".

Thanks!

Description for LOAD DATA INFILE has been updated
to include the CHARACTER SET clause.

Changed status to Documenting, as documentation is confusing.
http://dev.mysql.com/doc/refman/5.0/en/load-data.html states that it is available as of
5.1.17, but makes no mention of 5.0.38. This has caused some confusion in a customer
issue.

Thank you for your bug report. This issue has been addressed in the documentation. The
updated documentation will appear on our website shortly, and will be included in the
next release of the relevant products.

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