MySQL Bugs: #100691: Incorrect replication of excluded table via replicate-wild-ignore-table

Bug #100691	Incorrect replication of excluded table via replicate-wild-ignore-table
Submitted:	30 Aug 2020 20:16	Modified:	25 Mar 2022 16:00
Reporter:	Michal Vrabel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	CentOS (8)
Assigned to:		CPU Architecture:	x86

Description:
MySQL do replication of SELECT queries executed from someFunction() which is used in SELECT query excluded from replication via replicate-wild-ignore-table.

I have hot analyzed if replicate-wild-ignore-table is connected to essence of the problem, I put it because it can affect it.

This is not critical if you not calculcate with load distribution for some specific SELECT queries (long time running queries which block up replication to next master by unnecessary SELECT queries).

How to repeat:
Configuration:
my.cnf: replicate-wild-ignore-table  %.tmpTable
3 master-master replicated servers (S1->S2, S2->S3, S3->S1)

=> CORRECT BEHAVIOR <=
Main query:  SELECT id, someFunction() AS result FROM db.tmpTable;
Main query executed on S2 is not replicated to S3 and S1 

=> PROBLEM <=

someFunction() run some SELECT queries during execution Main query. 
These SELECT queries executed by someFunction() ARE replicated to S3 and as pure SELECT queries are not replicated to S1

Suggested fix:
SELECT queries executed from someFunction() shouldn't be replicated from S2 to S3 in the same way as the queries are not replicated from S3 to S1.

Hi Michal,

I'm not reproducing this according to your description.

Can you please create a repeatable test case with real set of data and queries?

Did you check https://dev.mysql.com/doc/refman/8.0/en/replication-rules-table-options.html

Do you have "replicate-wild-ignore-table  %.tmpTable" on all three servers?

thanks
Bogdan

I don't think it's necessarily related to replicate-wild-ignore-table  %.tmpTable.

Because the first main query is not replicated on any server.
But for creating temporary table excluded from replication via %.tmpTable% use some function for get JSON as one column.

I am not sure to build reproducedable environment, because it is quite complex chain of functions.

But I have found one detail.

Main query call someFunction() and someFunction() call otherFunction() and replicated selects are from otherFunction(), not from someFunction(). I detect this only if select run long time... I don't know how to check it in other way.

So the problem can be connected to run from function called from function.
Both functions are:
READS SQL DATA
DETERMINISTIC

BRAND NEW INFORMATION:

I have found mysqlbinlog is working for replication files. I have performed an analysis and I have some clearly news.

Main query is basic select which call someFunction().
someFunction() call otherFunction();

otherFunction() do  CREATE TEMPORARY TABLE tmpTable_mytable;

It is only query (so not SELECT query) which is being replicated from S2->S3, but no from S3->S1 (it is correct, but I would like to ignore this query also for S2->S3).
If I run main query on S1, replication is S1->S2, but not from S2->S3 (only one "hop" in both case).

So the problem is propably connected to exclude configuration (in the first post there is bug, correct is: replicate-wild-ignore-table  %.tmpTable%).

All three servers have this the same configuration:

replicate-ignore-db             = db_tmp
replicate-wild-ignore-table     = %.tmpTable%
replicate-wild-ignore-table     = db_tmp.%

show slave status return on all 3 servers:
Replicate_Wild_Ignore_Table    %.tmpTable%,db_tmp.%

-----------------------------------

When CREATE TEMPORARY TABLE in otherFunction()  is:
CREATE TEMPORARY TABLE tmpTable_mytable          ... is replicated S2->S3
CREATE TEMPORARY TABLE db_tmp.tmpTable_mytable   ... is also replicated S2->S3

I hope you will be able to reproduce this in this way.

Hi,

No, I'm not reproducing this.

Okey, I have tried do smallest example and you are right, it is not reproducible. It have to be connected to something in the function. I try to find the smallest reproducible code.

Hi,

> I try to find the smallest reproducible code

Thanks! As you see, just doing what you explained does not reproduce the problem. You can try to reduce your env. where it happens till you get to the point that it is reproducible and without any proprietary informations so that you can share the test case.

bes regards
Bogdan

Good thing has been done:)

Very simple to reproduce now. There are at least two importat conditions to be this issue reproducible.

