[MDEV-18930] Failed CREATE OR REPLACE TEMPORARY not written into binary log makes data on master and slave diverge Created: 2019-03-14  Updated: 2019-08-05  Resolved: 2019-08-05

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Replication
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.2.27, 10.1.42, 10.3.18, 10.4.8

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sujatha Sivakumar (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description    

--source include/have_partition.inc
 
--source include/have_binlog_format_mixed.inc
 
--source include/master-slave.inc
 
 
 
CREATE TEMPORARY TABLE t1 (a INT NOT NULL);
 
--error ER_NO_SUCH_TABLE
 
LOAD DATA INFILE 'x' INTO TABLE x;
 
--error ER_PARTITION_NO_TEMPORARY
 
CREATE OR REPLACE TEMPORARY TABLE t1 (x INT) PARTITION BY HASH(x);
 
CREATE TABLE t1 (b INT);
 
INSERT INTO t1 VALUES (NULL);
 
 
 
--sync_slave_with_master
 
 
 
# Cleanup
 
--connection master
 
DROP TABLE t1;
 
--source include/rpl_end.inc

10.4 3568427d

 
Last_Errno	1048
 
Last_Error	Error 'Column 'a' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (NULL)'

The problem starts when CREATE OR REPLACE TEMPORARY TABLE doesn't get written to the binary log. Somehow, the previous failure affects it. So, the slave still has the temporary table with a INT NOT NULL, while the master has the table with b INT [NULL], hence the failure.

Partitioning doesn't play important role here, there are other limitations on temporary tables which cause the same effect, e.g. ROW_FORMAT=COMPRESSED with InnoDB.

 Comments   
Comment by Elena Stepanova [ 2019-03-25 ]

More variations of further replication abort (obvious, but mentioning them helps to find the existing bug report):

Same test case, with ALTER TABLE t1 PARTITION BY HASH(b); instead of INSERT...:

Last_SQL_Errno	1562
 
Last_SQL_Error	Error 'Cannot create temporary table with partitions' on query. Default database: 'test'. Query: 'ALTER TABLE t1 PARTITION BY HASH(b)'

or, with ALTER TABLE t1 ADD SYSTEM VERSIONING;:

Last_SQL_Errno	4137
 
Last_SQL_Error	Error 'TEMPORARY tables do not support system versioning' on query. Default database: 'test'. Query: 'ALTER TABLE t1 ADD SYSTEM VERSIONING'

Comment by Sujatha Sivakumar (Inactive) [ 2019-08-01 ]

Hello Sachin,

Can you please review the fix for MDEV-18930.

Patch: https://github.com/MariaDB/server/commit/1e58c579f5b61644a74045a0470d2c62b343eb75

Build Bot Link: http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.1-sujatha

Thank you

Comment by Sujatha Sivakumar (Inactive) [ 2019-08-05 ]

Fix has been implemented in version 10.1.42.

For 10.2 and above only the result file needs to be re-recorded.

10.2 patch: https://github.com/MariaDB/server/commit/9834755b9e9cd4afcd1b650dcb36ae6010ee21e1
10.3 patch: https://github.com/MariaDB/server/commit/3748f7b97e91c34fae044da0a3cfe418af582041
Patch is the same for 10.4 as well. Tested on build bot.

Generated at Thu Feb 08 08:47:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.