Bug #100691 Incorrect replication of excluded table via replicate-wild-ignore-table
Submitted: 30 Aug 2020 20:16 Modified: 10 Sep 2020 10:08
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

[30 Aug 2020 20:16] Michal Vrabel
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.
[2 Sep 2020 17:36] MySQL Verification Team
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
[2 Sep 2020 18:22] Michal Vrabel
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
[2 Sep 2020 18:46] Michal Vrabel
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.
[3 Sep 2020 15:34] MySQL Verification Team
Hi,

No, I'm not reproducing this.
[4 Sep 2020 6:03] Michal Vrabel
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.
[4 Sep 2020 8:09] MySQL Verification Team
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
[4 Sep 2020 10:08] Michal Vrabel
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 ;
[4 Sep 2020 13:01] Michal Vrabel
fix:
*** In the otherFunction() are important two things **  
should be
*** In the someFunction() are important two things **
[4 Sep 2020 13:22] MySQL Verification Team
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
[4 Sep 2020 13:32] Michal Vrabel
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.
[5 Sep 2020 18:12] Michal Vrabel
Update of synopsis after detailed analysis, which showed the reason of error.
[9 Sep 2020 21:15] Michal Vrabel
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.
[10 Sep 2020 10:08] MySQL Verification Team
Hi,

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