MAIN QUERY:

SELECT someFunction() AS json_data
-- this having condition is important, without them replication works fine and do not replicate create temporary table in the someFunction()
HAVING json_data IS NOT NULL;

*** In the otherFunction() are important two things **
1. create temporary table must be filled from select using some function - otherFunction() in this case. Without calling function, replication is working fine.
2. RETURN_VALUE must be filled from SELECT query, using temporary table or other table and shouldn't be RETURN_VALUE NULL (if RETURN_VALUE = NULL, create temporary table is not replicated).

I hope this case helps mysql to be better and better... and fixing this issue help to load balancing of complex select queries.

/*  CODE TO CREATE BOTH FUNCTIONS */

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DELIMITER ;;

DROP FUNCTION IF EXISTS `otherFunction`;;
CREATE FUNCTION `otherFunction`() RETURNS json
    READS SQL DATA
    DETERMINISTIC
BEGIN
 DECLARE RETURN_VALUE JSON;

 SELECT '[0]' INTO RETURN_VALUE;

 RETURN RETURN_VALUE;
END;;

DROP FUNCTION IF EXISTS `someFunction`;;
CREATE FUNCTION `someFunction`() RETURNS json
    READS SQL DATA
    DETERMINISTIC
BEGIN
 DECLARE RETURN_VALUE JSON;

 DROP TEMPORARY TABLE IF EXISTS tmpTable_calcClaim;

 CREATE TEMPORARY TABLE tmpTable_calcClaim         
    -- MUST BE EXECUTED FUNCTION, without calling function it is working fine
    SELECT otherFunction() AS json_data;

  -- THIS FILLING RETURN_VALUE => working fine (not replicated previous temporary table), must be select from some table
  -- SELECT '[0]' INTO RETURN_VALUE;
  

  -- THIS FILLING RETURN_VALUE => previous create temporary table is being replicated. 
  SELECT '[0]' INTO RETURN_VALUE
  FROM tmpTable_calcClaim
  LIMIT 1;
  
 RETURN RETURN_VALUE;
END;;

DELIMITER ;

fix:
*** In the otherFunction() are important two things **  
should be
*** In the someFunction() are important two things **

Hi,

Thanks for the case.
Can you also share my.cnf for these servers?
Does this work between 2 servers (master->slave) or 3 servers are required?
If three, does it need to be circular (s1->s2->s3->s1) or regular (master->slave->slaveslave) ?

What do you expect and what's happening (you expect it to replicate but is not, or you expect it to not replicate and it is?)?

Thanks
Bogdan

Suggestion: 
If I want ignore replicate %.tmpTable% and tmpTable_calcClaim is mostly not replicated, this is correct behavior.

I have found one combination which cause replication of ignored table, so correct behavior is definitly: do not replicate
CREATE TEMPORARY TABLE tmpTable_calcClaim in my case.

I think that whole my.cnf is not necessary for reproduce...

These lines are important, mosty  replicate-wild-ignore-table     = %.tmpTable%

replicate-ignore-db             = db_tmp
replicate-wild-ignore-table     = %.tmpTable%
replicate-wild-ignore-table     = db_tmp.%

Are you able to reproduce shared code which is ready to use and do not need any data tables?
I have checked/tried if replicated via mysqlbinlog on relaylog on  S3.

My configuration is master-master via 3 servers.  If I run my code on S1, is replicated to S2, not S3.
If I run my code on S2, is replicated to S3, not to S1.

You should be able to reproduce the error via my code, can you run it and check relay log via
mysqlbinlog [relay_bin_log] | grep tmpTable_ ?

Because of setting
replicate-wild-ignore-table     = %.tmpTable%

It should be always empty output, because tmpTable is ignored from replication.

Update of synopsis after detailed analysis, which showed the reason of error.

Are you able to reproduce this replication error? Please follow my steps, I have to minify all my production code to this very simple code. It take about 5 hours and it would be nice, if you can try reproduce problem, which I have found out.  Or if you need more info how to reproduce in time, when I remember this case. Thank you.

Hi,

Thanks for the final test case, I did manage to reproduce the problem.

Hi, how does it looks like, is any chance to fix this issue?

I made some tests now and it looks like solved issue now. 
Maybe this has been affected by some other bugfix.