Bug #50624 crash in check_table_access during call procedure
Submitted: 26 Jan 2010 14:32 Modified: 19 Jun 2010 0:23
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0, 5.1.43,5.1.44,5.5.99-m3 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[26 Jan 2010 14:32] Matthias Leich
Description:
The crash happens in sql_parse.cc:5375
==> if (tables->security_ctx)
      sctx= tables->security_ctx;
    else
      sctx= backup_ctx;

My script:
----------
--disable_abort_on_error
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP FUNCTION IF EXISTS f1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

CREATE TABLE t1 ( col1 BIGINT);
# Harmless function: CREATE FUNCTION f1 () RETURNS TINYINT RETURN 17 ;
CREATE FUNCTION f1 () RETURNS INTEGER RETURN ( SELECT COUNT( * ) FROM v1  );
# This procedure is as "bad" as the following one CREATE PROCEDURE p1 () UPDATE v1 SET col1 = f1 ();
CREATE PROCEDURE p1 () DELETE FROM v1 WHERE col1 = f1 ();
CALL p1 ();
CREATE VIEW v1 AS SELECT col1 FROM t1 ;
CALL p1 ();
# Here comes the crash
CALL p1 ();

# Cleanup
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP VIEW v1;
DROP TABLE t1;

Result on 5.1.44-debug
(mysql-5.1 , revno: 3315 2010-01-15)
------------------------------------
...
main.ml104                               [ fail ]
        Test ended at 2010-01-26 15:15:18

CURRENT_TEST: main.ml104
--- ...r/ml104.result  2010-01-26
+++ ...r/ml104.reject  2010-01-26
@@ -1 +1,21 @@
-DUMMY protocol
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS v1;
+DROP FUNCTION IF EXISTS f1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1 ( col1 BIGINT);
+CREATE FUNCTION f1 () RETURNS INTEGER RETURN ( SELECT COUNT( * ) FROM v1  );
+CREATE PROCEDURE p1 () DELETE FROM v1 WHERE col1 = f1 ();
+CALL p1 ();
+ERROR 42S02: Table 'test.v1' doesn't exist
+CREATE VIEW v1 AS SELECT col1 FROM t1 ;
+CALL p1 ();
+CALL p1 ();
+ERROR HY000: Lost connection to MySQL server during query
...
100126 17:15:18 - mysqld got signal 11 ;
...
Thread 1 (process 29665):
#0  0x00007fee5c53bce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b1d813 in my_write_core (sig=11) at stacktrace.c:329
#2  0x00000000006bbfd7 in handle_segfault (sig=11) at mysqld.cc:2569
#3  <signal handler called>
#4  0x00000000006cb2dc in check_table_access (thd=0x11bd5c8, want_access=1, tables=0x0, number=4294967295, no_errors=false) at sql_parse.cc:5375
#5  0x00000000006cbce2 in check_one_table_access (thd=0x11bd5c8, privilege=8, all_tables=0x121b3f8) at sql_parse.cc:5135
#6  0x00000000006cbed2 in delete_precheck (thd=0x11bd5c8, tables=0x121b3f8) at sql_parse.cc:7378
#7  0x00000000006d144f in mysql_execute_command (thd=0x11bd5c8) at sql_parse.cc:3295
#8  0x00000000008a2789 in sp_instr_stmt::exec_core (this=0x121bb60, thd=0x11bd5c8, nextp=0x401f51b8) at sp_head.cc:2927
#9  0x00000000008a29cb in sp_lex_keeper::reset_lex_and_exec_core (this=0x121bba0, thd=0x11bd5c8, nextp=0x401f51b8, open_tables=false, instr=0x121bb60) at sp_head.cc:2748
#10 0x00000000008a90a8 in sp_instr_stmt::execute (this=0x121bb60, thd=0x11bd5c8, nextp=0x401f51b8) at sp_head.cc:2870
#11 0x00000000008a4fa5 in sp_head::execute (this=0x1219ec8, thd=0x11bd5c8) at sp_head.cc:1255
#12 0x00000000008a5d70 in sp_head::execute_procedure (this=0x1219ec8, thd=0x11bd5c8, args=0x11bf940) at sp_head.cc:1985
#13 0x00000000006d4f69 in mysql_execute_command (thd=0x11bd5c8) at sql_parse.cc:4383
#14 0x00000000006d70bf in mysql_parse (thd=0x11bd5c8, inBuf=0x12417e8 "CALL p1 ()", length=10, found_semicolon=0x401f6ef0) at sql_parse.cc:5961
#15 0x00000000006d7f3a in dispatch_command (command=COM_QUERY, thd=0x11bd5c8, packet=0x1212039 "CALL p1 ()", packet_length=10) at sql_parse.cc:1233
#16 0x00000000006d93a4 in do_command (thd=0x11bd5c8) at sql_parse.cc:874
#17 0x00000000006c5765 in handle_one_connection (arg=0x11bd5c8) at sql_connect.cc:1127
#18 0x00007fee5c537040 in start_thread () from /lib64/libpthread.so.0
#19 0x00007fee5b7e508d in clone () from /lib64/libc.so.6
#20 0x0000000000000000 in ?? ()

