Bug #37157 --replicate-*-tables options don't effect non-table DB objects
Submitted: 3 Jun 2008 11:26 Modified: 13 Aug 2009 14:31
Reporter: Jens Reinemuth Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1+ OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: database, exists, PROCEDURE, replication, stored
Triage: Triaged: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

[3 Jun 2008 11:26] Jens Reinemuth
Description:
In our networ there are several MySQL-Servers which all replicate different tables from a Master.

For a new project i have a "monster" stored procedure which simply generates all needed views in one Database on the server.

If i call the procedure on the server, all slaves stop replicating with an error that the used database does not exist in their database

-- Server

DROP PROCEDURE IF EXISTS `myDB`.`sp_CreateViews`

DELIMITER ?

CREATE PROCEDURE
    `myDB`.`sp_CreateViews` (IN intYear INT,IN intFirmenID INT,OUT strResult TINYINT)
MODIFIES SQL DATA

BEGIN
...
END $

DELIMITER ;

CALL sp_CreateViews(2008,1,@retVal);

-- Client 

Stops replication with "Database does not exist: `myDB`..."

While on several Clients hve wildcard in the replicate_wild_do_table statements, others dont...

None of the clients explicitely replicate the used table, so is

How to repeat:
-- Server

DROP PROCEDURE IF EXISTS `myDB`.`sp_CreateViews`

DELIMITER ?

CREATE PROCEDURE
    `myDB`.`sp_CreateViews` (IN intYear INT,IN intFirmenID INT,OUT strResult TINYINT)
MODIFIES SQL DATA

BEGIN
...
END $

DELIMITER ;

CALL sp_CreateViews(2008,1,@retVal);

-- Client 

Stops replication with "Database does not exist: `myDB`..."

While on several Clients hve wildcard in the replicate_wild_do_table statements, others dont...

None of the clients explicitely replicate the used table.

Suggested fix:
Slaves should simply ignore the SP-statements on Databases they don't replicate nor have...
[3 Jun 2008 11:34] Jens Reinemuth
I forgot:

We got the following versions:

Master: 5.0.16-standard-log 

Slaves: 5.0.45 & 5.0.51a
[3 Jun 2008 19:56] Valeriy Kravchuk
Thank you for a problem report. Can you try to upgrade your master to a newer version, 5.0.45, and check if the problem will still be repeatable?
[4 Jun 2008 7:26] Jens Reinemuth
Hi Valeryi,

i will try the update in the evening hours, because the server ist very busy. But i didn't get the thing right. The problem should not be the master as it doesn't do something wrong. It simply logs the actions. The slaves should not try to execute the actions regarding non existing databases, should they?

Jens
[4 Jun 2008 11:56] Jens Reinemuth
Now i tried a way smaller test on the two development-servers locally:

--
Master is Fedora Core 9, MySQL 5.0.51a

I created 2 databases: "test_replicate" and "test_dontreplicate"

Each one has 1 table "test", id int and name varchar(50)

--
Slave is gentoo, MySQL 5.0.60-r1

replicate-wild-do-table=test_replicate.%

Now i created a small stored procedure on the Master:

DROP PROCEDURE IF EXISTS `test_dontreplicate`.`sp_Test`
DELIMITER ?
CREATE PROCEDURE
    `test_dontreplicate`.`sp_Test` (IN myName Varchar(50),OUT strResult TINYINT)
MODIFIES SQL DATA
    

BEGIN
    SET @mySQL = CONCAT('INSERT INTO `test_dontreplicate`.`test` (`name`) VALUES ("',myName,'");');
    PREPARE stmt FROM @mySQL;
    EXECUTE stmt;
END ?

DELIMITER ;

And after this, the slave stopps with the error:

mysql> show slave status\G
.
.
.
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: test_replicate.%
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1049
                 Last_Error: Error 'Unknown database 'test_dontreplicate'' on query. Default database: 'test_dontreplicate'. Query: 'CREATE DEFINER=`root`@`%` PROCEDURE
    `test_dontreplicate`.`sp_Test` (IN myName Varchar(50),OUT strResult TINYINT)
MODIFIES SQL DATA

BEGIN
    SET @mySQL = CONCAT('INSERT INTO `test_dontreplicate`.`test` (`name`) VALUES ("',myName,'");');
    PREPARE stmt FROM @mySQL;
    EXECUTE stmt;
END'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 396
            Relay_Log_Space: 611
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
.
.
.

So newer versions don't fix the problem...

I treid to set "replicate_ignore_db=%" before the "wild_do_..."-settings, but this doesnt work...
[7 Oct 2008 13:59] Susanne Ebrecht
Verified as described by using MySQL 5.0 bzr tree. Have to test with MySQL 5.1 too.

Here is my test:

CREATE DATABASE test_replication;

./bin/mysqladmin -h <slave host> -P<slave port> shutdown

my.cnf:
[mysqld]
...
replicate-wild-do-table=test_replication.%
[client]
...

./libexec/mysqld --defaults-file=<path of slave my.cnf>/my.cnf &

On Master:
use test_replication;
create table t(id serial);

look on slave:
use test_replication;
show tables;

table was replicated as expected

