Bug #65786 myisam-repair-options DEFAULT or FORCE do not work for mysql.proc table
Submitted: 2 Jul 2012 15:37 Modified: 2 Jul 2012 20:02
Reporter: Elena Stepanova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any

[2 Jul 2012 15:37] Elena Stepanova
Description:
If mysql.proc table was open before server crash, then after recovery the next attempts to execute procedure-related DDL cause error 145 (Table './mysql/proc' is marked as crashed and should be repaired). It happens with myisam-recover-options = DEFAULT as well as with FORCE. 

On 5.1, even the first DDL statement would succeed (with warnings). On 5.5, neither the first nor subsequent statements work, until the table is accessed explicitly, via CHECK, SHOW CREATE, SELECT etc.

Output of the provided test case from 5.5.25:

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;

How to repeat:
# Run as
# perl ./mtr --mysqld=--myisam-recover=FORCE <testname>

CREATE PROCEDURE p1() BEGIN END;

--echo # Crash and restart server
--enable_reconnect
--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
restart
EOF
--shutdown_server 0
--source include/wait_until_disconnected.inc
--source include/wait_until_connected_again.inc

--echo # Succeeds with warnings on 5.1, but fails on 5.5:
--error 145
CREATE PROCEDURE p2() BEGIN END;

--echo # Fails on 5.5:
--error 145
CREATE PROCEDURE p2() BEGIN END;

--echo # Throws warnings:
SELECT 1 FROM mysql.proc WHERE 0;

--echo # Succeeds:
CREATE PROCEDURE p2() BEGIN END;

DROP PROCEDURE p1;
DROP PROCEDURE p2;

# End of test case
[2 Jul 2012 20:02] Sveta Smirnova
Thank you for the report.

Verified as described.