Bug #11143 CREATE DATABASE in Cluster Replication is not replicated consistently
Submitted: 7 Jun 2005 16:19 Modified: 14 Jun 2005 13:02
Reporter: Jonathan Miller Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S4 (Feature request)
Version:5.1.0-wl2325-wl1354-new OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[7 Jun 2005 16:19] Jonathan Miller
Description:
I had setup a master cluster with a slave cluster pulling. I started replication without issue.  I created a BANK database and all the tables that went with it. I then created a BANK2 database and all the tables that went with it.

On the slave, the slave has stopped due to the BANK database not existing. I created both the BANK and BANK2 databases and restarted the slave. The slave created all the tables for BANK. Then the slave errored due to BANK2 already existing. I droped BANK2 and restarted the slave, and all processed correctly.

It does not make sense to have to create one database on the slave and not the other. We should either replicate all the Create databases or non.

From slave error log:

050607 17:27:19 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/GL
050607 17:27:19 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/ACCOUNT
050607 17:27:32 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/TRANSACTION
050607 17:27:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/SYSTEM_VALUES
050607 17:27:33 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/ACCOUNT_TYPES
050607 17:27:56 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/TRANSACTION
050607 17:27:56 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/SYSTEM_VALUES
050607 17:27:56 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/ACCOUNT_TYPES
050607 17:28:11 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/GL
050607 17:28:11 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/ACCOUNT
050607 17:29:04 [ERROR] Slave: Error 'Can't create/write to file './BANK/GL.frm' (Errcode: 2)' on query. Default database: 'BANK'. Query: 'CREATE TABLE GL ( TIME BIGINT UNSIGNED NOT NULL,
                  ACCOUNT_TYPE INT UNSIGNED NOT NULL,
 ACCOUNT_TYPE INT UNSIGNED NOT NULL,
                  BALANCE INT UNSIGNED NOT NULL,
                  DEPOSIT_COUNT INT UNSIGNED NOT NULL,
                  DEPOSIT_SUM INT UNSIGNED NOT NULL,
                  WITHDRAWAL_COUNT INT UNSIGNED NOT NULL,
                  WITHDRAWAL_SUM INT UNSIGNED NOT NULL,
                  PURGED INT UNSIGNED NOT NULL,
                  PRIMARY KEY USING HASH (TIME,ACCOUNT_TYPE))
   ENGINE = NDB', Error_code: 1
050607 17:29:04 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master1.000001' position 102
050607 17:31:17 [Note] Slave: received end packet from server, apparent master shutdown:
050607 17:31:17 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'master1.000001' position 4328
050607 17:31:17 [ERROR] Slave I/O thread: error reconnecting to master 'rep@ndb10:3307': Error: 'Lost connection to MySQL server during query'  errno: 2013  retry-time: 1  retries: 86400
050607 17:31:21 [Note] Slave: connected to master 'rep@ndb10:3307',replication resumed in log 'master1.000001' at position 4328
050607 17:53:11 [Note] Slave SQL thread initialized, starting replication in log 'master1.000001' at position 102, relay log './ndb11-relay-bin.000003' position: 241
050607 17:53:11 [ERROR] Slave: Error 'Can't create/write to file './BANK/GL.frm' (Errcode: 2)' on query. Default database: 'BANK'. Query: 'CREATE TABLE GL ( TIME BIGINT UNSIGNED NOT NULL,
                  ACCOUNT_TYPE INT UNSIGNED NOT NULL,
                  BALANCE INT UNSIGNED NOT NULL,
                  DEPOSIT_COUNT INT UNSIGNED NOT NULL,
                  DEPOSIT_SUM INT UNSIGNED NOT NULL,
                  WITHDRAWAL_COUNT INT UNSIGNED NOT NULL,
                  WITHDRAWAL_SUM INT UNSIGNED NOT NULL,
                  PURGED INT UNSIGNED NOT NULL,
                  PRIMARY KEY USING HASH (TIME,ACCOUNT_TYPE))
   ENGINE = NDB', Error_code: 1
050607 17:53:11 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master1.000001' position 102
050607 17:53:37 [Note] Slave SQL thread initialized, starting replication in log 'master1.000001' at position 102, relay log './ndb11-relay-bin.000003' position: 241
050607 17:53:37 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/GL
050607 17:53:37 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/ACCOUNT
050607 17:53:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/TRANSACTION
050607 17:53:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/SYSTEM_VALUES
050607 17:53:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK/ACCOUNT_TYPES
050607 17:53:38 [ERROR] Slave: Error 'Can't create database 'BANK2'; database exists' on query. Default database: 'BANK2'. Query: 'CREATE DATABASE BANK2', Error_code: 1007
050607 17:53:38 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master1.000001' position 2170
050607 17:54:08 [Note] Slave SQL thread initialized, starting replication in log 'master1.000001' at position 2170, relay log './ndb11-relay-bin.000003' position: 2309
050607 17:54:08 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/TRANSACTION
050607 17:54:08 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/SYSTEM_VALUES
050607 17:54:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/ACCOUNT_TYPES
050607 17:54:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/GL
050607 17:54:09 [Note] NDB Binlog: CREATE TABLE Event: REPL$BANK2/ACCOUNT

How to repeat:
1) setup a master cluster and a slave cluster.
2) Start replication
3) on the master create a database.
4) add tables.
5) create second database
6) add tables
*) Slave should be stopped due to error at this point.
7) on slave create the two database from above.
8) "START SLAVE"
*) the slave should have processed the tables for the first database and errored out on the second becuase it already exists.
9) drop second database on the slave.
10) "START SLAVE:
*) Slave should now have processed the create table statements for the second database.

Suggested fix:
1) Be consistent in what is replicated.
2) Maybe ingnore the create database failure if that database already exists.
[13 Jun 2005 18:25] Tomas Ulin
CREATE DATABASE is replicated _only_ from the master on which the statement is performed.  The application programmer has to take care to manage CREATE DATABASE so that it get's replicated correctly under this constraint.

Also, CREATE DATABASE does currently give an error on the slave, it is under discussion how to treat these kínds of statemets (CREATE/DROP/ALTER  DATABASE/TABLE) during row-based replication.

Current workarounds exist to use the "IF EXISTS" construction if the user want's to avoid to have the slave stopping in these cases.
[14 Jun 2005 13:02] Jonathan Miller
Just a note: Both created were done in the same master mysqld process.
[31 Mar 2008 14:55] amit tasgaonkar
good
[13 Mar 2014 13:33] Omer Barnir
This bug is not scheduled to be fixed at this time.