On Master:
Create database test_dontreplicate;

Look on slave:
show databases;

database wasn't replicated (expected behaviour)

On Master:
DELIMITER ?
BEGIN
    SET @mySQL = CONCAT('INSERT INTO `test_dontreplicate`.`test` (`name`) VALUES
("',myName,'");');
    PREPARE stmt FROM @mySQL;
    EXECUTE stmt;
END ?

DELIMITER ;

On Slave:
show slave status \G

Result:
...
Slave_SQL_Running: No
....
 Last_Errno: 1049
                 Last_Error: Error 'Unknown database 'test_dontreplicate'' on query. Default database: 'test_replication'. Query: 'CREATE DEFINER=`miracee`@`localhost` PROCEDURE `test_dontreplicate`.`sp_Test`(IN myName Varchar(50),OUT strResult TINYINT)
    MODIFIES SQL DATA
BEGIN
    SET @mySQL = CONCAT('INSERT INTO `test_dontreplicate`.`test` (`name`) VALUES
("',myName,'");');
    PREPARE stmt FROM @mySQL;
    EXECUTE stmt;
END'
...

It should not replicate this procedure at all. Also it should not stop the slave after trying to replicate this.
[7 Oct 2008 14:12] Susanne Ebrecht
Sorry I made a copy and paste error ... I took the CREATE PROCEDURE given from reporter.

Anyway, this also hits MySQL 5.1 bzr tree.
[11 Jun 2009 11:58] Alfranio Correia
Is it possible to use either --replicate-do-db or --replicate-ignore-db?
Remember to call "USE <db>" to set the appropriate database, before creating the procedure. 

See the documentation for further details.
[12 Jun 2009 1:35] Alfranio Correia
Please, check also the option --replicate-rewrite-db.
[17 Jun 2009 3:58] Donna Harmon
Work Arounds:
1) Set the below in the Slave's my.cnf to the database which the stored procedure is created in and the procedure will not be replicated:

replicate-ignore-db=dbname

2) Set the below in the Slave's my.cnf to the databases which you do want replicated and the stored procedure will be ignored: 

replicate-do-db=dbname
[17 Jun 2009 11:20] Alfranio Correia
I don't see the issue described in this bug report as a bug. Let's analyze the
current report in two steps. First, the creation of the procedure and then its
execution.

1 - Code (Creation):

  CREATE DATABASE test_replicate;
  USE test_replicate;
  CREATE TABLE test_replicate.test(id INT, name VARCHAR(50));

  SET sql_log_bin=0;
  CREATE DATABASE test_dontreplicate;
  CREATE TABLE test_dontreplicate.test(id INT, name VARCHAR(50));
  SET sql_log_bin=1;

  USE test;
  DROP PROCEDURE IF EXISTS test_dontreplicate.sp_test;

  DELIMITER |;
  CREATE PROCEDURE test_dontreplicate.sp_test (IN x INT)
  BEGIN
    INSERT INTO test_dontreplicate.test VALUES (x,'test');
  END|
  DELIMITER ;|

1 - Binlog (Creation):
  master-bin.000001 454 Query 1 666
  use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `test_dontreplicate`.`sp_test`(IN x INT)
  BEGIN
    INSERT INTO test_dontreplicate.test VALUES (x,'test');
  END

1 - Analysis (Creation):
The slave will try to create a procedure in the database "test_dontreplicate"
which does not exist and will fail. There are two alternatives to circumvent
the failure: either the command doest not get into the binary log by using
the sql_log_bin or the database is filtered by either --replicate-do-db or
--replicate-ignore-db. Note that --replicate-wild-do-table is ineffective as
there is no table to be filtered.

2 - Code (Execution):

  USE test;
  CALL test_dontreplicate.sp_test(10);

2 - Binlog (Execution):
  use `test_dontreplicate`; INSERT INTO test_dontreplicate.test VALUES ( NAME_CONST('x',10),'test')

2 - Analysis (Execution):
The slave will fail while trying to execute the "use" as there is no such
database. Note that the slave does not get a chance to check the
--replicate-wild-do-table as it fails before. And although the current database
in the code is "test", implicitly the execution of a procedure changes it.
Please, check section 19.2.1 of the manual.
[17 Jun 2009 22:20] Alfranio Correia
The step "2 - Analysis (Execution)" described above is wrong.
See the correct explanation below:

The slave will fail while trying to execute the insert on a database that does
not exist. To circumvent this issue, use --replicate-do-db, --replicate-ignore-db, --replicate-do-table, --replicate-ignore-table, --replicate-wild-do-table.

See section 16.1.3.3. in the manual for further details.

Thanks Donna to make this clear.
[18 Jun 2009 9:32] Jon Stephens
Per today's IRC discussion with Alfranio, I've reassigned this to myself for review; I'll see if there's a way to make this issue clearer in the docs.
[25 Jun 2009 14:18] 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.
[12 Aug 2009 21:03] Roel Van de Paar
Documentation pages flowcharts:
http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html
http://dev.mysql.com/doc/refman/5.1/en/replication-rules-table-options.html

Overview page:
http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html
[30 May 2012 11:38] Sveta Smirnova
Bug #65449 was marked as duplicate of this one