bk commit into 5.0 tree (pem:1.1939) BUG#14233

[pem]

Below is the list of changes that have just been committed into a local
5.0 repository of pem. When pem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1939 05/10/26 15:34:57 pem@stripped +8 -0
  Fixed BUG#14233: Crash after tampering with the mysql.proc table
    Added error checking for errors when attempting to use stored procedures
    after the mysql.proc table has been dropped, corrupted, or tampered with.
    Test cases were put in a separate file (sp-destruct.test).

  mysql-test/t/sp-destruct.test
    1.1 05/10/26 15:34:48 pem@stripped +130 -0
    New result file for destruction of the mysql.proc table.

  mysql-test/t/sp-destruct.test
    1.0 05/10/26 15:34:48 pem@stripped +0 -0
    BitKeeper file /usr/home/pem/bug14233/mysql-5.0/mysql-test/t/sp-destruct.test

  mysql-test/r/sp-destruct.result
    1.1 05/10/26 15:34:47 pem@stripped +87 -0
    New test file for destruction of the mysql.proc table.

  sql/sql_trigger.h
    1.15 05/10/26 15:34:47 pem@stripped +1 -1
    Updated friend declaration for sp_cache_routines*.

  sql/sql_base.cc
    1.313 05/10/26 15:34:47 pem@stripped +24 -8
    Check for error from sp_cache_routines_* calls.

  sql/sp.h
    1.30 05/10/26 15:34:47 pem@stripped +5 -5
    Return error code from stored routine cache function.

  sql/sp.cc
    1.97 05/10/26 15:34:47 pem@stripped +63 -22
    Check and return error code when caching stored routines.
    In the case when no error message has been set, set one.

  sql/share/errmsg.txt
    1.51 05/10/26 15:34:47 pem@stripped +2 -0
    New error message for corrupted mysql.proc table.

  mysql-test/t/sp.test
    1.162 05/10/26 15:34:47 pem@stripped +2 -0
    Added comment.

  mysql-test/r/sp-destruct.result
    1.0 05/10/26 15:34:47 pem@stripped +0 -0
    BitKeeper file /usr/home/pem/bug14233/mysql-5.0/mysql-test/r/sp-destruct.result

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	pem
# Host:	mysql.comhem.se
# Root:	/usr/home/pem/bug14233/mysql-5.0

--- 1.312/sql/sql_base.cc	2005-10-25 11:02:41 +02:00
+++ 1.313/sql/sql_base.cc	2005-10-26 15:34:47 +02:00
@@ -1978,15 +1978,20 @@
   if (!thd->prelocked_mode && !thd->lex->requires_prelocking()
&& 
       thd->lex->sroutines_list.elements)
   {
-    bool first_no_prelocking, need_prelocking;
+    bool first_no_prelocking, need_prelocking, tabs_changed;
     TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
     DBUG_ASSERT(thd->lex->query_tables == *start);
     sp_get_prelocking_info(thd, &need_prelocking, &first_no_prelocking);
 
-    if ((sp_cache_routines_and_add_tables(thd, thd->lex,
-                                         first_no_prelocking) ||
-        *start) && need_prelocking)
+    if (sp_cache_routines_and_add_tables(thd, thd->lex,
+                                         first_no_prelocking,
+                                         &tabs_changed) < 0)
+    {
+      result= -1;               // Fatal error
+      goto err;
+    }
+    else if ((tabs_changed || *start) && need_prelocking)
     {
       query_tables_last_own= save_query_tables_last;
       *start= thd->lex->query_tables;
@@ -2110,9 +2115,13 @@
           tables->lock_type >= TL_WRITE_ALLOW_WRITE)
       {
         if (!query_tables_last_own)
-            query_tables_last_own= thd->lex->query_tables_last;
-        sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
-                                                      tables->table->triggers);
+          query_tables_last_own= thd->lex->query_tables_last;
+        if (sp_cache_routines_and_add_tables_for_triggers(thd, thd->lex,
+                                                   tables->table->triggers) < 0)
+        {
+          result= -1;               // Fatal error
+          goto err;
+        }
       }
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
     }
@@ -2133,9 +2142,16 @@
       /* We have at least one table in TL here. */
       if (!query_tables_last_own)
         query_tables_last_own= thd->lex->query_tables_last;
-      sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables->view);
+      if (sp_cache_routines_and_add_tables_for_view(thd, thd->lex,
+                                                    tables->view) < 0)
+      {
+        result= -1;               // Fatal error
+        goto err;
+      }
     }
   }
+
+ err:
   thd->proc_info=0;
   free_root(&new_frm_mem, MYF(0));              // Free pre-alloced block
 

