[MDEV-8494] mysqldump ignore-table doesn't ignore mysql.slow_log or mysql.general_log Created: 2015-07-17  Updated: 2018-07-01  Resolved: 2015-08-17

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.0.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Michiel Hazelhof Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian Jessie X64

Issue Links:
Relates
relates to MDEV-4875 Can't restore a mysqldump if --add-dr... Closed

 Description   

When doing a full DB dump with mysqldump some tables are dumped regardles of their appearance in --ignore-table.

/usr/bin/mysqldump --defaults-file=/etc/mysql/mydump.cnf -u<user> -p<password> --add-locks --extended-insert --add-locks --add-drop-database --add-drop-table --single-transaction --routines --triggers --all-databases --all-tablespaces --allow-keywords --complete-insert --create-options --events --dump-date>/root/db.sql

cat /etc/mysql/mydump.cnf

[mysqldump]
 
ignore-table=mysql.general_log
 
ignore-table=mysql.slow_log

Still contains the two tables, other mysql.* tables can be ignored, worse in some cases it also contains the drop statement which causes an import to fail! (when dumping the whole server the export includes "/!40000 DROP DATABASE IF EXISTS `mysql`/;" which breaks if we attempt to import it).

 Comments   
Comment by Elena Stepanova [ 2015-07-19 ]

GieltjE, hi,

could you please clarify the last part of the description:

worse in some cases it also contains the drop statement which causes an import to fail! (when dumping the whole server the export includes "/!40000 DROP DATABASE IF EXISTS `mysql`/;" which breaks if we attempt to import it).

What are "some cases", which DROP statement you are talking about (is it DROP DATABASE which you quoted later, or something else?), and how exactly does it make the import to fail?

Thanks.

Comment by Michiel Hazelhof [ 2015-07-19 ]

It is the drop database I quoted later, it always happens when dumping the complete server, when importing it it stops with "ERROR 1580 (HY000) at line 639862: You cannot 'DROP' a log table if logging is enabled".

Comment by Elena Stepanova [ 2015-07-19 ]

Well, the DROP DATABASE statement is there because you request it explicitly by adding --add-drop-database to the command line.

Comment by Michiel Hazelhof [ 2015-07-19 ]

The drop statement should not be there as every table from that database is excluded, the problem is that the general_log and slow_log cannot be excluded for some reason (they always appear in the dump).

Comment by Elena Stepanova [ 2015-07-19 ]

Regarding the DROP DATABASE statement, I can convert it into a feature request.
I personally would be hesitant to vote for it, though, I don't like it when scripts try to think for me: if I request dropping databases on my command line, I want the databases to be dropped. ignore-table mainly means that I don't want its contents to be dumped, not that I want to turn on some hidden logic. What would be reasonable though, and would probably resolve the problem, is turn off log_output=TABLE before dropping mysql database or log tables.

Regarding dumping the table, I do not see it with your command line. The dump only contains CREATE TABLE IF NOT EXISTS for these two tables, but it does not attempt either to drop them or to dump the contents. Could you please check which version of mysqldump you are using, and if it's recent, create and attach an example of a dump file which you are getting? (You can remove all private data, we are only interested in the command line, file structure and file contents related to tables in question).

Comment by Michiel Hazelhof [ 2015-07-19 ]

mysqldump version: mysqldump Ver 10.15 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64)
Command: /usr/bin/mysqldump --all-databases --add-drop-database --add-drop-table --ignore-table=mysql.general_log --ignore-table=mysql.slow_log --ignore-table=mysql.column_stats > test.sql

Expected result: A dumb of all databases without general_log, slow_low and column_stats
Result: Only column_stats is properly excluded.

Comment by Elena Stepanova [ 2015-07-19 ]

As said above, I don't see this command either drop general_log and slow_log or dump their contents. It only adds CREATE TABLE IF NOT EXISTS statements which should not do any harm. This specifics of log tables was introduced in MySQL 5.5 and documented e.g. here: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

Before MySQL 5.5.25, mysqldump does not dump the general_log or slow_query_log tables for dumps of the mysql database. As of 5.5.25, the dump includes statements to recreate those tables so that they are not missing after reloading the dump file. Log table contents are not dumped.

