MySQL Import – Fix information_schema Full of NULLs After Database Import

MySQL

I am using MySQL workbench to dump 4 databases with tables that reference each other. As a note, there are no current problems with the databases at all – everything works as expected. When I try various data exports from MySQL workbench and then import it to another PC with MySQL workbench, everything appears to be fine except that queries to information_schema are returning results full of NULL

On my working setup, if I run this query

SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
        FROM information_schema.TABLE_CONSTRAINTS i 
        LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
        WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
        AND i.TABLE_SCHEMA = 'details'
        AND i.TABLE_NAME = 'locales'

I will get a whole bunch of information about fkey relationships.

After I dump and import these databases to another PC, and run this query again, I get the same amount of rows but all data is NULL. When I investigate the tables in MySQL workbench, they all show proper foreign keys! But I need the above query to produce correct results because I use it in my PHP application to display foreign keys to the user. What's worse – if I make two new tables and set up fkeys between them, information_schema still returns more rows full of NULL values using the same query as above. It's like the whole thing is broken for the database.

How can I get the above query to not return rows full of NULLs? What could be wrong?

Best Answer

mysql doesn't find a LEFT JOIN with both tables.

You can use:

SELECT kcu.*,tc.* FROM information_schema.KEY_COLUMN_USAGE kcu
LEFT JOIN  information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb';

SELECT kcu.*,tc.* FROM information_schema.TABLE_CONSTRAINTS tc 
RIGHT JOIN information_schema.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND tc.TABLE_SCHEMA = 'testdb'

I think it is a bug, which you can report, or else tell me and i report it

Related Solutions

MySQL import LOCK doesn’t prevent PRIMARY key issues

Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:

) ENGINE=InnoDB AUTO_INCREMENT=<some-number> ...

You may want to consider one of three(3) things

SUGGESTION #1 : mysqldump the database with table structure

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database > my_database_data.sql

That way, the auto_increment is attached to the table definition

SUGGESTION #2 : mysqldump the data and table structure separately

imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > my_database_schema.sql

You would load my_database_schema.sql first, then my_database_data.sql

SUGGESTION #3 : Read the next auto_increment of every table into a script

You can use a query like this to make the SQL script:

SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;

In the OS, you can do it like this:

SQLSTMT="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql

Simply run autoinc.sql before my_database_data.sql

Give it a Try !!!

MySQL – Troubleshooting MySqlDump’s DISABLE KEYS Ineffectiveness on Import

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE:

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

No mention is ever made of InnoDB in context with ALTER TABLE ... DISABLE/ENABLE KEYS;

Even if you run ALTER TABLE ... DISABLE KEYS; against an InnoDB table, it generates a warning:

mysql> show create table mytimes\G
*************************** 1. row ***************************
       Table: mytimes
Create Table: CREATE TABLE `mytimes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totalTime` int(11) NOT NULL,
  `totalTimeDesc` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table mytimes disable keys;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytimes' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

That's why there is no affect. Please recall that @jynus mentioned the same thing in his answer in bulletpoint 7.

Keep in mind also that MyISAM keeps data and indexes in two separate files (.MYD for data, .MYI for indexes), so it would be trivial to disable and enable indexes. InnoDB keeps the PRIMARY KEY and row data in the same InnoDB pages (via the Clustered Index). Secondary indexes will carry the PRIMARY KEY as an attachment to every secondary index leaf entry. Since the data and indexes are intertwined via the Clustered Index, no one has, as of yet, attempted to implement DISABLE KEYS and ENABLE KEYS in InnoDB.

Best Answer

Related Solutions

MySQL import LOCK doesn’t prevent PRIMARY key issues

Give it a Try !!!

MySQL – Troubleshooting MySqlDump’s DISABLE KEYS Ineffectiveness on Import

Related Question