--- 1.50/sql/share/errmsg.txt	2005-10-11 15:01:31 +02:00
+++ 1.51/sql/share/errmsg.txt	2005-10-26 15:34:47 +02:00
@@ -5422,3 +5422,5 @@
 	eng "Cannot add or update a child row: a foreign key constraint fails (%.192s)"
 ER_SP_BAD_VAR_SHADOW 42000
 	eng "Variable '%-.64s' must be quoted with `...`, or renamed"
+ER_SP_PROC_TABLE_CORRUPT
+	eng "The table mysql.proc is missing, corrupt, or contains bad data (internal code %d)"

--- 1.14/sql/sql_trigger.h	2005-09-15 01:56:04 +02:00
+++ 1.15/sql/sql_trigger.h	2005-10-26 15:34:47 +02:00
@@ -101,7 +101,7 @@
   void set_table(TABLE *new_table);
 
   friend class Item_trigger_field;
-  friend void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
+  friend int sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
                 Table_triggers_list *triggers);
 
 private:
--- New file ---
+++ mysql-test/r/sp-destruct.result	05/10/26 15:34:47
use mysql;
drop table if exists proc_backup;
create table proc_backup as select * from proc;
use test;
drop procedure if exists bug14233;
drop function if exists bug14233;
drop table if exists t1;
drop view if exists v1;
create procedure bug14233()
set @x = 42;
create function bug14233_f() returns int
return 42;
create table t1 (id int);
create trigger t1_ai after insert on t1 for each row call bug14233();
alter table mysql.proc drop type;
call bug14233();
ERROR HY000: The table mysql.proc is missing, corrupt, or contains bad data (internal code
-5)
create view v1 as select bug14233_f();
ERROR HY000: The table mysql.proc is missing, corrupt, or contains bad data (internal code
-5)
insert into t1 values (0);
ERROR HY000: The table mysql.proc is missing, corrupt, or contains bad data (internal code
-5)
flush table mysql.proc;
call bug14233();
ERROR HY000: Incorrect information in file: './mysql/proc.frm'
create view v1 as select bug14233_f();
ERROR HY000: Incorrect information in file: './mysql/proc.frm'
insert into t1 values (0);
ERROR HY000: Incorrect information in file: './mysql/proc.frm'
flush table mysql.proc;
call bug14233();
ERROR 42S02: Table 'mysql.proc' doesn't exist
create view v1 as select bug14233_f();
ERROR 42S02: Table 'mysql.proc' doesn't exist
insert into t1 values (0);
ERROR 42S02: Table 'mysql.proc' doesn't exist
use mysql;
create table proc as select * from proc_backup;
alter table proc add primary key (db,name,type);
use test;
flush table mysql.proc;
flush privileges;
delete from mysql.proc where name like 'bug14233%';
insert into mysql.proc
(
db, name, type, specific_name, language, sql_data_access, is_deterministic,
security_type, param_list, returns, body, definer, created, modified,
sql_mode, comment
)
values
(
'test', 'bug14233_1', 'FUNCTION', 'bug14233_1', 'SQL', 'READS_SQL_DATA', 'NO',
'DEFINER', '', 'int(10)',
'select count(*) from mysql.user',
'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
),
(
'test', 'bug14233_2', 'FUNCTION', 'bug14233_2', 'SQL', 'READS_SQL_DATA', 'NO',
'DEFINER', '', 'int(10)',
'begin declare x int; select count(*) into x from mysql.user; end',
'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
),
(
'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO',
'DEFINER', '', '',
'alksj wpsj sa ^#!@ ',
'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
);
select bug14233_1();
ERROR 0A000: Not allowed to return a result set from a function
create view v1 as select bug14233_1();
ERROR 0A000: Not allowed to return a result set from a function
select bug14233_2();
ERROR 2F005: FUNCTION bug14233_2 ended without RETURN
create view v1 as select bug14233_2();
select * from v1;
ERROR 2F005: FUNCTION bug14233_2 ended without RETURN
call bug14233_3();
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
drop trigger t1_ai;
create trigger t1_ai after insert on t1 for each row call bug14233_3();
insert into t1 values (0);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
delete from mysql.proc where name like 'bug14233%';
drop table mysql.proc_backup;
drop trigger t1_ai;
drop table t1;
drop view v1;

--- New file ---
+++ mysql-test/t/sp-destruct.test	05/10/26 15:34:48
#
# Destructive stored procedure tests
#
# We do horrible things to the mysql.proc table here, so any unexpected
# failures here might leave it in an undetermined state.
#
# In the case of trouble you might want to skip this.
#

# We're using --system things that probably doesn't work on Windows.
--source include/not_windows.inc

# Backup proc table
use mysql;
--disable_warnings
drop table if exists proc_backup;
--enable_warnings
create table proc_backup as select * from proc;
use test;

