MySQL Bugs: #33762: mysqldump can not dump INFORMATION

Bug #33762	mysqldump can not dump INFORMATION_SCHEMA
Submitted:	9 Jan 2008 11:46	Modified:	11 Dec 2009 21:17
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	6.0.4-p3, 5.0, 5.1 BK	OS:	Any
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
mysqldump can not be used to dump the information_schema table.

How to repeat:
$ cd mysql-test 
$ perl mysql-test-run.pl --skip-ndb --start-and-exit
$ ../bin/mysqldump --socket=var/tmp/master.sock INFORMATION_SCHEMA

Suggested fix:
It appears mysqldump tries to issue the following two queries:

SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('INFORMATION_SCHEMA'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME;

SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('INFORMATION_SCHEMA')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME;

both return zero rows.

information_schema dump for bug33634

Attachment: bug33634-i_s.dump.gz (application/x-gzip, text), 15.31 KiB.

Please disregard last attachment.

I am setting this bug to open because regardless of the fix for bug #21295, dumping the INFORMATION_SCHEMA database should be possible if INFORMATION_SCHEMA is explicitly specified in the command line.

Thank you for the report.

Verified as described.

There's no real reason to prevent INFORMATION_SCHEMA from being explicitly dumped, but there's certainly no reason to dump it when all databases are being dumped. Bug #21527 was related, because we need to avoid trying to lock the INFORMATION_SCHEMA tables.

Allow INFORMATION_SCHEMA to be dumped using mysqldump

Attachment: bug33762.patch (text/plain), 4.29 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73851

2893 Jim Winstead	2009-05-12
      mysqldump would not dump the INFORMATION_SCHEMA even when it was explicitly
      requested. (Bug #33762)
      modified:
        client/mysqldump.c
        mysql-test/r/mysqldump.result
        mysql-test/t/mysqldump.test

"[9 Jan 2008 14:19] Miguel Solorzano

Please see bug http://bugs.mysql.com/bug.php?id=21295 why the backup of
INFORMATION_SCHEMA was disable when doing a backup, however I tried to
find in our Manual about and didn't find, so maybe a Doc bug?.
Thanks in advance."

I don't know what you didn't find, but the behavior that this bug purports to fix is as documented.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html says:

"mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it."

Pushed to 5.1-bugteam and mysql-pe.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:jimw@mysql.com-20090713193847-xj6gbgm2a1zwwanv) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

There are still some problems here, I think.

The test case uses this command:

--exec $MYSQL_DUMP --compact --opt -d information_schema tables

I did my own test and found that "mysqldump --no-data information_schema tables" works on Mac OS X with the table name in any lettercase, but on Linux, I had to give the table name as TABLES. (Even though that is normally not true for references to I_S tables on Linux.)

Also, attempting to dump the entire database by not naming a table produces an error:

mysqldump --no-data information_schema
mysqldump: Got error: 1044: Access denied for user 'paul'@'localhost' to database 'information_schema' when using LOCK TABLES

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The problems noted at [10 Aug 15:23] have been opened as a separate bug report: Bug#49633

Noted in 5.1.38, 5.4.2, 5.5.0, 6.0.14 changelogs.

Previously, mysqldump would not dump the INFORMATION_SCHEMA database
and ignored it if it was named on the command line. Now, mysqldump
will dump INFORMATION_SCHEMA if it is named on the command line.
Currently, this requires that the --skip-lock-tables (or --skip-opt)
option be given.

After adding --skip-lock-tables to the options to mysqldump we now get the following error when requesting a backup of information_scheme explicity:-

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `INNODB_CMP_RESET`': Access denied; you need (at least one of) the PROCESS privilege(s) for this operation (1227)

mysqldump version:
mysql  Ver 14.14 Distrib 5.1.47, for portbld-freebsd7.0 (amd64) using  5.2

mysql server version:
mysql  Ver 14.14 Distrib 5.5.4-m3, for portbld-freebsd8.1 (amd64) using  5.2

was this fixed?

Add --no-tablespaces to solve the problem