[MDEV-30834] restoring a dump created by mysqldump doesn't restore table rows if the database is excluded from binary log using binlog_ignore_db option Created: 2023-03-12  Updated: 2023-12-11  Resolved: 2023-12-11

Status: Closed
Project: MariaDB Server
Component/s: Replication, Server
Affects Version/s: 10.11.2
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2

Type: Bug Priority: Major
Reporter: Andrea Janna Assignee: Marko Mäkelä
Resolution: Duplicate Votes: 1
Labels: None
Environment:

Debian 12
mariadb-server debian package version 1:10.11.2+maria~debsid

Issue Links:
Duplicate
duplicates MDEV-29989 binlog_do_db option breaks importing ... Closed

 Description   

If binary logging is enabled but a specific database is excluded from the binary log by binlog_ignore_db option, then restoring a mysqldump to that specific database doesn't restore table rows although no error is reported.

Affected MariaDB version: 10.11.2
I also tested MariaDB version 10.5.18 which is not affected by this bug.

— Steps to reproduce the issue —
Execute the server with the following options:
log_bin=/var/log/mysql/mysql-bin.log
binlog_ignore_db=database1

Execute the following SQL commands:
CREATE DATABASE `database1`;
CREATE DATABASE `database2`;
use database1;
CREATE TABLE `table1` (`column1` text NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `table1` VALUES ('hello world');

Execute from the shell:
mysqldump database1 > database.sql
mysql database1 < database.sql
mysql database2 < database.sql

— Expected result —
database1 table1 and database2 table1 contain one row.

— Actual result —
database1 table1 is empty.
database2 table2 contains one row.

— Workaround —
In the sql commands generated by mysqldump replace
UNIQUE_CHECKS=0
with
UNIQUE_CHECKS=1

 Comments   
Comment by Sergei Golubchik [ 2023-05-19 ]

mtr test case:

source include/have_innodb.inc;
 
source include/have_binlog_format_mixed.inc;
 
set unique_checks=0, foreign_key_checks=0;
 
create table t1 (c text not null) engine=innodb;
 
insert t1 values ('***********');
 
select * from t1;
 
drop table t1;
 
set unique_checks=1, foreign_key_checks=1;

run with ./mtr --mysqld='--binlog_ignore_db=test'

Comment by Sergei Golubchik [ 2023-05-19 ]

likely, caused by InnoDB bulk insert optimization. requires unique_checks=0, foreign_key_checks=0, and engine=innodb.

Comment by Marko Mäkelä [ 2023-12-11 ]

This was fixed in MDEV-29989.

Generated at Thu Feb 08 10:19:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.