MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for December, 2014

Querying InnoDB Tables

without comments

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.

Written by maclochlainn

December 27th, 2014 at 1:18 am

MySQL Non-unique Indexes

with one comment

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.

Written by maclochlainn

December 24th, 2014 at 1:14 am

Popular PHP Frameworks

with 2 comments

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.

PHPFrameworkPopularity-1024x853

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.

Written by maclochlainn

December 23rd, 2014 at 1:55 pm

VMware 7 Upgrade

without comments

VMwareUpgrade7I 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.

Written by maclochlainn

December 23rd, 2014 at 12:17 am