| Bug #44041 | ALTER DATABASE change default COLLATION for following stmts of other DB on slave | ||
|---|---|---|---|
| Submitted: | 2 Apr 2009 9:16 | Modified: | 23 Oct 2009 4:36 |
| Reporter: | Zhenxing He | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | disabled | ||
[18 May 2009 11:04]
Susanne Ebrecht
Many thanks for writing a bug report.
I think you mixed something here.
CREATE DATABASE d1;
ALTER DATABASE d1
DEFAULT CHARACTER SET = 'utf8';
use test;
Test is another database then database d1. Maybe your database test is inconsistent in charset/collation.
Do the following test on master:
CREATE DATABASE d1;
ALTER DATABASE d1
DEFAULT CHARACTER SET = 'utf8';
SHOW VARIABLES LIKE 'character_set_database';
Result should be UTF8
SHOW VARIABLES LIKE 'collation_database';
Result should be utf8_general_ci
Do you use SBR or RBR?
[18 May 2009 11:11]
Susanne Ebrecht
Behaviour from RBR and SBR is same here. Database is correct changed to utf8 and utf8_general_ci.
[18 May 2009 12:51]
Zhenxing He
Hi, It's the 'Database Collation' of the function f1(), not that of the database 'd1' that's wrong. And the database test is consistent on master and slave, it is the default charset/collation: latin1_swedish_ci.
[12 Jun 2009 8:55]
Susanne Ebrecht
The default database collation depends on default database character set. If your default database character set is latin1 then the database collation is latin1_swedish_ci. If your default database character set is utf8 then the database collation is utf8_general_ci.
[24 Jun 2009 16:52]
Alexander Nozdrin
There is a test case in mysql-test/suite/rpl/t/rpl_killed_ddl.test disabled due to this bug. Please either enable the test case, or fix the test case.
[6 Sep 2009 12:54]
Philip Stoev
This bug has fallen through the cracks. Since it is a genuine replication bug and not a test case bug, unassigning myself and setting the correct lead. Lars, I would prefer that the underlying issue is fixed and then we re-enable the test case without having to fix the test case itself as an interim solution.
[23 Oct 2009 4:02]
Libing Song
Closed as duplicate of Bug#45516.
Description: When used ALTER DATABASE to change the CHARSET OR COLLATION of one db on master, the default COLLATION of the following statement on other db can be changed too. How to repeat: Run the following test: ---------------------------------------------------------------------- source include/master-slave.inc; CREATE DATABASE d1; ALTER DATABASE d1 DEFAULT CHARACTER SET = 'utf8'; use test; CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC RETURN 1; SHOW FUNCTION STATUS LIKE 'f1'; sync_slave_with_master; SHOW FUNCTION STATUS LIKE 'f1'; -------------------------------------------------------------------- The value for column 'Database Collation' of SHOW FUNCTION command on master will be: latin1_swedish_ci while on slave, it will be: utf8_general_ci Suggested fix: When executing an Query_log_event, it only set the new db name for current thd, it does not update the collation_database and db_access value for the new database. I'm thinking a fix like this: --- sql/log_event.cc 2009-03-24 13:58:52 +0000 +++ sql/log_event.cc 2009-04-02 09:10:55 +0000 @@ -2945,6 +2945,9 @@ VOID(pthread_mutex_unlock(&LOCK_thread_count)); thd->variables.pseudo_thread_id= thread_id; // for temp tables DBUG_PRINT("query",("%s",thd->query)); + + if (!(flags | LOG_EVENT_SUPPRESS_USE_F)) + mysql_change_db(thd, new_db, FALSE); if (ignored_error_code((expected_error= error_code)) || !check_expected_error(thd,rli,expected_error))