If you observe anything different, please attach the resulting dump file. If the essence of the request is that CREATE TABLE IF NOT EXISTS statements must not be there, again, I can convert it into a feature request, if you can provide an example of them being harmful.

Comment by Michiel Hazelhof [ 2015-07-19 ]

The resulting dump file:

-- MySQL dump 10.15  Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64)
 
--
 
-- Host: localhost    Database: 
-- ------------------------------------------------------
 
-- Server version	10.0.20-MariaDB-1~jessie-log
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
 
--
 
-- Current Database: `mysql`
 
--
 
 
 
/*!40000 DROP DATABASE IF EXISTS `mysql`*/;
 
 
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
 
 
USE `mysql`;
 
 
 
<snip>
 
 
 
--
 
-- Table structure for table `general_log`
 
--
 
 
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE IF NOT EXISTS `general_log` (
 
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
 
  `user_host` mediumtext NOT NULL,
 
  `thread_id` bigint(21) unsigned NOT NULL,
 
  `server_id` int(10) unsigned NOT NULL,
 
  `command_type` varchar(64) NOT NULL,
 
  `argument` mediumtext NOT NULL
 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Table structure for table `slow_log`
 
--
 
 
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE IF NOT EXISTS `slow_log` (
 
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
 
  `user_host` mediumtext NOT NULL,
 
  `query_time` time(6) NOT NULL,
 
  `lock_time` time(6) NOT NULL,
 
  `rows_sent` int(11) NOT NULL,
 
  `rows_examined` int(11) NOT NULL,
 
  `db` varchar(512) NOT NULL,
 
  `last_insert_id` int(11) NOT NULL,
 
  `insert_id` int(11) NOT NULL,
 
  `server_id` int(10) unsigned NOT NULL,
 
  `sql_text` mediumtext NOT NULL,
 
  `thread_id` bigint(21) unsigned NOT NULL
 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 
 
-- Dump completed on 2015-07-19 17:34:14
 
 

Comment by Elena Stepanova [ 2015-07-19 ]

So, there are indeed only CREATE statements for general_log and slow_log. Why are they a problem?

I do see a real problem which your scenario reveals – that mysql schema should not be dropped while log_output=TABLE and either general log or slow log is enabled. I think mysqldump should still drop the schema if it's told to do so, but it should turn off this kind of logging first, and restore the value afterwards. It's a little cumbersome to check, but it's doable.

However, so far I do not see a problem with (not) ignoring mysql.slow_log or mysql.general_log. I'm not a big fan of the implicit logic for general/slow log tables introduced in MySQL while fixing http://bugs.mysql.com/bug.php?id=26121 and http://bugs.mysql.com/bug.php?id=45740, but it's already there, and making it even more complicated will only make things worse. Removing these CREATE TABLE IF NOT EXISTS statements would not make the ERROR 1580 you are getting to go away, but it would cause a problem later.

Not writing the DROP DATABASE `mysql` statement, as you suggest, would solve the problem in your particular case, but it would actually reduce mysqldump functionality for no good reason, which is not desirable.

Currently I see three reasonable ways to use ignore-table options, all of which mysqldump serves well enough.

1) I have db1 schema, in which t1, t2, ... t10 are valuable tables, while tGarbage is just some trash which should never exist after loading the clean dump, no matter whether it existed before loading it or not. Then, I can create the dump with --add-drop-database --ignore-table=db1.tGarbage, and it will do exactly that.

2) I have db2 schema, in which t1, t2, ... t10 are valuable tables, and tLocal is a very valuable table which should not be touched by the dump no matter what.Then, I can create the dump with --add-drop-table --ignore-table=db2.tLocal, and it will do exactly that.

3) I have both db1 and db2, for db1 I want to do (1), and for db2 I want to do (2). Since the settings are essentially different, I can run mysqldump twice, separately for each schema, and get the desired result.

If we do as you suggest and forbid dropping the schema if ignore-table is provided, in general case we will achieve nothing, but will lose the scenario (1).

Comment by Elena Stepanova [ 2015-08-17 ]

