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
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?