Bug #15126 character_set_database is not replicated (LOAD DATA INFILE need it)
Submitted: 22 Nov 2005 8:46 Modified: 10 Sep 2008 12:46
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.27-BK OS:Linux (Linux)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: regression

[22 Nov 2005 8:46] Guilhem Bichot
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.
[4 Oct 2006 14:15] Guilhem Bichot
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.
[19 Oct 2006 13:43] Valeriy Kravchuk
Verified just as described with 5.0.27-BK on Linux.
[23 Oct 2006 11:40] Valeriy Kravchuk
Bug #22109 was marked as a duplicate of this one.
[13 Dec 2006 20:44] 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/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
[13 Dec 2006 21:01] 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/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
[15 Feb 2007 13:45] 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/19944
[15 Feb 2007 14:47] Guilhem Bichot
*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.
[15 Feb 2007 17:47] Alexander Barkov
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?
[16 Feb 2007 10:03] Guilhem Bichot
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.
[20 Feb 2007 9:00] Rafal Somla
Good to push
[2 Mar 2007 9:15] Alexander Barkov
Pushed into 5.1.17-rpl
[8 Mar 2007 7:54] Andrei Elkin
fixed in 5.0.38,5.1.17-beta
[8 Mar 2007 8:07] Andrei Elkin
"fixed" above meant pushed
[9 Mar 2007 14:35] MC Brown
A note has been added to the 5.0.38 and 5.1.17 changelogs.
[16 Mar 2007 10:29] Alexander Barkov
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!
[16 Mar 2007 19:07] Paul DuBois
Description for LOAD DATA INFILE has been updated
to include the CHARACTER SET clause.
[9 Sep 2008 21:44] Gary Pendergast
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.
[10 Sep 2008 12:46] Jon Stephens
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.
[10 Sep 2008 12:47] Jon Stephens
http://lists.mysql.com/commits/53690