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

• Submitted: 9 Aug 2013 7:00
• Modified: 12 Aug 2013 10:10
• Reporter: Hartmut Holzgraefe
• Status: Verified
• Category: MySQL Server: Logging
• Severity: S3 (Non-critical)
• Version: 5.5.33
• OS: Any
• CPU Architecture: Any

[9 Aug 2013 7:00] Hartmut Holzgraefe

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

(see also bug #69953)

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 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 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*/;

[12 Aug 2013 10:10] MySQL Verification Team

Hello Hartmut,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh

[16 Sep 2020 5:09] Nikolai Ikhalainen

The problem still happens with 8.0.21:

mysqldump -psecret --all-databases --add-drop-database > dump.sql
mysql -psecret < dump.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3552 (HY000) at line 24: Access to system schema 'mysql' is rejected.