Bug #47870 Bogus "The table mysql.proc is missing,..."
Submitted: 6 Oct 2009 17:28 Modified: 24 Jun 2011 18:43
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1,6.0.14 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[6 Oct 2009 17:28] Matthias Leich
Description:
RQG test grammar:
-----------------
query:
   CALL s1.p1 |
   CREATE SCHEMA s1; CREATE PROCEDURE s1.p1() BEGIN END; DROP SCHEMA s1;

How to run the test:
--------------------
perl runall.pl \
   --basedir=<path to your MySQL release> \
   --threads=2 --queries=1000 --duration=30 \
   --mysqld=--log-output=file \
   --grammar=<grammar from above> \
   --vardir=<tmpfs recommended> --seed=1

Result on 6.0-codebase-bugfixing 2009-10-06
with slightly different configuration
-------------------------------------------
...
# 19:11:06 Starting
# 19:11:06  gentest.pl \
...
# 19:11:07 Starting 2 processes, 100 queries each, duration 300 seconds.
# 19:11:07 Query: CALL s1.p1 failed: 1305 PROCEDURE %s does not exist. Further errors of this kind will be suppressed.
  <-- This is to be expected.
# 19:11:07 Query: CALL s1.p1 failed: 1049 Unknown database '%s'. Further errors of this kind will be suppressed.
  <-- This is to be expected.
# 19:11:07 Query: CALL s1.p1 failed: 1305 PROCEDURE %s does not exist. Further errors of this kind will be suppressed.
  <-- This is to be expected.
# 19:11:07 Query: CALL s1.p1 failed: 1457 Failed to load routine s1.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
  <==== This must not happen and is most probably
        false alarm.

My environment:
---------------
- mysql-6.0-codebase-bugfixing 2009-10-06
  mysql-5.1-bugteam 2009-09-16
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[4 Nov 2009 15:55] Jon Olav Hauglid
Reproduceable using MTR on 5.1-bugteam if the following sync point is added:
=== modified file 'sql/sql_db.cc'
@@ -1630,6 +1631,8 @@ bool mysql_change_db(THD *thd, const LEX
   }
 #endif
 
+  DEBUG_SYNC(thd, "before_db_dir_check");
+
   if (check_db_dir_existence(new_db_file_name.str))
   {
     if (force_switch)

MTR test file:
==============
--source include/have_debug_sync.inc

CREATE SCHEMA s1;
CREATE PROCEDURE s1.p1() BEGIN END;

connect (con2, localhost, root);
SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema';
--send CALL s1.p1

connection default;
SET DEBUG_SYNC='now WAIT_FOR check_db';
DROP SCHEMA s1;
SET DEBUG_SYNC='now SIGNAL dropped_schema';

connection con2;
--error ER_SP_PROC_TABLE_CORRUPT      # <= This is the bogus error message
--reap;
[20 Nov 2009 9:44] Philip Stoev
(11:40:26 AM) joh: pstoev: The only consequence of this bug is that you get a different error message than you should have gotten. mysql.proc is not corrupted or anything like that.
[24 Jun 2011 18:43] Paul Dubois
Noted in 5.1.59, 5.5.15, 5.6.3 changelogs.

A race condition between loading a stored routine using the name
qualified by the database name and dropping that database resulted in
a spurious error message: The table mysql.proc is missing, corrupt,
or contains bad data  

CHANGESET - http://lists.mysql.com/commits/139789