Bug #72682 Replication MBR halts - stored procedure from unreplicated schema
Submitted: 19 May 2014 13:42 Modified: 23 May 2014 16:00
Reporter: Tinel Barb Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.37 OS:Linux (5.5.37-0ubuntu0.12.04.1-log)
Assigned to: CPU Architecture:Any

[19 May 2014 13:42] Tinel Barb
Description:
Master-Slave MBR, MySQL 5.5.37 Community on both servers.

SQL Thread stops with error #1049 when a stored procedure was restored on Master on an unreplicated schema.
Individual MySQL options are:

MASTER my.cnf:
--------------
server-id=10
innodb_support_xa=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1
log-bin-trust-function-creators=1
binlog-format=MIXED

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
========================================

SLAVE my.cnf:
-------------
server-id=2
innodb_support_xa=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1
log-bin-trust-function-creators=1
binlog-format=MIXED

replicate-wild-do-table=sibanc.%
replicate-wild-do-table=sibanc_luna.%
replicate-wild-do-table=raportari.%
========================================

ERROR:
------
140519  9:03:37 [ERROR] Slave SQL: Error 'Unknown database 'situatii'' on query. Default database: 'situatii'.
Query: 'CREATE DEFINER=`root`@`192.168.%.%` FUNCTION `AlphaToInt`(str char(6)) RETURNS int(11)
    NO SQL
    DETERMINISTIC
BEGIN
.......more cmd........
return result;
END', Error_code: 1049
140519  9:03:37 [Warning] Slave: Unknown database 'situatii' Error_code: 1049
140519  9:03:37 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'mysql-bin.000085' position 3359955
============================================================================

The Slave shouldn't read from Master's binlog the commands regarding 'situatii' schema because it is not specified to be replicated, according to replication options.
Knowing the fact that the stored procedures are replicated using SBR, the exposed behavior is not following the documents "Evaluation of Database-Level Replication and Binary Logging Options", "Evaluation of Table-Level Replication Options" and "Replication Rule Application and Examples".
As from "Evaluation of Database-Level Replication...", if there are no Database-Level options the engine should treat the Table-Level Replication Options.
I haven't found the reason why the Slave downloaded from Master the commands for this unspecified schema.

FIX for the moment:
Created the nonexistent 'situatii' schema on Slave and issued "SLAVE START" command. After that, the slave catches up the Master well.
On Master there are also tens of other databases, from which only 3 needs replication. Also, new databases are created all the time, some of them are dropped after awhile.
It is wrong to create every schema existent on Master just because the IO thread can't be well controlled.

Links:
http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html
http://dev.mysql.com/doc/refman/5.5/en/replication-rules-table-options.html
http://dev.mysql.com/doc/refman/5.5/en/replication-rules-examples.html

How to repeat:

MASTER with AAA, BBB, CCC, DDD schemes.
SLAVE with only AAA, BBB and CCC schemes, no DDD schema.
Set up replication for AAA, BBB, CCC databases:
-> Master-Slave replication 
-> Mixed format
-> Table-level replication options for selecting the replicated schemes:
---- Master:
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
---- Slave:
replicate-wild-do-table=sibanc.%
replicate-wild-do-table=sibanc_luna.%
replicate-wild-do-table=raportari.%
-> Select `DDD` schema on Master and issue a CREATE FUNCTION statement on it.

Suggested fix:
Table-level replication option could precede the Database-level replication options.

This could allow a better control of replicated options, because the Database-Level replication logic is dropping important/useful cross-database statements which can be filtered only with Table-Level replication options _ONLY_ if there are no Database-Level options.

For example, giving the `reports` schema, needed to be replicated, and not replicated `work` schema:
use work; insert into `reports`.`report1` select * from worktable;
will NOT be replicated with Database-level replication options, even there are any table-level options.
[21 May 2014 10:06] Tinel Barb
Hello,

The "How to repeat" section should be as:

MASTER with AAA, BBB, CCC, DDD schemes.
SLAVE with only AAA, BBB and CCC schemes, no DDD schema.
Set up replication for AAA, BBB, CCC databases:
-> Master-Slave replication 
-> Mixed format
-> Table-level replication options for selecting the replicated schemes:
---- Master:
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
---- Slave:
replicate-wild-do-table=AAA.%
replicate-wild-do-table=BBB.%
replicate-wild-do-table=CCC.%
-> Select `DDD` schema on Master and issue a CREATE FUNCTION or CREATE PROCEDURE statement on it.

My apologizes for the mistake.

As a note, the problem resides in the way IO_Thread gather data from Master.

Thank you!
[23 May 2014 16:00] Tinel Barb
Hello again!

I was doing an extensive research and testing to find the most appropriate set-up for replication of cross-database statements and creation of stored procedures and functions, including ways to exclude from replication the creation of stored procedures on non-replicated schemes.

In the end, I've find that a mix of database-options and table-options are working as requested, _regardless_ the logical schema from the links provided.

Simply, on SLAVE the definitions are:
----------------------------------------
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema

replicate-do-db = AAA
replicate-do-db = BBB
replicate-do-db = CCC

replicate-wild-do-table = AAA.%
replicate-wild-do-table = BBB.%
replicate-wild-do-table = CCC.%
==========================================

With these set-up, commands like:

use DDD; insert into AAA.table1 select 0,count(id) from BBB.table2;

will be replicated, and creation of stored procedures on non-replicated schema will not be downloaded by IO_Thread and sent to execution.

Even if this set-up is not following the logical from http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html and http://dev.mysql.com/doc/refman/5.5/en/replication-rules-table-options.html , I consider this behavior as a _feature_.
I will close the ticked before someone will mess it up :)

Best regards!

Tinel Barb
Romania