Bug #33762 mysqldump can not dump INFORMATION_SCHEMA
Submitted: 9 Jan 2008 12:46 Modified: 5 Oct 17:16
Reporter: Philip Stoev
Status: Need Doc Info
Category:Client Severity:S2 (Serious)
Version:6.0.4-p3, 5.0, 5.1 BK OS:Any
Assigned to: Jim Winstead Target Version:
Triage: Triaged: D5 (Feature request) / R1 (None/Negligible) / E2 (Low)

[9 Jan 2008 12:46] Philip Stoev 
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.
[9 Jan 2008 12:59] Philip Stoev 
information_schema dump for bug33634

Attachment: bug33634-i_s.dump.gz (application/x-gzip, text), 15.31 KiB.
[9 Jan 2008 12:59] Philip Stoev 
Please disregard last attachment.
[19 Jan 2008 19:18] Philip Stoev 
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.
[23 Jan 2008 13:40] Sveta Smirnova 
Thank you for the report.

Verified as described.
[1 May 2008 23:30] Jim Winstead 
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.
[1 May 2008 23:31] Jim Winstead 
Allow INFORMATION_SCHEMA to be dumped using mysqldump

Attachment: bug33762.patch (text/plain), 4.29 KiB.
[12 May 19:39] Bugs System 
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
[12 May 20:14] Paul DuBois 
"[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."
[13 Jul 21:41] Jim Winstead 
Pushed to 5.1-bugteam and mysql-pe.
[4 Aug 21:50] Bugs System 
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)
[4 Aug 22:45] Bugs System 
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)
[1 Oct 7:59] Bugs System 
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)
[1 Oct 9:25] Bugs System 
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)
[1 Oct 15:25] Bugs System 
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)
[5 Oct 12:50] Bugs System 
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)