Bug #98651 Inserting into temporary table from function breaks replication in MIXED mode
Submitted: 18 Feb 2020 23:49 Modified: 19 Feb 2020 11:59
Reporter: Alexey Gavrilov Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:8.0, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[18 Feb 2020 23:49] Alexey Gavrilov
We have encountered a replication error after upgrading on MySQL 8.0 when running replication in the MIXED mode (which is default for AWS RDS).

In our application we insert data into a temporary table based on a function result.  For example:

     INSERT INTO temp_table SELECT a_func();

, where a_func() returns a set of values. An attempt to replicate the statement above fails on a slave with a following error when running in the MIXED mode: 

1146 | Error 'Table 'test.temp_table' doesn't exist' on query. Default database: 'test'. Query: 'INSERT INTO temp_table SELECT temp_func()'

How to repeat:
You can reproduce the problem by setting up a master/slave replication, creating some function and trying to insert  the function result into a temporary table.

I have created a complete  test case in the GitHub repository: https://github.com/Alexey1Gavrilov/mysql-replication-bug

Steps to reproduce:
   -  clone the repo
   -  run `docker-compose up`. It will start master and slave docker containers, setup MIXED replication and create a dummy function
   - run `run.sh` script, it will start the replication 
   - the script will create a temporary table and insert a dummy value returned by the dummy function triggering the error and breaking the replication

To my experience the problem exists only on MySQL 8.0 (I've verified on 8.0.16 and 8.0.19). 

A possible workaround is to set the replication mode to 'ROW'.
[19 Feb 2020 11:59] MySQL Verification Team
Hello Alexey,

Thank you for the report and test case.
Observed this with 8.0.19 build.

[19 Feb 2020 12:02] MySQL Verification Team
Test results - 8.0.19

Attachment: 98651_8.0.19.results (application/octet-stream, text), 17.10 KiB.

[19 Feb 2020 12:04] MySQL Verification Team
Test results - 5.7.29

Attachment: 98651_5.7.29.results (application/octet-stream, text), 8.50 KiB.