Details
Minor 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
- relates to
-
MDEV-8494 mysqldump ignore-table doesn't ignore mysql.slow_log or mysql.general_log
-
- Closed
-
- links to
Activity
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}}
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
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.
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*/;
|
The patch
https://github.com/MariaDB/server/pull/2107/commits/42423ddf42f67e8873fece822ab8fb05370d69f1
is OK to push. Thanks.
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.