MySQL ERROR 1031 (HY000) – Table Storage Engine for ‘proc’ Doesn’t Have This Option

innodbmyisamMySQLstored-procedures

I am new to MySQL administration, and this week a customer came to me with a problem. They created an application with Wamp in a Windows Server environment.

For some reason when listing the tables in the MySQL Workbench, an error pops up saying could not be fetched.

MySQL Workbench

And in the output window, the following error is thrown:

enter image description here

They also reported that a stored procedure has disappeared, and when trying to compile it again in MySQL, another error is generated:

ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option

Some tables were using the MyISAM engine, so I changed it and now all are as INNODB, and the ROW FORMAT parameters are like Do not Use.

Other information that may be important, this base was developed in a version 5.1, and this base with problems is in version 5.7.14.

UPDATE: I just tried to run mysqldump, to import on another server.
And this error was thrown:

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)

Best Answer

In MySQL 5.6, five(5) InnoDB tables were introduced to the mysql schema. I mentioned and described those tables in my 4-year-old answer to the post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

For MySQL 5.7, it went to 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>

Please note the remaining mysql tables

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>

As shown, mysql.proc (where stored procedures and functions are stored) is supposed to remain as MyISAM. I have mentioned these table counts before (see the subheading "CAVEAT FOR MySQL 5.6/5.7 users" in my answer Will hacking away ibdata1 destroy databases with only MyISAM tables?)

LESSON HERE

You should never convert any table in the mysql schema into any storage engine. Changing them only made matters worse. The solution to this problem may have been as simple as executing REPAIR TABLE mysql.proc;.

Related Question