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