--disable_warnings
drop procedure if exists bug14233;
drop function if exists bug14233;
drop table if exists t1;
drop view if exists v1;
--enable_warnings

create procedure bug14233()
  set @x = 42;

create function bug14233_f() returns int
  return 42;

create table t1 (id int);
create trigger t1_ai after insert on t1 for each row call bug14233();

# Unsupported tampering with the mysql.proc definition
alter table mysql.proc drop type;
--error ER_SP_PROC_TABLE_CORRUPT
call bug14233();
--error ER_SP_PROC_TABLE_CORRUPT
create view v1 as select bug14233_f();
--error ER_SP_PROC_TABLE_CORRUPT
insert into t1 values (0);

flush table mysql.proc;

# Thrashing the .frm file
--system echo 'saljdlfa' > var/master-data/mysql/proc.frm
--error ER_NOT_FORM_FILE
call bug14233();
--error ER_NOT_FORM_FILE
create view v1 as select bug14233_f();
--error ER_NOT_FORM_FILE
insert into t1 values (0);


flush table mysql.proc;

# Drop the mysql.proc table
--system rm var/master-data/mysql/proc.*
--error ER_NO_SUCH_TABLE
call bug14233();
--error ER_NO_SUCH_TABLE
create view v1 as select bug14233_f();
--error ER_NO_SUCH_TABLE
insert into t1 values (0);

# Restore mysql.proc
use mysql;
create table proc as select * from proc_backup;
alter table proc add primary key (db,name,type);
use test;

flush table mysql.proc;
flush privileges;

delete from mysql.proc where name like 'bug14233%';

# Unsupported editing of mysql.proc, circumventing checks in "create ..."
insert into mysql.proc
(
  db, name, type, specific_name, language, sql_data_access, is_deterministic,
  security_type, param_list, returns, body, definer, created, modified,
  sql_mode, comment
)
values
(
  'test', 'bug14233_1', 'FUNCTION', 'bug14233_1', 'SQL', 'READS_SQL_DATA', 'NO',
  'DEFINER', '', 'int(10)',
  'select count(*) from mysql.user',
  'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
),
(
  'test', 'bug14233_2', 'FUNCTION', 'bug14233_2', 'SQL', 'READS_SQL_DATA', 'NO',
  'DEFINER', '', 'int(10)',
  'begin declare x int; select count(*) into x from mysql.user; end',
  'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
),
(
  'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO',
  'DEFINER', '', '',
  'alksj wpsj sa ^#!@ ',
  'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
);

--error ER_SP_NO_RETSET
select bug14233_1();
--error ER_SP_NO_RETSET
create view v1 as select bug14233_1();

--error ER_SP_NORETURNEND
select bug14233_2();
create view v1 as select bug14233_2();
--error ER_SP_NORETURNEND
select * from v1;

--error ER_PARSE_ERROR
call bug14233_3();
drop trigger t1_ai;
create trigger t1_ai after insert on t1 for each row call bug14233_3();
--error ER_PARSE_ERROR
insert into t1 values (0);

# Clean-up
delete from mysql.proc where name like 'bug14233%';
drop table mysql.proc_backup;
drop trigger t1_ai;
drop table t1;
drop view v1;


--- 1.161/mysql-test/t/sp.test	2005-10-24 22:53:55 +02:00
+++ 1.162/mysql-test/t/sp.test	2005-10-26 15:34:47 +02:00
@@ -13,6 +13,8 @@
 # Tests that require multiple connections, except security/privilege tests,
 #   go to sp-thread.
 # Tests that uses 'goto' to into sp-goto.test (currently disabled)
+# Tests that destroys system tables (e.g. mysql.proc) for error testing
+#   go to sp-destruct.
 
 use test;
 

--- 1.96/sql/sp.cc	2005-10-07 02:37:20 +02:00
+++ 1.97/sql/sp.cc	2005-10-26 15:34:47 +02:00
@@ -1414,20 +1414,22 @@
       first_no_prelock - If true, don't add tables or cache routines used by
                          the body of the first routine (i.e. *start)
                          will be executed in non-prelocked mode.
+      tabs_changed     - Set to TRUE some tables were added, FALSE otherwise
   NOTE
     If some function is missing this won't be reported here.
     Instead this fact will be discovered during query execution.
 
   RETURN VALUE
-    TRUE  - some tables were added
-    FALSE - no tables were added.
+     0 - success
+    -x - failure (error code, like SP_PARSE_ERROR et al)
 */
 
-static bool
+static int
 sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
                                      Sroutine_hash_entry *start, 
