Bug #65266 mysqldump: bogus: Error: 'Table 'INFORMATION_SCHEMA.FILES' doesn't exist'
Submitted: 9 May 2012 17:15 Modified: 3 Aug 2012 18:20
Reporter: Rick James Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.1+, 5.5.25 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[9 May 2012 17:15] Rick James
Description:
The error below does not seem to cause trouble, but it is scary.

I have not pinned down which combinations of versions cause it; mysqldump 5.1 and 5.5 have the problem when accessing 4.1 (and perhaps 5.0) systems.

-------
$ mysqldump -h cache02.db.ac.tw1  -u mysql_audit -p  --no-data --databases test
-- MySQL dump 10.13  Distrib 5.1.48, for unknown-linux-gnu (x86_64)
--
-- Host: cache02.db.ac.tw1    Database: test
-- ------------------------------------------------------
-- Server version       4.1.23-5b-Yahoo-SMP-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 */;
mysqldump: Error: 'Table 'INFORMATION_SCHEMA.FILES' doesn't exist' when trying to dump tablespaces

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;
/*!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 2012-05-09 17:01:38
-------

This seems to occur when the target (-h host) machine either has information_schema, but does not have `FILES`, or does not have information_schema at all.

How to repeat:
Use mysqldump 5.1.xx to dump a pre-5.1 database.

Suggested fix:
Avoid looking for information_schema.files and/or suppress the error message.

I do not believe that --compatible=name should be relevant to this bug.
[9 May 2012 18:42] Shane Bester
Does the option --no-tablespaces prevent the error?
[9 May 2012 22:27] Rick James
--no-tablespaces suppresses the error message (in the one case I just tried).

The source machine, being old, would probably not have any log_file groups or tablespaces to dump.
[10 May 2012 11:14] Shane Bester
I have a feeling this is "Not a bug".  It's recommended to always use the same version of mysqldump as the server (at least major version the same).
[10 May 2012 16:51] Sveta Smirnova
Thank you for the report.

Looks like you use old version. With current version I get:

[sveta@delly mysql-test]$ ../../mysql-5.5/bin/mysqldump -uroot -P 9306 -h 127.0.0.1 test
-- MySQL dump 10.13  Distrib 5.5.25, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version	5.0.97-debug-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 */;

--
-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server
--

...

This is a comment and does not break anything. This is just proper information message. So I close this as "Can't repeat"
[30 Jul 2012 10:16] Hugo Lombard
Could you try it against a 4.1 host, please?

Output below of 'mysqldump -q -e --no-data --no-create-info -h dbhost.example.com mysql user'

-- MySQL dump 10.13  Distrib 5.5.25a, for Linux (x86_64)
--
-- Host: dbhost.example.com    Database: mysql
-- ------------------------------------------------------
-- Server version       4.1.22-standard-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 */;
mysqldump: Error: 'Table 'INFORMATION_SCHEMA.FILES' doesn't exist' when trying to dump tablespaces
/*!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 2012-07-30 12:12:51
[30 Jul 2012 22:25] Rick James
5.1.58 still has the problem.

mysqldump -h ...  -u ... -p...  --no-data --databases test
-- MySQL dump 10.13  Distrib 5.1.58, for unknown-linux-gnu (x86_64)
--
-- Host: ...    Database: test
-- ------------------------------------------------------
-- Server version       4.1.23-5b-Yahoo-SMP-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 */;
mysqldump: Error: 'Table 'INFORMATION_SCHEMA.FILES' doesn't exist' when trying to dump tablespaces

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;
/*!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 2012-07-30 22:15:18

So, perhaps it is fixed in 5.5, but it does not seem to be fixed in 5.1.
[3 Aug 2012 18:11] Sveta Smirnova
Thank you for the feedback.

Please try mysqldump from latest version 5.1.63.
[3 Aug 2012 18:20] Sveta Smirnova
No feedback needed.

Verified as described: error happens when 5.1 or 5.5 mysqldump tries to backup from 4.1 server. Version 5.0 is not affected.

Workaround: remove this error message from final dump.