Querying InnoDB Tables
Somebody ran into the following error message trying to query the innodb_sys_foreign and innodb_sys_foreign_cols tables from the information_schema database:
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation |
It’s easy to fix the error, except you must grant the PROCESS privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege global PROCESS privilege to the student user with the following command:
GRANT PROCESS ON *.* TO student; |
Then, you can run this query to resolve foreign keys to their referenced primary key column values:
SELECT SUBSTRING_INDEX(f.id,'/',-1) AS constraint_name , CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) AS foreign_key_column , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)) AS primary_key_column FROM innodb_sys_foreign f INNER JOIN innodb_sys_foreign_cols fc ON f.id = fc.id WHERE SUBSTRING_INDEX(f.for_name,'/',-1) = 'system_user_lab' ORDER BY CONCAT(SUBSTRING_INDEX(for_name,'/',-1),'.',SUBSTRING_INDEX(for_col_name,'/',-1)) , CONCAT(SUBSTRING_INDEX(ref_name,'/',-1),'.',SUBSTRING_INDEX(ref_col_name,'/',-1)); |
It returns the following:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
However, you can get the same information without granting the global PROCESS privilege. You simply use the table_constraints and key_column_usage tables, like this:
SELECT tc.constraint_name , CONCAT(kcu.table_name,'.',kcu.column_name) AS foreign_key_column , CONCAT(kcu.referenced_table_name,'.',kcu.referenced_column_name) AS primary_key_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'foreign key' AND tc.table_name = 'system_user_lab' ORDER BY tc.table_name , kcu.column_name; |
It prints the same output:
+---------------------+--------------------------------------+------------------------------------+ | constraint_name | foreign_key_column | primary_key_column | +---------------------+--------------------------------------+------------------------------------+ | system_user_lab_fk1 | system_user_lab.created_by | system_user_lab.system_user_id | | system_user_lab_fk2 | system_user_lab.last_updated_by | system_user_lab.system_user_id | | system_user_lab_fk3 | system_user_lab.system_user_group_id | common_lookup_lab.common_lookup_id | | system_user_lab_fk4 | system_user_lab.system_user_type | common_lookup_lab.common_lookup_id | +---------------------+--------------------------------------+------------------------------------+ 4 rows in set (0.00 sec) |
Hope this helps.
Subscribe via RSS