Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.25, 5.5
-
Fix Version/s: 5.5
-
Component/s: Storage Engine - MyISAM
Description
Also filed as http://bugs.mysql.com/bug.php?id=65786.
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-based versions, 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:
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;
bzr version-info
revision-id: monty@askmonty.org-20120627141312-z65pj80390f0f5pp date: 2012-06-27 17:13:12 +0300 build-date: 2012-07-02 19:42:37 +0400 revno: 3460
Also reproducible on MySQL 5.5/5.6.
Not reproducible on MySQL 5.1 and 5.1-based versions of MariaDB.
Test case:
# to reproduce on MySQL, run with --mysqld=--myisam-recover-options=FORCE # (it's not default there) 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;
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Did you consider upgrading to 10.0? It seems to be fixed there somehow (I'm running tests to find out what exactly fixed it)
The test case doesn't fail on 10.0. I found the patch which fixes at least the part of the problem revealed by the test case from the description:
revno: 3427.36.30
revision-id: monty@askmonty.org-20131014211848-s8tfivk4k3eah3z1
parent: monty@askmonty.org-20131014211716-xflgmalxgzke4il4
committer: Michael Widenius <monty@askmonty.org>
branch nick: 10.0-parallel
timestamp: Tue 2013-10-15 00:18:48 +0300
message:
Flush the proc file after every modifications. This will avoid errors of type "Table './mysql/proc' is marked as crashed and should be repaired"
Here is the patch itself:
=== modified file 'sql/sp.cc'
--- sql/sp.cc 2013-06-06 15:51:28 +0000
+++ sql/sp.cc 2013-10-14 21:18:48 +0000
@@ -1167,6 +1167,9 @@
ret= SP_OK;
if (table->file->ha_write_row(table->record[0]))
ret= SP_WRITE_ROW_FAILED;
+ /* Make change permanent and avoid 'table is marked as crashed' errors */
+ table->file->extra(HA_EXTRA_FLUSH);
+
if (ret == SP_OK)
sp_cache_invalidate();
@@ -1256,6 +1259,8 @@
{
if (table->file->ha_delete_row(table->record[0]))
ret= SP_DELETE_ROW_FAILED;
+ /* Make change permanent and avoid 'table is marked as crashed' errors */
+ table->file->extra(HA_EXTRA_FLUSH);
}
if (ret == SP_OK)
@@ -1366,6 +1371,8 @@
ret= SP_WRITE_ROW_FAILED;
else
ret= 0;
+ /* Make change permanent and avoid 'table is marked as crashed' errors */
+ table->file->extra(HA_EXTRA_FLUSH);
}
if (ret == SP_OK)
@@ -1540,7 +1547,11 @@
if (nxtres != HA_ERR_END_OF_FILE)
ret= SP_KEY_NOT_FOUND;
if (deleted)
+ {
sp_cache_invalidate();
+ /* Make change permanent and avoid 'table is marked as crashed' errors */
+ table->file->extra(HA_EXTRA_FLUSH);
+ }
}
table->file->ha_index_end();
I applied it to 5.5 tree (locally of course), the test doesn't fail with the patch. However, I'm not sure if it's a generic solution – apparently it won't fix problems with other tables which, as Dreas van Donselaar reported on IRC, are also observed on their servers.
Sergei Golubchik, what do you think – should we apply the patch to 5.5? Is it safe, is it good, is it going to help in real-life scenarios?
We experience this bug randomly almost on a daily basis (we manage a very large amount of MariaDB servers). We have build a separate system to automatically fix the table (e.g. mysqlcheck --auto-repair --all-databases -F does manage to fix it), so it's not always easy to collect useful data. We did get a copy of mysql.proc in a crashed state but it does auto-repair in our testing environment. Although less frequently, procs_priv also appears affected sometimes.
Any information that can help to solve this, please let me know and I try to get it.