CODEWITHSUNDEEP
mysqlphpmyadmin
Alan Kis
Alan Kis

Reputation: 1910

information_schema error when restoring database dump

As usual, I have done full export using PMA. Now, when I try to import the dumped data, I get this error:

#1044 - Access denied for user 'root'@'localhost' to database 'information_schema'

What’s going on?

Upvotes: 2

Views: 6989

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

Each table in the information_schema database is a TEMPORARY MEMORY TABLE

For example, notice information_schema.tables

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

Also, notice that whenever you run mysqldump using --all-databases, information_schema NEVER SHOWS UP.

The sole purposed of the information_schema database is to store metadata of the physical files and logical description of tables in the database. Whenever you run service mysql start, mysqld will create all the information_schema tables. Afterwards, mysqld populates these temporary memory tables with information concerning the physical files.

In terms of reloading a databases, please let mysqld handle the information_schema database. Please see my DBA StackExchange post from June 15, 2011 about the MySQL information_schema.

Upvotes: 1

Mario Wenig
Mario Wenig

Reputation: 122

Make sure your dump does not contain a CREATE DATABASE information_schema, the Information_Schema isn't something you should be creating. May or may not help you!

Upvotes: 2

Related Questions