Details
Description
I found that when restoring a mysqldump backup to a different database when binlog_do_db is enabled, that the tables are created, but the INSERTs are not executed.
Here is the configuration I narrowed it down to:
[mysqld]
|
server_id=3 |
log_bin = /var/log/mysql/mysql-bin.log
|
binlog_do_db = tax
|
Backup file:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; |
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; |
|
DROP TABLE IF EXISTS `benutzer`;
|
CREATE TABLE `benutzer` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`vorname` varchar(255) NOT NULL, |
`nachname` varchar(255) NOT NULL, |
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=10433 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
|
INSERT INTO `benutzer` VALUES (1,'Arno','Schäfer'); |
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; |
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; |
So when I try to load this backup file (narrowed down from the original mysqldump) into database "temp" (i.e. a different db from the database "tax" specified for binlog_do_db), the table "benutzer" is created, but the row is not inserted. If I remove the binlog_do_db line in the config, the problem goes away. If I remove either of the FOREIGN_KEY_CHECKS or UNIQUE_CHECKS lines, the problem goes away. If I am not mistaken, binlog_do_db should prevent the INSERT from being logged in the binlog, but not from being executed in the first place.
Attachments
Issue Links
- duplicates
-
MDEV-29989 binlog_do_db option breaks importing sql dumps
- Closed
- links to