Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32442

Tables empty after mysqldump restore when binlog_do_db is set for a different db

    XMLWordPrintable

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

          Activity

            People

              Unassigned Unassigned
              arnoschaefer Arno Schäfer
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.