3

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.

9
  • 1
    Did you recently upgrade from MySQL 5.5 ? Oct 10 '14 at 20:04
  • 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 ? Oct 10 '14 at 20:17
  • thirty nine (39)
    – fhuseynli
    Oct 10 '14 at 20:24
  • This is a little messy. I'll explain in my answer ... Oct 10 '14 at 20:25
11

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

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 !!!

2
  • 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. Sep 20 '16 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.