ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

Question

I am trying to grant privileges for a user to a new database

mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword';

I get the following error

ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'

I am using MySQL 5.6

mysql> select @@version;
+-------------+
| @@version   |
+-------------+
| 5.6.24-72.2 |
+-------------+
1 row in set (0.00 sec)

I did find an article on MySQL on updating Native Passwords on 5.6.x and later

6.3.9.3 Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html

I ran the command they recommend as root

mysql> UPDATE mysql.user SET plugin = 'mysql_native_password'
    -> WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41);
ERROR 1054 (42S22): Unknown column 'plugin' in 'where clause'

Answer 1

Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6

If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL 5.6.

The column plugin is column #41 in mysql.user in MySQL 5.5/5.6

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.04 sec)

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.6.24    |
+-----------+
1 row in set (0.02 sec)

mysql>

That column does not appear in MySQL 5.1, 5.0 or 4.x. What this tells me is that somehow MySQL was upgraded to 5.6 but still has the mysql.user from 5.1 or older.

If you run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; and you get 39, I have just the fix for you.

See my post MySQL service stops after trying to grant privileges to a user on how manually fix mysql.user from 5.1 straight to to 5.6.

Answer 2

I had similar error during CREATE USER query.

I did following solution to rectify the user table.

ALTER TABLE `user` ADD `Create_tablespace_priv` ENUM('N','Y') NOT NULL DEFAULT 'N' AFTER `Trigger_priv`; 
ALTER TABLE `user` ADD `plugin` CHAR(64) NULL AFTER `max_user_connections`; 
ALTER TABLE `user` ADD `authentication_string` TEXT NULL DEFAULT NULL AFTER `plugin`; 
ALTER TABLE `user` ADD `password_expired` ENUM('N','Y') NOT NULL DEFAULT 'N' AFTER `authentication_string`; 

Now CREATE USER query works fine.!!

Rectification is achieved from the reference of the answer to Cannot GRANT privileges as root. Thank you RolandoMySQLDBA.!

Following helped to create above SQL:

+------------------------+------------------------+-----------------------------------+------+-----+---------+
| Field (MySQL 5.1)      | Field (MySQL 5.6)      | Type                              | Null | Key | Default |
+------------------------+------------------------+-----------------------------------+------+-----+---------+
| Host                   | Host                   | char(60)                          | NO   | PRI |         |
| User                   | User                   | char(16)                          | NO   | PRI |         |
| Password               | Password               | char(41)                          | NO   |     |         |
| Select_priv            | Select_priv            | enum('N','Y')                     | NO   |     | N       |
| Insert_priv            | Insert_priv            | enum('N','Y')                     | NO   |     | N       |
| Update_priv            | Update_priv            | enum('N','Y')                     | NO   |     | N       |
| Delete_priv            | Delete_priv            | enum('N','Y')                     | NO   |     | N       |
| Create_priv            | Create_priv            | enum('N','Y')                     | NO   |     | N       |
| Drop_priv              | Drop_priv              | enum('N','Y')                     | NO   |     | N       |
| Reload_priv            | Reload_priv            | enum('N','Y')                     | NO   |     | N       |
| Shutdown_priv          | Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |
| Process_priv           | Process_priv           | enum('N','Y')                     | NO   |     | N       |
| File_priv              | File_priv              | enum('N','Y')                     | NO   |     | N       |
| Grant_priv             | Grant_priv             | enum('N','Y')                     | NO   |     | N       |
| References_priv        | References_priv        | enum('N','Y')                     | NO   |     | N       |
| Index_priv             | Index_priv             | enum('N','Y')                     | NO   |     | N       |
| Alter_priv             | Alter_priv             | enum('N','Y')                     | NO   |     | N       |
| Show_db_priv           | Show_db_priv           | enum('N','Y')                     | NO   |     | N       |
| Super_priv             | Super_priv             | enum('N','Y')                     | NO   |     | N       |
| Create_tmp_table_priv  | Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |
| Lock_tables_priv       | Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |
| Execute_priv           | Execute_priv           | enum('N','Y')                     | NO   |     | N       |
| Repl_slave_priv        | Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |
| Repl_client_priv       | Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |
| Create_view_priv       | Create_view_priv       | enum('N','Y')                     | NO   |     | N       |
| Show_view_priv         | Show_view_priv         | enum('N','Y')                     | NO   |     | N       |
| Create_routine_priv    | Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |
| Alter_routine_priv     | Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |
| Create_user_priv       | Create_user_priv       | enum('N','Y')                     | NO   |     | N       |
| Event_priv             | Event_priv             | enum('N','Y')                     | NO   |     | N       |
| Trigger_priv           | Trigger_priv           | enum('N','Y')                     | NO   |     | N       |
|                        | Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |
| ssl_type               | ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |
| ssl_cipher             | ssl_cipher             | blob                              | NO   |     | NULL    |
| x509_issuer            | x509_issuer            | blob                              | NO   |     | NULL    |
| x509_subject           | x509_subject           | blob                              | NO   |     | NULL    |
| max_questions          | max_questions          | int(11) unsigned                  | NO   |     | 0       |
| max_updates            | max_updates            | int(11) unsigned                  | NO   |     | 0       |
| max_connections        | max_connections        | int(11) unsigned                  | NO   |     | 0       |
| max_user_connections   | max_user_connections   | int(11) unsigned                  | NO   |     | 0       |
|                        | plugin                 | char(64)                          | YES  |     |         |
|                        | authentication_string  | text                              | YES  |     | NULL    |
|                        | password_expired       | enum('N','Y')                     | NO   |     | N       |
+------------------------+------------------------+-----------------------------------+------+-----+---------+