MySQL Bugs: #5460: "create database" not replicated

Bug #5460	"create database" not replicated
Submitted:	8 Sep 2004 6:29	Modified:	21 Sep 2004 14:52
Reporter:	Michael McCallister	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (Linux 2.4.20-30.9smp)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When replicating a single server, "create database" statements are ignored and therefore subsequent "create table" statements fail.  So everytime a new database is created on the master - the slave busts until the database is manually created on the slave and the slave is restarted.

For example, everytime I create a new database on the master, I get this kind of message:

ERROR: 1  Can't create/write to file './firstsolar/FSContact.frm' (Errcode: 2)
040907 23:16:25  Slave: Error 'Can't create/write to file './firstsolar/FSContact.frm' (Errcode: 2)' on query. Default database: 'firstsolar'. Query: 'CREATE TABLE `FSContact` (...............

My replication specific settings:

server-id       = 8903421
master-host=somehost.com
master-user=replication
master-password=xxxxxxxx
master-port=3309
replicate-ignore-db=mysql

How to repeat:
Create a DB on master and it busts the slave every time.

Suggested fix:
Manually create DB on slave and restart slave - somewhat annoying after about the 10th time.

Hello Michael,

This indeed looks annoying. So far I haven't heard of it happening and cannot repeat it.
First thing: you are using --replicate-ignore-db=mysql. So I guess you know that, as
http://dev.mysql.com/doc/mysql/en/Replication_Options.html
says, any statement which is executed with 'mysql' being the current database (like 'USE mysql; CREATE DATABASE firstsolar;') will NOT be written to the binary log.
For the CREATE DATABASE to be written, 'mysql' should not be the default database of your connection. We usually recommend --replicate-wild-ignore-table over --replicate-ignore-db, in the general case.
If you're not in this case, I'd like a repeatable short testcase ("I open a connection and type this and then that fails").
You can inspect your master's binary logs (using 'mysqlbinlog') to verify that the CREATE DATABASE is in them, if yes, you can inspect your slave's relay logs (mysqlbinlog) to verify that the CREATE DATABASE is in them.

Guilhem

Hi Guilhem,

"For the CREATE DATABASE to be written, 'mysql' should not be the
default database of your connection." Does not apply to me.

I set this replication up when MySQL replication first became stable so it has been running for a while. I seem to remember --replicate-ignore-db=mysql was added because it used to replicate the "mysql" database as well (including users) and I wanted a different root password for the slave DB. And yes - I have been dealing with this bug the whole time assuming that eventually it would get fixed and for whatever reason when I had to manually add the last database to the slave tonight - I decided it was time to report the bug.

It is strange that nobody has heard of this happening before - this would leave me to believe there is something odd about my setup or most people don't bother to replicate all databases on the master server and explicity state which DBs are to be replicated.

Since it is more likely there is something odd about my setup, I guess we will start there. Both servers are running 4.0.20. When I set it up a while back, I followed the directions that were then posted for replication to the letter. The slave servers sole purpose is to run as a live backup in case the master server blows up or something else really bad. The master server currently runs 94 databases with an average of about 12 tables per database. Three odd issues that I can thnk of include:

1) Both servers have "skip-innodb" set in my.cnf.

2) I am using stunnel to have the slave connect to the master so that all communications are encrypted since replication is running over the Internet (not on local LAN).

3) When I create a new database on the master server - I almost always do it through Perl DBI as opposed to the mysql command line client.

I am open to any new suggestions based on this information. My next steps will include:

1) removing --replicate-ignore-db=mysql

2) Use mysql command line client vs. Perl DBI

3) Monitor binary logs - which will more than likely tell me that create database is not logged in my current scenario

I will let you know how this goes.

Mike

Hi Guilhem,

Removing --replicate-ignore-db=mysql appears to have resolved this issue.  I even added --replicate-ignore-db=mysql back temporarily to confirm this was indeed the issue and sure enough - adding it back to the slave config brought back the problem.  So for some reason, --replicate-ignore-db=mysql causes all "create database" statements to not be replicated.

I appreciate your help regarding this matter.  You spotted to problematic config issue right away.  Not sure if this constitutes a bug or is simply and end-user configuration issue - I leave that call to you!

Thank you very much.

Mike

You are welcome.
If it's a bug or not; well --*-ignore-db are touchy options, we usually recommend the *ignore-table options instead. ignore-db works faster but you always have to be careful, and when one forgets he runs into issues.
I am glad you fixed the problem.

I think there is still a problem here.  This statement: "any statement which is executed with 'mysql' being the current database (like 'USE mysql; CREATE DATABASE firstsolar;') will NOT be written to the binary log." does not seem to be true:

"lave: Error 'Unknown column 'Show_db_priv' in 'field list'' on query. Default database: 'm                                ysql'. Query: 'insert into user (Host,User,password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_p                                riv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_p                                riv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_                                priv) values"

As you can see, it is now trying to once again replicate the "mysql" table.  So removing "replicate-ignore-db" fixed the problem of not replicating "create database" statements, but now is allowing "mysql" database updates to be replicated.

This would appear to be a bug to me.  Or maybe just a bug in the documentation.  

This appears to fix the problem for now:

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

I fail to understand: you wrote:
>As you can see, it is now trying to once again replicate the "mysql"
>table.  So removing "replicate-ignore-db" fixed the problem of not
>replicating "create database" statements, but now is allowing "mysql"
>database updates to be replicated.
Yes, removing replicate-ignore-db=mysql means you do not want slave to ignore
the 'mysql' database anymore, i.e. you want slave to replicate the 'mysql' database.
That's why it's normal that removing replicate-ignore-db=mysql is allowing "mysql"
database updates to be replicated.