[MDEV-4875] Can't restore a mysqldump if --add-drop-database meets general_log Created: 2013-08-09  Updated: 2023-11-27  Resolved: 2022-05-06

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 5.5.32, 5.5, 10.0, 10.1
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Minor
Reporter: Hartmut Holzgraefe Assignee: Daniel Black
Resolution: Fixed Votes: 2
Labels: upstream, verified

Issue Links:
Relates
relates to MDEV-8494 mysqldump ignore-table doesn't ignore... Closed

 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

 Comments   
Comment by Elena Stepanova [ 2015-04-02 ]

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.

Comment by Nickolay Ihalainen [ 2020-09-16 ]

The issue also affects 10.4:

{{$ mariadb-dump --all-databases --add-drop-database > dump.sql
$ mariadb -e 'select version(), @@slow_query_log'
-------------------------------------+

version() @@slow_query_log

-------------------------------------+

10.4.12-MariaDB-log 1

-------------------------------------+
$ mysql < dump.sql
ERROR 1580 (HY000) at line 22: You cannot 'DROP' a log table if logging is enabled}}

Comment by Hartmut Holzgraefe [ 2022-05-01 ]

Got fixed somewhere between 10.2.34 and .35, 10.3.26 and .27, 10.4.15 and .16, 10.5.6 and .8, 10.6 and later were never affected ...

Or at least in those releases I could no longer reproduce it

Comment by Daniel Black [ 2022-05-02 ]

with MDEV-4851 fixed, I suspect this might still be reproducible if the destination MariaDB instances is logging the general or slow query log to a table.

Comment by Daniel Black [ 2022-05-05 ]

Modified suggest fix of:

  /*!50106 SET @OLD_LOG_OUTPUT=@@LOG_OUTPUT*/;
 
  /*M!100203 EXECUTE IMMEDIATE IF(@@LOG_OUTPUT='TABLE', "SET GLOBAL LOG_OUTPUT='NONE'", "DO 0") */;
 
  /*!40000 DROP DATABASE IF EXISTS `mysql`*/;
 
  /*!50106 SET GLOBAL LOG_OUTPUT=@OLD_LOG_OUTPUT*/;

Comment by Alexander Barkov [ 2022-05-06 ]

The patch
https://github.com/MariaDB/server/pull/2107/commits/42423ddf42f67e8873fece822ab8fb05370d69f1
is OK to push. Thanks.

Comment by Daniel Black [ 2022-05-06 ]

Thanks for the reviews bar

Generated at Thu Feb 08 06:59:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.