3

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?

1
  • Which MySQL version ?
    – tdaget
    Jul 30, 2020 at 8:47

2 Answers 2

1

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

4
  • Wow. You are right. It works.. what the heck? How does it work on one db and not the other? Also what do you think the bug is? You can report it.. Jul 27, 2020 at 17:02
  • When right join and join gives an answer, and left join gives all NULL, than can't be right at all, besides exchanging bith tables and using right join also gives only NULL, that is a major bug
    – nbk
    Jul 27, 2020 at 17:09
  • Crazy! Well the bug is all yours to report :) Jul 27, 2020 at 17:10
  • you can find the bug report here bugs.mysql.com/bug.php?id=100339&thanks=4
    – nbk
    Jul 27, 2020 at 18:09
1

Avoid importing these databases:

mysql
information_schema
performance_schema
sys_schema
1
  • Importing these databases might fail as they are read only ...
    – tdaget
    Jul 30, 2020 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.