[MDEV-11246] replicate-rewrite-db doesn't work on functions with STATEMENT binlog Created: 2016-11-07  Updated: 2022-02-22  Resolved: 2022-02-22

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.1.18, 5.5, 10.0, 10.1, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Anton Avramov Assignee: Sachin Setiya (Inactive)
Resolution: Not a Bug Votes: 0
Labels: upstream
Environment:

ubuntu trusty


 Description   

Create a master replication server with variable binlog_format=STATEMENT
On the master 

create database mariadb_repl_bug;

Create a slave replication server with settings: 

[mysqld]
 
repl_bug.replicate-rewrite-db=mariadb_repl_bug->m_bug_repl
 
repl_bug.replicate-do-db  = m_bug_repl

On the slave 

create database m_bug_repl;

Setup replication on the slave named 'repl_bug'

On the master execute: 

create table test (id integer(11) not null primary key auto_increment, val varchar(25));
 
delimiter ;;
 
create function show_but() returns integer(11)
 
begin
 
  insert into test (val) value('hello');
 
  return last_insert_id();
 
end;;
 
delimiter ;

At this point there should be one table and one procedure in db mariadb_repl_bug on the master and m_bug_repl.

Now on the master execute: 

use  mariadb_repl_bug;
 
select show_but();

The replication on the slave will then stop with error: 

Error 'FUNCTION mariadb_repl_bug.show_but does not exist' on query. Default database: 'm_bug_repl'. Query: 'SELECT `mariadb_repl_bug`.`show_but`()'

If you create the database mariadb_repl_bug and the function than the error will change to duplicate key.

In both cases this is incorrect behaviour. The slave should rewrite the db on calling the function also.

 Comments   
Comment by Elena Stepanova [ 2016-11-16 ]

Reproducible on all of 5.5-10.2, and also on MySQL (tried 5.7, but probably all of them are affected).

The problem here is that the function invocation is not written into the binary log as is, instead the server generates its own variation:

master-bin.000001	1027	Query	1	1140	use `mariadb_repl_bug`; SELECT `mariadb_repl_bug`.`show_but`()

That is, it writes the function with a fully-qualified name. And then of course replicate-rewrite-db does not work, because it considers the statement to be a cross-database query (a degenerate case of cross-database, but anyway – the name is fully qualified, so no substitution happens).

It's not quite obvious why it should be fully qualified, probably it just happened to be this way.

lukav,

Did you, by any chance, also file a bug at bugs.mysql.com, and if not, are you willing to do it?

Comment by Daniel Black [ 2018-01-01 ]

fyi - can't find a bugs.mysql.com bug.

Comment by Sachin Setiya (Inactive) [ 2018-09-02 ]

db name is added because of commit 3a05847abb for bug 19725

sql/sp_head.cc:
 
  When binlogging a "SELECT myfunc()" (when a stored function is executed
 
  inside a statement which does not go to the binlog (like a SET,
 
  SELECT, DO), we need to write "SELECT db_of_myfunc().myfunc()",
 
  because the function may be in a database which is not the default
 
  database.

Comment by Sachin Setiya (Inactive) [ 2018-09-02 ]

So the only option is to threat this function as non cross database function. which is string manipulation , So idk what else can be done

Comment by Andrei Elkin [ 2022-02-22 ]

Mariadb KB warns on the statement format limits

When an event is logged in its statement-based format, many replication filters that affect a database will test the filter against the default database (i.e. the one selected by the USE statement). This applies to the following replication filters:

binlog_do_db
binlog_ignore_db
replicate_rewrite_db
replicate_do_db
replicate_ignore_db

Generated at Thu Feb 08 07:48:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.