| 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 | ||
[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. regards, Umesh
[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.
Description: 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'.