Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.27, 10.5.8, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
-
None
-
CentOS 8
Description
Hi,
I found a bug affecting recent MariaDB versions.
Versions tested:
- OS repo: MariaDB 5.5.68 (on CentOS 7) - Not affected
- OS repo: MariaDB 10.3.27 (On CentOS 8) - Affected
- MariaDB repo: MariaDB 10.5.8 (On CentOS 8) - Affected
(All with stock settings, e.g. stock binlog_format)
We have a master, with multiple slaves.
The slaves have log_slave_updates=1, to replicate to more slaves.
- Master: MariaDB 5.5.68 on CentOS 7 from OS repository.
- Slave #1: MariaDB 10.5.8 On CentOS 8 from MariaDB repository. Also tested 10.3.27 from OS repository.
- Slave #2: MariaDB 5.5.68 on CentOS 7 from OS repository.
We run an query on the master to update some table. The query is successfully replicated to slaves #1 and #2. However, slave #1 doesn't write it properly to the binary log, causing the further slaves actually lose rows.
The query we run on the master:
create temporary table tpool like cid_stock; |
load data local infile '/root/cidcsv.sql' into table tpool fields terminated by ',' lines terminated by '\n' (cid) set countrycode='IL'; |
delete from cid_stock where countrycode='IL'; |
insert into cid_stock select * from tpool where countrycode='IL'; |
The issue seems related to temporary tables, because it doesn't happen with a static table. With this query i can successfully see the rows in the binary log:
create table pool2 like cid_stock; |
load data local infile '/root/cidcsv.sql' into table pool2 fields terminated by ',' lines terminated by '\n' (cid) set countrycode='IL'; |
delete from cid_stock where countrycode='IL'; |
insert into cid_stock select * from pool2 where countrycode='IL'; |
What happens is that slave #1 correctly executes this, however it doesn't write it properly to the binary log. The load data infile statement is missing. I guess its ok for it to be missing because of binlog_format=mixed, however the rows aren't replicated either.
So what happens is that delete works, but the insert inserts 0 rows because the table is empty.
On slave #2 its fine, it correctly writes the load data infile to the binary log.
Binary log emitted by slave #1:
# at 14546
|
#210124 13:09:38 server id 8 end_log_pos 14588 CRC32 0xdb2fc10d GTID 0-8-96 ddl
|
/*!100001 SET @@session.gtid_seq_no=96*//*!*/;
|
# at 14588
|
#210124 13:09:38 server id 8 end_log_pos 14709 CRC32 0x47091aaf Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
SET @@session.pseudo_thread_id=44/*!*/;
|
create temporary table tpool like cid_stock
|
/*!*/;
|
# at 14709
|
#210124 13:09:38 server id 8 end_log_pos 14751 CRC32 0x2e83513f GTID 0-8-97 trans
|
/*!100001 SET @@session.gtid_seq_no=97*//*!*/;
|
START TRANSACTION
|
/*!*/;
|
# at 14751
|
#210124 13:09:38 server id 8 end_log_pos 14873 CRC32 0xfa6ce3aa Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
delete from cid_stock where countrycode='IL'
|
/*!*/;
|
# at 14873
|
#210124 13:09:38 server id 8 end_log_pos 14904 CRC32 0x634c53ca Xid = 227
|
COMMIT/*!*/;
|
# at 14904
|
#210124 13:09:38 server id 8 end_log_pos 14946 CRC32 0xad87d017 GTID 0-8-98
|
/*!100001 SET @@session.gtid_seq_no=98*//*!*/;
|
START TRANSACTION
|
/*!*/;
|
# at 14946
|
#210124 13:09:38 server id 8 end_log_pos 15088 CRC32 0xd5b71ed0 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
insert into cid_stock select * from tpool where countrycode='IL'
|
/*!*/;
|
# at 15088
|
#210124 13:09:38 server id 8 end_log_pos 15119 CRC32 0xb6ae6dbc Xid = 229
|
COMMIT/*!*/;
|
DELIMITER ;
|
# End of log file
|
Binary log emitted by slave #2:
# at 21828
|
#210124 13:09:38 server id 8 end_log_pos 21945 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
SET @@session.pseudo_thread_id=44/*!*/;
|
create temporary table tpool like cid_stock
|
/*!*/;
|
# at 21945
|
#210124 13:09:38 server id 8 end_log_pos 22024 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
BEGIN
|
/*!*/;
|
# at 22024
|
#210124 13:09:38 server id 8 end_log_pos 22223
|
#Begin_load_query: file_id: 17 block_len: 176
|
# at 22223
|
#210124 13:09:38 server id 8 end_log_pos 22498 Execute_load_query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-11-1' INTO TABLE `tpool` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`cid`) SET `countrycode`='IL'
|
/*!*/;
|
# file_id: 17
|
# at 22498
|
#210124 13:09:38 server id 8 end_log_pos 22578 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
COMMIT
|
/*!*/;
|
# at 22578
|
#210124 13:09:38 server id 8 end_log_pos 22642 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
BEGIN
|
/*!*/;
|
# at 22642
|
#210124 13:09:38 server id 8 end_log_pos 22760 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
delete from cid_stock where countrycode='IL'
|
/*!*/;
|
# at 22760
|
#210124 13:09:38 server id 8 end_log_pos 22787 Xid = 191
|
COMMIT/*!*/;
|
# at 22787
|
#210124 13:09:38 server id 8 end_log_pos 22851 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
BEGIN
|
/*!*/;
|
# at 22851
|
#210124 13:09:38 server id 8 end_log_pos 22989 Query thread_id=44 exec_time=0 error_code=0
|
SET TIMESTAMP=1611493778/*!*/;
|
insert into cid_stock select * from tpool where countrycode='IL'
|
/*!*/;
|
# at 22989
|
#210124 13:09:38 server id 8 end_log_pos 23016 Xid = 193
|
COMMIT/*!*/;
|
DELIMITER ;
|
# End of log file
|
Table structure:
CREATE TABLE `cid_stock` ( |
`countrycode` varchar(8) NOT NULL DEFAULT '', |
`cid` varchar(32) NOT NULL DEFAULT '', |
PRIMARY KEY (`countrycode`,`cid`), |
KEY `countrycode` (`countrycode`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Example CSV file for the load data infile:
4912399313
|
4912399314
|
4912399315
|
4912399316
|
4912399317
|
4912399318
|