Archive for December, 2014
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.
MySQL Non-unique Indexes
Somebody wanted to know how to find any non-unique indexes in information_schema of the MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints. A similar query lets you find unique indexes in MySQL. Both queries are in this post.
You set the session variable like this:
SET @sv_table_name := 'member_lab'; |
You can query the indexes result with the following query:
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = @sv_table_name AND s.non_unique = TRUE AND NOT EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.table_name , s.seq_in_index; |
You can also reverse the logic and exclude implicit unique indexes on auto incrementing columns, like
SELECT s.table_name , s.index_name , s.seq_in_index , s.column_name FROM information_schema.statistics s WHERE s.table_name = @sv_table_name AND s.non_unique = FALSE AND NOT s.index_name = 'primary' AND EXISTS (SELECT null FROM information_schema.table_constraints tc WHERE s.table_name = tc.table_name AND s.index_name = tc.constraint_name) ORDER BY s.index_name , s.seq_in_index; |
Hope this helps those trying to find non-unique indexes for a table in MySQL.
Popular PHP Frameworks
My students often ask me about popular PHP frameworks and MySQL approaches. I wish a PHP framework choice was as clear as a JavaScript framework, like AngularJS. Unfortunately, PHP frameworks aren’t that clear cut.
It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t guarantee its accuracy). As far as jobs go, on Dice.com only Laravel had more than 3 positions referring to the framework by name. There were actually 42 PHP developer positions that mention Laravel out of 2,115 PHP developer positions. So, it seems learning a specific framework for PHP doesn’t yet have much market appeal.
While learning the basics of PHP are generic, frameworks expedite process and control pattern implementation. My suggestion to students is to target three to five employers that use a specific framework and start learning how to use the framework. I’d like to get opinions from those in the field whether this is the best advice.
As to MySQL, I suggest they learn native Mysqli and MySQL PDO. Any other suggestions on that from readers? Thanks as always.
VMware 7 Upgrade
I finally upgraded from VMware Fusion 6 to VMware Fusion 7 to take advantage of the new features. It was interesting to upgrade the Windows 7 virtual machine because of the unique failure message it raised.
The message said it was incompatible, and that I should navigate to:
Virtual Machine -> Settings -> Compatibility -> Upgrade
The Upgrade button checks the Allow upgrading the virtual hardware for this virtual machine checkbox. You will get prompted with the Would you like to upgrade this virtual machine? dialog for the next virtual machine.
Subscribe via RSS