我正在使用 MySQL 5.6，我試圖為特定數據庫創建一個超級使用者，但我得到了

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

然後我想也許已經有一個使用者名和密碼相同。我刷新了權限並執行以下查詢：

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

在該查詢之後，我得到：ERROR 2013 (HY000): Lost connection to MySQL server during query 並且 MySQL 服務停止工作。

我認為你的問題歸結為撥款的錯位。

當您執行此查詢時

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

你應該以下號碼

• 如果你得到 43，MySQL 5.6
• 如果你得到 42，MySQL 5.5
• 如果你得到 39，MySQL 5.1
• 如果你得到 37，MySQL 5.0

這只是意味著您忘記了一個升級步驟

# mysql_upgrade --upgrade-system-tables

這應該填補缺失的列。

我之前討論過這個

• May 01, 2013:我可以從數據文件中找出 MySQL 的版本嗎？
• Apr 24, 2014: mysql: 恢復管理員使用者的所有權限 (StackOverflow)

既然你說有 39，那就意味著你從 MySQL 5.1 轉到 MySQL 5.5 時mysql.user沒有執行。mysql_upgrade --upgrade-system-tables

老實說，我不知道mysql_upgrade --upgrade-system-tables在 MySQL 5.6 中執行是否會成功地重新調整mysql.user兩個版本。

我建議通過執行以下操作來製作副本

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

製作完副本後，繼續執行mysql_upgrade --upgrade-system-tables。登錄mysql並SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';再次執行。希望它會讀到 43。

如果沒有，請像這樣複製使用者文件

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

如果您想手動更改設計，請嘗試以下操作：

使用 MySQL 5.6 佈局創建 mysql.user_new

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';

將贈款從舊轉移到新

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;

更新Create_tablespace_priv為root

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

調節開關

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

試一試，看看是否一切正常！！！

引用自：https://dba.stackexchange.com/questions/78923