So, as described above, there is indeed an issue with mysqldump --add-drop-database when mysql schema is involved and TABLE logging is turned on; but it has already been filed as MDEV-4875 (upstream issue https://bugs.mysql.com/bug.php?id=69970). Otherwise, I don't see a bug in here, so I'm closing it as a duplicate of MDEV-4875. If you disagree, please comment, and if you make your case, the report will be re-opened.

Comment by Michiel Hazelhof [ 2018-07-01 ]

Sorry for the slow reply, but with MariaDB 10.2.16 these problems still occur, if a table is noted as being excluded it should be excluded no matter the use case (it just means the tool is broken).

root@fyn:~# /usr/bin/mysqldump --defaults-file=/etc/mysql/mydump.cnf -uGieltjE -p mysql
 
Enter password:
 
-- MySQL dump 10.16  Distrib 10.2.16-MariaDB, for debian-linux-gnu (x86_64)
 
--
 
-- Host: localhost    Database: mysql
 
-- ------------------------------------------------------
 
-- Server version       10.2.16-MariaDB-10.2.16+maria~stretch-log
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
 
--
 
-- Table structure for table `general_log`
 
--
 
 
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE IF NOT EXISTS `general_log` (
 
  `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
 
  `user_host` mediumtext NOT NULL,
 
  `thread_id` bigint(21) unsigned NOT NULL,
 
  `server_id` int(10) unsigned NOT NULL,
 
  `command_type` varchar(64) NOT NULL,
 
  `argument` mediumtext NOT NULL
 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
--
 
-- Table structure for table `slow_log`
 
--
 
 
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE IF NOT EXISTS `slow_log` (
 
  `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
 
  `user_host` mediumtext NOT NULL,
 
  `query_time` time(6) NOT NULL,
 
  `lock_time` time(6) NOT NULL,
 
  `rows_sent` int(11) NOT NULL,
 
  `rows_examined` int(11) NOT NULL,
 
  `db` varchar(512) NOT NULL,
 
  `last_insert_id` int(11) NOT NULL,
 
  `insert_id` int(11) NOT NULL,
 
  `server_id` int(10) unsigned NOT NULL,
 
  `sql_text` mediumtext NOT NULL,
 
  `thread_id` bigint(21) unsigned NOT NULL,
 
  `rows_affected` int(11) NOT NULL
 
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 
 
-- Dump completed on 2018-07-01 10:38:53

contents of mydump.cnf

cat /etc/mysql/mydump.cnf
 
[mysqldump]
 
quick
 
quote-names
 
ignore-table=mysql.column_stats
 
ignore-table=mysql.columns_priv
 
ignore-table=mysql.db
 
ignore-table=mysql.event
 
ignore-table=mysql.func
 
ignore-table=mysql.general_log
 
ignore-table=mysql.gtid_slave_pos
 
ignore-table=mysql.help_category
 
ignore-table=mysql.help_keyword
 
ignore-table=mysql.help_relation
 
ignore-table=mysql.help_topic
 
ignore-table=mysql.host
 
ignore-table=mysql.index_stats
 
ignore-table=mysql.innodb_index_stats
 
ignore-table=mysql.innodb_table_stats
 
ignore-table=mysql.ndb_binlog_index
 
ignore-table=mysql.plugin
 
ignore-table=mysql.proc
 
ignore-table=mysql.procs_priv
 
ignore-table=mysql.proxies_priv
 
ignore-table=mysql.roles_mapping
 
ignore-table=mysql.servers
 
ignore-table=mysql.slow_log
 
ignore-table=mysql.table_stats
 
ignore-table=mysql.tables_priv
 
ignore-table=mysql.time_zone
 
ignore-table=mysql.time_zone_leap_second
 
ignore-table=mysql.time_zone_name
 
ignore-table=mysql.time_zone_transition
 
ignore-table=mysql.time_zone_transition_type
 
ignore-table=mysql.user

In my opinion it would also be very handy to be able to just exclude entire databases, thus removing the need for exceedingly large ignore-table statements (which are error prone if there are new tables added over the years).

Generated at Thu Feb 08 07:27:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.