[MDEV-378] myisam-repair-options DEFAULT or FORCE do not work for mysql.proc table Created: 2012-07-02  Updated: 2015-01-30

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - MyISAM
Affects Version/s: 5.5.25, 5.5
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 2
Labels: upstream, verified


 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;
 
 



 Comments   
Comment by Dreas van Donselaar [ 2015-01-30 ]

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.

Comment by Elena Stepanova [ 2015-01-30 ]

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)

Comment by Elena Stepanova [ 2015-01-30 ]

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 reported on IRC, are also observed on their servers.

serg, 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?

Generated at Thu Feb 08 06:28:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.