Releases showing this bug:
--------------------------
- 5.1.44-debug
  (mysql-5.1 , revno: 3315 2010-01-15)
- 5.1.43-debug
  (mysql-5.1-bugteam revno: 3331 2010-01-25)
- 5.5.99-m3-debug
  (mysql-next-mr , revno: 2965 2010-01-25)

Attention
  6.0.14-alpha-debug
  (mysql-6.0-codebase-bugfixing, revno: 3844 2010-01-25)
  does NOT show this bug

My environment:
---------------
- MySQL compiled from source with
  BUILD/compile-pentium64-debug-max
- Intel Core2Duo (64 Bit)
- Linux OpenSuSE 11.0 (64 Bit)

How to repeat:
See above
[26 Jan 2010 14:46] Matthias Leich
D2/W2/I3 because
- it is IMHO rather unlikely that somebody
  runs a command sequence like mine
- Workarounds
  1. Don't be careless when granting permissions
     -> less users which are able to run DoS attacks
  2. Don't create objects (procedure p1) which
     rely on objects to be created later (view v1)

I found this bug when developing a RQG grammar for replication testing.
In case of RQG tests the workarounds from above are not applicable.
Please fix this otherwise I will have most probably to
disable the use of procedures, functions and triggers.
[3 Feb 2010 11:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99040

3353 Davi Arnaut	2010-02-03
      Bug#50624: crash in check_table_access during call procedure
      
      This bug is just one facet of stored routines not being able to
      detect changes in meta-data (WL#4179). This particular problem
      is can be triggered within a single session due to the improper
      management of the pre-locking list if the view is expanded after
      the pre-locking list is calculated.
      
      Since the overall solution for the meta-data detection issue is
      planned for a later release, for now a workaround is used to
      fix this particular aspect that only involves a single session.
      The workaround is to flush the thread-local stored routine cache
      every time a view is created or modified, causing locally cached
      routines to be re-evaluated upon invocation.
     @ mysql-test/r/sp-bugs.result
        Add test case result for Bug#50624.
     @ mysql-test/t/sp-bugs.test
        Add test case for Bug#50624.
     @ sql/sp_cache.cc
        Update function description.
     @ sql/sql_view.cc
        Invalidate the SP cache if a view is being created or modified.
[13 Feb 2010 10:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100240

3339 Davi Arnaut	2010-02-13
      Bug#50624: crash in check_table_access during call procedure
      
      This bug is just one facet of stored routines not being able to
      detect changes in meta-data (WL#4179). This particular problem
      can be triggered within a single session due to the improper
      management of the pre-locking list if the view is expanded after
      the pre-locking list is calculated.
      
      Since the overall solution for the meta-data detection issue is
      planned for a later release, for now a workaround is used to
      fix this particular aspect that only involves a single session.
      The workaround is to flush the thread-local stored routine cache
      every time a view is created or modified, causing locally cached
      routines to be re-evaluated upon invocation.
     @ mysql-test/r/sp-bugs.result
        Add test case result for Bug#50624.
     @ mysql-test/t/sp-bugs.test
        Add test case for Bug#50624.
     @ sql/sp_cache.cc
        Update function description.
     @ sql/sql_view.cc
        Invalidate the SP cache if a view is being created or modified.
[13 Feb 2010 10:38] Davi Arnaut
Queued to 5.1-bugteam
[1 Mar 2010 8:43] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:davi.arnaut@sun.com-20100213103514-y7zvj1okm33nypoi) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:39] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:44] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[8 Apr 2010 14:34] Paul Dubois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

The server did not recognize that the stored procedure cache became
invalid if a view was created or modified within a procedure,
resulting in a crash.
[17 Jun 2010 12:07] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:52] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:34] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)