Recently I got a report from a user that they could not create a database using phpMyAdmin.  When I logged in and did some testing I began getting errors when I attempted to create a user, a database or grant privileges as root…

The root cause was a misalignment of the grants due to a missed step during a patch cycle (as we haven’t manually upgraded).  Here is more about it and how I fixed the problem:

First doing some investigation…

# mysql -uroot -p
Server version: 5.5.52 MySQL Community Server (GPL) by Remi

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.52 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
Empty set (0.00 sec)

mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+--------------+
| column_count |
+--------------+
| 39 |
+--------------+
1 row in set (0.00 sec)

That last query should have the following numbers:

• If you get 43, MySQL 5.6
• If you get 42, MySQL 5.5
• If you get 39, MySQL 5.1
• If you get 37, MySQL 5.0

As you can see from the above output the numbers are not correct, as we are using version 5.5 and only had 39 column’s returned, instead of the expected 42.  This means that the upgrade wasn’t completed.  We can fix this with the following:

# mysql_upgrade -u root -p --upgrade-system-tables
Looking for 'mysql' as: mysql
The --upgrade-system-tables option was used, databases won't be touched.
Running 'mysql_fix_privilege_tables'...
OK

Rerunning the previous mysql queries we can see the changes:

# mysql -uroot -p
Server version: 5.5.52 MySQL Community Server (GPL) by Remi

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.52 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_schema='mysql' and table_name='user' and column_name='plugin';
+-------------+------------------+
| column_name | ordinal_position |
+-------------+------------------+
| plugin | 41 |
+-------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';
+--------------+
| column_count |
+--------------+
| 42 |
+--------------+
1 row in set (0.00 sec)

Now I can go back into the phpMyAdmin interface and everything is working again.  Problem Solved.

Hope this helps, if it does say hello.