Mysql
ERROR 1031 (HY000): ‘proc’ 的表儲存引擎沒有這個選項
我是 MySQL 管理的新手,本週一個客戶來找我有一個問題。他們在 Windows Server 環境中使用 Wamp 創建了一個應用程序。
出於某種原因,在 MySQL Workbench 中列出表時,會彈出一條錯誤消息
could not be fetched
。在輸出視窗中,拋出以下錯誤:
他們還報告了一個儲存過程消失了,當嘗試在 MySQL 中再次編譯它時,又產生了另一個錯誤:
ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option
一些表使用的是MyISAM引擎,所以我改變了它,現在都是INNODB,ROW FORMAT參數就像Do not Use。
其他可能很重要的資訊,這個基礎是在 5.1 版本中開發的,而這個有問題的基礎是在 5.7.14 版本中。
更新:我只是嘗試執行 mysqldump,在另一台伺服器上導入。並拋出了這個錯誤:
d:\wamp64\bin\mysql\mysql5.7.14\bin>mysqldump.exe -u root --routines --all-data bases --force > dump20170912.sql mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'bejarti'': Table s torage engine for 'proc' doesn't have this option (1031) mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'berbigier'': Table storage engine for 'proc' doesn't have this option (1031) mysqldump: Got error: 1031: Table storage engine for 'proc' doesn't have this op tion when using LOCK TABLES mysqldump: Couldn't execute 'show create table `proc`': Table storage engine for 'proc' doesn't have this option (1031) mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mysql'': Table sto rage engine for 'proc' doesn't have this option (1031) mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'new_schema'': Tabl e storage engine for 'proc' doesn't have this option (1031)
在 MySQL 5.6 中,模式中引入了五 (5) 個 InnoDB 表
mysql
。我在 4 年前對InnoDB 後的回答中提到並描述了這些表:錯誤:升級到 mysql 5.6 後未找到表“mysql”。“innodb_table_stats”對於 MySQL 5.7,它達到了 19。
mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.12 | +-----------+ 1 row in set (0.00 sec) mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +---------------------------+ | table_name | +---------------------------+ | engine_cost | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | plugin | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +---------------------------+ 19 rows in set (0.53 sec) mysql>
請注意其餘
mysql
表格mysql> select table_name,engine from information_schema.tables -> where table_schema='mysql' and engine <> 'InnoDB'; +------------------+--------+ | table_name | engine | +------------------+--------+ | columns_priv | MyISAM | | db | MyISAM | | event | MyISAM | | func | MyISAM | | general_log | CSV | | ndb_binlog_index | MyISAM | | proc | MyISAM | | procs_priv | MyISAM | | proxies_priv | MyISAM | | slow_log | CSV | | tables_priv | MyISAM | | user | MyISAM | +------------------+--------+ 12 rows in set (0.00 sec) mysql>
如圖所示,
mysql.proc
(儲存過程和函式的儲存位置)應該保留為 MyISAM。我之前提到過這些表計數(請參閱我的回答中的副標題“CAVEAT FOR MySQL 5.6/5.7 users”是否會破解 ibdata1 破壞僅使用 MyISAM 表的數據庫?)課程在這裡
您永遠不應將
mysql
架構中的任何表轉換為任何儲存引擎。改變它們只會讓事情變得更糟。這個問題的解決方案可能就像執行REPAIR TABLE mysql.proc;
.