-                                     bool first_no_prelock)
+                                     bool first_no_prelock, bool *tabs_changed)
 {
+  int ret= 0;
   bool result= FALSE;
   bool first= TRUE;
   DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
@@ -1453,12 +1455,35 @@
       name.m_name.str+= 1;
       name.m_name.length= name.m_qname.length - name.m_db.length - 1;
 
-      if (db_find_routine(thd, type, &name, &sp) == SP_OK)
+      switch ((ret= db_find_routine(thd, type, &name, &sp)))
       {
-        if (type == TYPE_ENUM_FUNCTION)
-          sp_cache_insert(&thd->sp_func_cache, sp);
-        else
-          sp_cache_insert(&thd->sp_proc_cache, sp);
+      case SP_OK:
+        {
+          if (type == TYPE_ENUM_FUNCTION)
+            sp_cache_insert(&thd->sp_func_cache, sp);
+          else
+            sp_cache_insert(&thd->sp_proc_cache, sp);
+        }
+        break;
+      case SP_KEY_NOT_FOUND:
+        ret= SP_OK;
+        break;
+      case SP_OPEN_TABLE_FAILED:
+        /*
+          Force it to attempt opening it again on subsequent calls;
+          otherwise we will get one error message the first time, and
+          then ER_SP_PROC_TABLE_CORRUPT (below) on subsequent tries.
+        */
+        mysql_proc_table_exists= 1;
+        /* Fall through */
+      default:
+        /*
+          In some cases no error has been set (e.g. get field failed,
+          when the proc table has been tampered with).
+         */
+        if (! thd->net.report_error)
+          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), ret);
+        break;
       }
       delete newlex;
       thd->lex= oldlex;
@@ -1473,7 +1498,9 @@
     }
     first= FALSE;
   }
-  DBUG_RETURN(result);
+  if (tabs_changed)
+    *tabs_changed= result;
+  DBUG_RETURN(ret);
 }
 
 
@@ -1488,18 +1515,20 @@
       lex              - LEX representing statement
       first_no_prelock - If true, don't add tables or cache routines used by
                          the body of the first routine (i.e. *start)
+      tabs_changed     - Set to TRUE some tables were added, FALSE otherwise
                          
   RETURN VALUE
-    TRUE  - some tables were added
-    FALSE - no tables were added.
+     0 - success
+    -x - failure (error code, like SP_PARSE_ERROR et al)
 */
 
-bool
-sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock)
+int
+sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock,
+                                 bool *tabs_changed)
 {
   return sp_cache_routines_and_add_tables_aux(thd, lex,
            (Sroutine_hash_entry *)lex->sroutines_list.first,
-           first_no_prelock);
+           first_no_prelock, tabs_changed);
 }
 
 
@@ -1513,16 +1542,21 @@
       thd     - thread context
       lex     - LEX representing statement
       aux_lex - LEX representing view
+                         
+  RETURN VALUE
+     0 - success
+    -x - failure (error code, like SP_PARSE_ERROR et al)
 */
 
-void
+int
 sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
 {
   Sroutine_hash_entry **last_cached_routine_ptr=
                           (Sroutine_hash_entry **)lex->sroutines_list.next;
   sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines_list);
-  (void)sp_cache_routines_and_add_tables_aux(thd, lex, 
-                                             *last_cached_routine_ptr, FALSE);
+  return sp_cache_routines_and_add_tables_aux(thd, lex, 
+                                              *last_cached_routine_ptr, FALSE,
+                                              NULL);
 }
 
 
@@ -1536,12 +1570,18 @@
       thd      - thread context
       lex      - LEX respresenting statement
       triggers - triggers of the table
+
+  RETURN VALUE
+     0 - success
+    -x - failure (error code, like SP_PARSE_ERROR et al)
 */
 
-void
+int
 sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
                                               Table_triggers_list *triggers)
 {
+  int ret= 0;
+
   if (add_used_routine(lex, thd->stmt_arena, &triggers->sroutines_key))
   {
     Sroutine_hash_entry **last_cached_routine_ptr=
@@ -1559,10 +1599,11 @@
         }
       }
     }
-    (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                               *last_cached_routine_ptr, 
-                                               FALSE);
+    ret= sp_cache_routines_and_add_tables_aux(thd, lex,
+                                              *last_cached_routine_ptr, 
+                                              FALSE, NULL);
   }
+  return ret;
 }
 
 

--- 1.29/sql/sp.h	2005-09-15 01:56:03 +02:00
+++ 1.30/sql/sp.h	2005-10-26 15:34:47 +02:00
@@ -86,11 +86,11 @@
                          sp_name *rt, char rt_type);
 void sp_remove_not_own_routines(LEX *lex);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
-bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
-                                      bool first_no_prelock);
-void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
-                                               LEX *aux_lex);
-void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
+int sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
+                                     bool first_no_prelock, bool *tabs_changed);
+int sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
+                                              LEX *aux_lex);
+int sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
                                          Table_triggers_list *triggers);
 
 extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);