MySQL service stops after trying to grant privileges to a user

Question

I am using MySQL 5.6, I was trying to create a super user for specific database but I got

ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%'

Then I thought maybe there is already a user with the same name and password. I flushed privileges and run the following query:

grant all privileges on db1.* to 'superuser'@'localhost'

After that query I get: ERROR 2013 (HY000): Lost connection to MySQL server during query and MySQL service stops working.

I uninstalled MySQL 5.5 completely before installing MySQL 5.6. I installed it manually though. Do you think it is because of some variable changes? — fhuseynli, Oct 10 '14 at 20:16
Please run this query : SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; What is column_count ? — RolandoMySQLDBA, Oct 10 '14 at 20:17
Look at my answer, please. You said 39. That means the mysql.user was from MySQL 5.1. EEEEWWW !!! — RolandoMySQLDBA, Oct 10 '14 at 20:28

score 11 · Accepted Answer · edited May 23 '17 at 12:40

I think your problem boils down to a misalignment of grants.

When you run this query

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You should the following number

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

It simply means you forgot one more upgrade step

# mysql_upgrade --upgrade-system-tables

This should fill in the missing columns.

I discussed this before

May 01, 2013 : Can I find out what version of MySQL from the data files?
Apr 24, 2014 : mysql: Restore All privileges to admin user (StackOverflow)

Since you said that mysql.user has 39, than means you did not run mysql_upgrade --upgrade-system-tables when you went from MySQL 5.1 to MySQL 5.5.

To be honest with you, I do not know if running mysql_upgrade --upgrade-system-tables in MySQL 5.6 will successfully realign mysql.user two versions back.

I would suggest making a copy by doing the following

net stop mysql
cd D:\MySQL\mysql
copy user.frm user1.frm
copy user.MYD user1.MYD
copy user.MYI user1.MYI
net start mysql

After making the copy, go ahead and run mysql_upgrade --upgrade-system-tables. Login to mysql and run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; again. Hopefully it will read 43.

If it does not, copy the user files back like this

net stop mysql
cd D:\MySQL\mysql
del user.*
move user1.frm user.frm
move user1.MYD user.MYD
move user1.MYI user.MYI
net start mysql

If you want to change the design manually, try the following:

Create mysql.user_new with MySQL 5.6 layout

CREATE TABLE mysql.user_new (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

Move the Grants from Old to New

INSERT INTO mysql.user_new
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections)
SELECT
Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections
FROM mysql.user;

Update the `Create_tablespace_priv` for `root`

UPDATE mysql.user_new SET Create_tablespace_priv='Y' WHERE user = 'root';

Flip the Switch

ALTER TABLE mysql.user RENAME mysql.user_old;
ALTER TABLE mysql.user_new RENAME mysql.user;
FLUSH PRIVILEGES;

Give it a Try and See if everything works !!!

Do I need to add those through my.ini file? If thats the case, what variables and values are missing from 5.1 which 5.6 has them? — fhuseynli, Oct 10 '14 at 20:30
BTW MySQL 5.7 has 45 columns in mysql.user table and no password column. — RolandoMySQLDBA, Sep 20 '16 at 14:05

MySQL service stops after trying to grant privileges to a user

1 Answers1

Create mysql.user_new with MySQL 5.6 layout

Move the Grants from Old to New

Update the `Create_tablespace_priv` for `root`

Flip the Switch

Give it a Try and See if everything works !!!

Linked

MySQL service stops after trying to grant privileges to a user

1 Answers1

Create mysql.user_new with MySQL 5.6 layout

Move the Grants from Old to New

Update the Create_tablespace_priv for root

Flip the Switch

Give it a Try and See if everything works !!!

Linked

Update the `Create_tablespace_priv` for `root`