MySQL Bugs: #104480: Filtered replication fails on CREATE TABLE statements performed with backticks

Bug #104480	Filtered replication fails on CREATE TABLE statements performed with backticks
Submitted:	30 Jul 2021 20:34	Modified:	10 Aug 2021 0:33
Reporter:	Christian CHATEAUVIEUX	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	8.0.25	OS:	Debian
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
I am using mySQL master/slave filtered replication with rewrite-db

replicate-rewrite-db=channel_one:src_db->dest_db

Binary logging is using ROW format

Whenever a table is created under my master DB, replication fails on the slave DB, with a message saying that the database on which the table was created ('src-db') cannot be found in the slave DB (and indeed, the table should be created against dest_db given the rewrite-db rule).

The problem only occurs when the CREATE TABLE statement was performed using backticks. 

How to repeat:
To reproduce, setup a master/slave replication with rewrite-db
Once the replication is running, create on the source DB a table without using backticks

CREATE TABLE mytable.src_db ...

This newly created table replicates sucesfully whereas creating another table with backticks, say

CREATE TABLE `myothertable`.`src_db` ...

fails with the aformentionned error.

Hi,

Can you share exactly what you have in config and exactly what fails because it might not be a bug but missunderstanding on how filtering works.

If you have "do-db" for e.g. mysql only looks at "default" database so for e.g. if you have do-db=xyz and do

use xyz
create table aaa -- this will be replicated
create table xxx.bbb -- this will be replicated
use xxx
create table xyz.ccc -- this will NOT be replicated

So please give use the exact my.cnf from both master and slave and exact create table statements that fail.

Thanks

Hello,

Thanks for your answer.
I don't specify any 'do-db' in my mySQL server config.
Here's my config:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
replicate-ignore-db=mysql
binlog-format=ROW
log_bin=mysql-bin
expire_logs_days=1
slave-skip-errors=1396,3836
replicate-rewrite-db=channel_cascade_staging:kpi_sutti->kpi_sutti_cascade_staging

Hi,

I'm not reproducing this with your setup. Please share CREATE's too

thanks

Hi,

If you have a->b in your config and you do

use a;
create table x (id int);
create table `y` (id int);

both will be replicated into b on the slave

if you do

create table a.xx (id int);
create table `a`.`yy` (id int);

both will be replicated into a on the slave

This is how filtering works, it is only the "default" db that's converted

but please make a small test case with connect, use, create.. and let us know what behaves the way you do not expect so we can confirm it is a bug or not. So far I don't see the system behave any differently from what it is expected

thanks

Hello,

thanks for your analysis, but I don't understand why perfectly valid SQL statements make the replication fail. In my opinion, it is not because the SQL client used 'use db', or prefixed the table name with the db name, or used backticks or not, that should have an incidence on the replication process. Regardless of the approach used, the changed occured in the DB that is supposed to be replicated to the slave.

Whet I see on my setup is that 

create table a.xx (id int);
create table `a`.`yy` (id int);

have different results. the statement without backticks does NOT fail and the data is routed in the rewrite db, which to me is the correct behaviour. The one with backticks fails and stops the replication.
In my view, the replication should work regardless of wether the SQL client did 
use backticks or not. 

Thanks

Hi,

> Whet I see on my setup is that 
>
> create table a.xx (id int);
> create table `a`.`yy` (id int);
> 
> have different results.

That would be a bug but I cannot reproduce this!

mysql 8.0.26 with databases `a`, `b` and `c`
config: replicate-rewrite-db="a->b"

connection to master:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

((none)) > create table a.t1(id int);
Query OK, 0 rows affected (0.02 sec)

((none)) > create table `a`.`t2`(id int);
Query OK, 0 rows affected (0.01 sec)

((none)) > use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(a) > create table a.t3(id int);
Query OK, 0 rows affected (0.01 sec)

(a) > create table `a`.`t4`(id int);
Query OK, 0 rows affected (0.01 sec)

(a) > create table t5(id int);
Query OK, 0 rows affected (0.01 sec)

(a) > create table `t6`(id int);
Query OK, 0 rows affected (0.01 sec)

(a) > use b;
Database changed
(b) > create table a.t7(id int);
Query OK, 0 rows affected (0.01 sec)

(b) > create table `a`.`t8`(id int);
Query OK, 0 rows affected (0.01 sec)

(b) >

now, connection to slave:

((none)) > use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(a) > show tables;
+-------------+
| Tables_in_a |
+-------------+
| t1          |
| t2          |
| t3          |
| t4          |
| t7          |
| t8          |
+-------------+
6 rows in set (0.00 sec)

(a) > use b;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(b) > show tables;
+-------------+
| Tables_in_b |
+-------------+
| t5          |
| t6          |
+-------------+
2 rows in set (0.01 sec)

(b) >

so as you can say only `t5` and `t6` are in database `b`, both created using default database so without explicit database. all other tables with explicit database set, no matter if that database was backquoted or not are in the database a.

This is expected behavior.

Can you give me exact steps to reproduce 

create a.tx(id int); 

ends up in database b, no matter if you are backquoting or not the a

thanks

Well, I can't reproduce this either. And indeed, I misunderstood that filtered replication only works in 'default' DB. Not sure if this will be usable in my context, given we use ORM tools... I need to ensure that they do use the 'USE' statement else this will fail. mySQLWorkbench, for instance, does not use the 'USE' statement when it composes statements out of the graphical wizards... so all of this make it error-prone.

Is there no way to ensure that statemetns prefixed by dbname get into the scope of the filtered replication with rewrite ?

If not, feel free to close the ticket.

Thanks

Hi,

Yes, this replication filter behavior is cause of many grievances amongst users for decades. But it is what it is. There are numerous blog posts both from us in MySQL Team and from other users explaining it, expanding the documentation.

Closing this report as not a bug.

> Is there no way to ensure that statemetns prefixed by 
> dbname get into the scope of the filtered replication with rewrite ?
>
> If not, feel free to close the ticket.

Don't forget this is not a support system, this is just a bug reporting system. We do have support too and it offers awesome bang for the buck so I really suggest you try it out.

As for the question, while back you would do this by rewriting query in mysql proxy (deprecated product), you can try similar product ProxySQL, it might be able to do this (can't say if it does, haven't follow up on it for a while but it might):
https://proxysql.com/blog/query-rewrite-with-proxysql-use-case-scenario/

Proper way would be to use MySQL Rewrite Query plugin:
https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html

but so far it support only SELECT, INSERT, REPLACE, UPDATE, and DELETE, so you will not be able to rewrite the CREATE statements :( 

kind regards
Bogdan

> given we use ORM tools... 
> I need to ensure that they do use the 'USE' statement else this will fail

I can't tell you that you should not use ORM tools, but I have to tell you that letting ORM tools create tables for you is bad idea in 99.9% cases.