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

Can't restore a mysqldump if --add-drop-database meets general_log

    XMLWordPrintable

Details

    Description

      A full dump created with

        mysqldump --all-databases --add-drop-database ...

      can't be restored on a system that has general_log or slow_query_log
      enabled as the DROP DATABASE command for the "mysql" database fails
      with

        ERROR 1580 (HY000): You cannot 'DROP' a log table if logging is enabled

      How to repeat:

      Create a full dump with

        mysqldump --all-databases --add-drop-database ... > dump.sql

      Then in mysql CLI:

        SET GLOBAL slow_query_log=1;
        SOURCE dump.sql

      Suggested fix:

      If bug#69953 / MDEV-4851 gets fixed first: extend DROP for "mysql" database with

        /*!50106 SET @OLD_LOG_OUTPUT=@@LOG_OUTPUT*/;
        /*!50106 SET GLOBAL LOG_OUTPUT='NONE'*/;
        /*!40000 DROP DATABASE IF EXISTS `mysql`*/;
        /*!50106 SET GLOBAL LOG_OUTPUT=@OLD_LOG_OUTPUT*/;

      as this will cover all present and potential future log tables;

      If bug #69953 / MDEV-4851 isn't fixed: explicitly disable general_log and
      slow_query_log

        /*!50106 SET @OLD_GENERAL_LOG=@@GENERAL_LOG*/;
        /*!50106 SET GLOBAL GENERAL_LOG=0*/;
        /*!50106 SET @OLD_SLOW_QUERY_LOG=@@SLOW_QUERY_LOG*/;
        /*!50106 SET GLOBAL SLOW_QUERY_LOG=0*/;
        /*!40000 DROP DATABASE IF EXISTS `mysql`*/;
        /*!50106 SET GLOBAL GENERAL_LOG=@OLD_GENERAL_LOG*/;
        /*!50106 SET GLOBAL SLOW_QUERY_LOG=@OLD_SLOW_QUERY_LOG*/;

      See also http://bugs.mysql.com/69970

      Attachments

        Issue Links

          Activity

            People

              danblack Daniel Black
              hholzgra Hartmut Holzgraefe
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.