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

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

            This looks like a possible duplicate of MDEV-29989. Is it?

            marko Marko Mäkelä added a comment - This looks like a possible duplicate of MDEV-29989 . Is it?
            arnoschaefer Arno Schäfer added a comment - - edited

            Yes, looks like it, thanks. Ticket can be closed.

            arnoschaefer Arno Schäfer added a comment - - edited Yes, looks like it, thanks. Ticket can be closed.

            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.