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

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

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.5.32, 10.1, 10.0, 5.5
        • Fix Version/s: 10.1
        • Component/s: Admin statements
        • Labels:

          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

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8494 mysqldump ignore-table doesn't ignore mysql.slow_log or mysql.general_log

                • Major - Major loss of function.
                • Closed
                links to

                Web Link Bug #69970 Can't restore a mysqldump if --add-drop-database meets general_log

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  http://bugs.mysql.com/bug.php?id=69953 was fixed in 10.1, so the problem mostly went away there, although http://bugs.mysql.com/69970 is still open, and statements for LOG_OUTPUT and such have not been added.

                  Show
                  elenst Elena Stepanova added a comment - http://bugs.mysql.com/bug.php?id=69953 was fixed in 10.1, so the problem mostly went away there, although http://bugs.mysql.com/69970 is still open, and statements for LOG_OUTPUT and such have not been added.

                    People

                    • Assignee:
                      Unassigned
                      Reporter:
                      hholzgra Hartmut Holzgraefe
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated: