myisam-repair-options does not work for mysql.proc table

[Miguel Angel Nieto]

Same as bug http://bugs.mysql.com/bug.php?id=65786

As a summary, on 5.5 and 5.6 the table mysql.proc is not automatically repaired when running procedure-related DDL. It only starts to work when you run a SELECT on mysql.proc table.

The summary of the issue says it doesn't work with DEFAULT or FORCE but for me it also fails with BACKUP and QUICK.

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm with the test from upstream bug:

[openxs@centos mysql-test]$ ./mtr --mysqld=--myisam-recover=FORCE bug65786
Logging: ./mtr --mysqld=--myisam-recover=FORCE bug65786
2015-07-13 16:17:50 2793 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2015-07-13 16:17:50 2793 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2015-07-13 16:17:50 2793 [Note] Plugin 'FEDERATED' is disabled.
2015-07-13 16:17:50 2793 [Note] Binlog end
2015-07-13 16:17:50 2793 [Note] Shutting down plugin 'CSV'
2015-07-13 16:17:50 2793 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.23
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/openxs/dbs/p5.6/mysql-test/var'...
Installing system database...

==============================================================================

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug65786 [ fail ]
        Test ended at 2015-07-13 16:18:06

CURRENT_TEST: main.bug65786
--- /home/openxs/dbs/p5.6/mysql-test/r/bug65786.result 2015-07-13 16:13:43.855440703 +0300
+++ /home/openxs/dbs/p5.6/mysql-test/var/log/bug65786.reject 2015-07-13 16:18:06.248440679 +0300
@@ -0,0 +1,19 @@
+CREATE PROCEDURE p1() BEGIN END;
+# Crash and restart server
+# Succeeds with warnings on 5.1, but fails on 5.5:
+CREATE PROCEDURE p2() BEGIN END;
+ERROR HY000: Table './mysql/proc' is marked as crashed and should be repaired
+# Fails on 5.5:
+CREATE PROCEDURE p2() BEGIN END;
+ERROR HY000: Table './mysql/proc' is marked as crashed and should be repaired
+# Throws warnings:
+SELECT 1 FROM mysql.proc WHERE 0;
+1
+Warnings:
+Error 145 Table './mysql/proc' is marked as crashed and should be repaired
+Error 1194 Table 'proc' is marked as crashed and should be repaired
+Error 1034 1 client is using or hasn't closed the table properly
+# Succeeds:
+CREATE PROCEDURE p2() BEGIN END;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
...

[Peter Schwaller (peter-schwaller)]

Workaround is to run SELECT from mysql.proc upon startup after crash/non-clean shutdown. Since a workaround exists, Percona does not plan to fix this bug. This issue will be left open as it is still an open upstream bug.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1637