MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:ahristov Date:August 1 2006 2:45pm
Subject:bk commit into 5.0 tree (andrey:1.2238) BUG#9051
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of andrey. When andrey 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@stripped, 2006-08-01 16:45:22+02:00, andrey@lmy004. +26 -0
  Fix for bug#9051 Functions: Database name in fully qualified Function call is case sensitive
  
  The SP code did not respect the value of the startup option --lower-case-table-names.
  Namely, on platforms with filesystems (even on *nix with mounted FAT or others) which are by their
  nature case insensitive, the lookup was case sensitive.
  Example:
  create database MY_DATABASE;
  create procedure MY_DATABASE.MY_PROC1();
  --echo "This will yield an error"
  call my_database.my_proc1();
  --echo "This will work"
  call MY_DATABASE.MY_PROC1();
  
  In addition, this patch fixes a problem of the SP cache which used character set with
  case insensitive collation. This problem is some kind of shadowing, which has no
  security implications only because the SP cache is per connection.
  Example:
  create database miXed_case_DATA_base;
  create procedure miXed_case_DATA_base.proc1() select "miXed_case_DATA_base.proc1";
  --echo "Let's create a database with the same name but different case"
  create database mixed_case_data_BASE;
  --echo "Now a procedure in the newly created database"
  create procedure mixed_case_data_BASE.proc1() select "mixed_case_data_BASE.proc1";
  --echo "This will yield correct result. Will call the first procedure we created"
  call miXed_case_DATA_base.proc1();
  --echo "This will lead to an error. It will compile and run the first procedure and not the one we want"
  call mixed_case_data_BASE.proc1();
  
  
  #9051 is solved by adding additional column to mysql.proc (this means that the users will have to
  fix their privilege tables with scripts/mysql_fix_privilege_tables.sh)
  which is a copy of `db` but has case insensitive collation which is needed to handle --lower-case-table-names=2
  According to our documentation in this mode the tables are created with the originally supplied name but
  the lookup is with lowercased version. This can't work for routines without having a column with case insensitive
  collation. In the case of tables this is handled by the file system
  
  The second bug is fixed by changing the charset of the per thread SP cache from having case insensitive collation
  to have case sensitive collation.

  BitKeeper/etc/ignore@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +1 -0
    Added extra/yassl/taocrypt/src/libtaocrypt_la-integer.loT to the ignore list

  mysql-test/lib/init_db.sql@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +3 -1
    add a new column which will hold copy of `db` but
    has case insensitive collation. It will be used in case of
    lower_case_table_names==2 mode to find the appropriate stored routine.

  mysql-test/r/lowercase0_cs_fs.require@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +3 -0
    New BitKeeper file ``mysql-test/r/lowercase0_cs_fs.require''

  mysql-test/r/lowercase0_cs_fs.require@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/r/lowercase1_cs_fs.require@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +3 -0
    New BitKeeper file ``mysql-test/r/lowercase1_cs_fs.require''

  mysql-test/r/lowercase1_cs_fs.require@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/r/sp-destruct.result@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +7 -7
    update result

  mysql-test/r/sp_lower_case0.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +58 -0
    New BitKeeper file ``mysql-test/r/sp_lower_case0.result''

  mysql-test/r/sp_lower_case0.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +0 -0

  mysql-test/r/sp_lower_case1.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +14 -0
    New BitKeeper file ``mysql-test/r/sp_lower_case1.result''

  mysql-test/r/sp_lower_case1.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +0 -0

  mysql-test/r/sp_lower_case2.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +38 -0
    New BitKeeper file ``mysql-test/r/sp_lower_case2.result''

  mysql-test/r/sp_lower_case2.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +0 -0

  mysql-test/r/sp_lower_case_restart.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +1 -0
    New BitKeeper file ``mysql-test/r/sp_lower_case_restart.result''

  mysql-test/r/sp_lower_case_restart.result@stripped, 2006-08-01 16:45:18+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp-destruct.test@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +4 -5
    db_copy is added to the mysql.proc structure. Update test with
    appropriate INSERT statement values.

  mysql-test/t/sp_lower_case0.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +44 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case0.test''

  mysql-test/t/sp_lower_case0.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case1-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +1 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case1-master.opt''

  mysql-test/t/sp_lower_case1-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case1.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +18 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case1.test''

  mysql-test/t/sp_lower_case1.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case2-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +1 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case2-master.opt''

  mysql-test/t/sp_lower_case2-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case2.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +27 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case2.test''

  mysql-test/t/sp_lower_case2.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case_restart-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +1 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case_restart-master.opt''

  mysql-test/t/sp_lower_case_restart-master.opt@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  mysql-test/t/sp_lower_case_restart.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +5 -0
    New BitKeeper file ``mysql-test/t/sp_lower_case_restart.test''

  mysql-test/t/sp_lower_case_restart.test@stripped, 2006-08-01 16:45:17+02:00, andrey@lmy004. +0 -0

  scripts/mysql_create_system_tables.sh@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +2 -0
    update table structure as fix for bug#9051 
    Functions: Database name in fully qualified Function call is case sensitive

  scripts/mysql_fix_privilege_tables.sql@stripped, 2006-08-01 16:45:13+02:00, andrey@lmy004. +2 -0
    update table structure as fix for bug#9051 
    Functions: Database name in fully qualified Function call is case sensitive

  sql/mysqld.cc@stripped, 2006-08-01 16:45:14+02:00, andrey@lmy004. +1 -1
    initialize because SP lower_case_table_names rely on having initialized value.

  sql/sp.cc@stripped, 2006-08-01 16:45:14+02:00, andrey@lmy004. +96 -76
    Add new field to mysql.proc - `db_copy` . On the contrary to `db` it
    has case insensitive collation to be used when --lower-case-table-names=2
    In this mode the `db` part is saved in the original format but the lookup is
    lowercases. While this works out of the box on file systems which are case
    insensitive and we rely on it, we have to put more work to get the same result.
    
    Fixed handling of unexpected number of fields in mysql.proc. The checks were
    scattered all over the code, moved the code to open_proc_table_for_read which
    will fail in case of incorrect number of fields.
    
    Renamed db_create_routine() to sp_db_create_routine() and exported it, so
    there is no need for 2 small proxy functions sp_create_procedure and sp_create_function()

  sql/sp.h@stripped, 2006-08-01 16:45:14+02:00, andrey@lmy004. +1 -5
    exported db_create_routine as sp_db_create_routine
    Removed useless proxy functions sp_create_function() and sp_create_procedure()
    which did proxy to sp_db_create_routine()

  sql/sp_cache.cc@stripped, 2006-08-01 16:45:14+02:00, andrey@lmy004. +25 -5
    - Changed the character set of the sp cache to have case sensitive collation.
      This actually fixes the following bug:
      create database miXed_case_DATA_base;
      create procedure miXed_case_DATA_base.proc1() select "miXed_case_DATA_base.proc1";
      create database mixed_case_data_BASE;
      create procedure mixed_case_data_BASE.proc1() select "mixed_case_data_BASE.proc1";
      --echo "This will yield correct result"
      call miXed_case_DATA_base.proc1();
      --echo "This won't compile and run the second procedure but actually run the first"
      call mixed_case_data_BASE.proc1();
    
    Add trace log macroses

  sql/sp_head.cc@stripped, 2006-08-01 16:45:15+02:00, andrey@lmy004. +18 -4
    Move the constructor of sp_head to the .cc file
    Remove the usage of the proxy functions sp_create_function() and
    sp_create_procedure(). Pass directly type information to sp_db_create_routine()

  sql/sp_head.h@stripped, 2006-08-01 16:45:15+02:00, andrey@lmy004. +1 -6
    Move the constructor to the .cc file

  sql/sql_yacc.yy@stripped, 2006-08-01 16:45:16+02:00, andrey@lmy004. +104 -6
    Handle the --lower-case-table-names=0|1|2 option for stored routines
    check_db_name() do lowercase the LEX_STRING coming from the lexer, therefore
    we need to preserve it for mode 2. In mode 0 check_db_name() does not lowercase.
    Hence, the lowercased version is used only in mode 2. Because check_db_name() is
    used in a lot of places it is better to not change it because this will expand
    the patch probably significantly.

# 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:	andrey
# Host:	lmy004.
# Root:	/work/mysql-5.0-runtime-bug9051

--- 1.560/sql/mysqld.cc	2006-08-01 16:45:35 +02:00
+++ 1.561/sql/mysqld.cc	2006-08-01 16:45:35 +02:00
@@ -380,7 +380,7 @@
 uint mysqld_port, test_flags, select_errors, dropping_tables, ha_open_options;
 uint mysqld_port_timeout;
 uint delay_key_write_options, protocol_version;
-uint lower_case_table_names;
+uint lower_case_table_names= 0;
 uint tc_heuristic_recover= 0;
 uint volatile thread_count, thread_running;
 ulonglong thd_startup_options;

--- 1.474/sql/sql_yacc.yy	2006-08-01 16:45:35 +02:00
+++ 1.475/sql/sql_yacc.yy	2006-08-01 16:45:35 +02:00
@@ -1222,6 +1222,15 @@
 sp_name:
 	  ident '.' ident
 	  {
+            LEX_STRING db= $1;
+            /*
+              check_db_name() will lowercase $1 for lower_case_table_names > 0
+              Therefore, for mode 2 we have to preserve the original casing as
+              is stated in the docs.
+            */
+            if (lower_case_table_names == 2)
+              db.str= YYTHD->strdup($1.str);
+
             if (!$1.str || check_db_name($1.str))
             {
 	      my_error(ER_WRONG_DB_NAME, MYF(0), $1.str);
@@ -1232,8 +1241,8 @@
 	      my_error(ER_SP_WRONG_NAME, MYF(0), $3.str);
 	      YYABORT;
 	    }
-	    $$= new sp_name($1, $3);
-	    $$->init_qname(YYTHD);
+	    $$= new sp_name(db, $3);
+            $$->init_qname(YYTHD);
 	  }
 	| ident
 	  {
@@ -1244,11 +1253,16 @@
 	      my_error(ER_SP_WRONG_NAME, MYF(0), $1.str);
 	      YYABORT;
 	    }
+            /*
+              The current db is actually lowercased if 
+              lower_case_table_names is != 0 . On the other hand, in the branch
+              above it is needed to convert it to lowercase in this mode and it
+              is done by check_db_name()
+            */
             if (thd->copy_db_to(&db.str, &db.length))
               YYABORT;
 	    $$= new sp_name(db, $1);
-            if ($$)
-	      $$->init_qname(YYTHD);
+            $$->init_qname(YYTHD);
 	  }
 	;
 
@@ -1404,6 +1418,12 @@
 	    LEX *lex = Lex;
 
 	    lex->sql_command= SQLCOM_CALL;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $2->m_name.str);
 	    lex->spname= $2;
 	    lex->value_list.empty();
 	    sp_add_used_routine(lex, YYTHD, $2, TYPE_ENUM_PROCEDURE);
@@ -3300,6 +3320,12 @@
 	    LEX *lex=Lex;
 
 	    lex->sql_command= SQLCOM_ALTER_PROCEDURE;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $3->m_name.str);
 	    lex->spname= $3;
 	  }
 	| ALTER FUNCTION_SYM sp_name
@@ -3318,6 +3344,13 @@
 	    LEX *lex=Lex;
 
 	    lex->sql_command= SQLCOM_ALTER_FUNCTION;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $3->m_name.str);
+
 	    lex->spname= $3;
 	  }
         | ALTER view_algorithm_opt definer view_suid
@@ -4673,7 +4706,14 @@
 	| ident '.' ident '(' udf_expr_list ')'
 	  {
 	    LEX *lex= Lex;
+
 	    sp_name *name= new sp_name($1, $3);
+            /* Lowercased on lookup in modes 1 & 2. */
+            if (lower_case_table_names)
+            {
+              my_casedn_str(system_charset_info, name->m_db.str);
+              my_casedn_str(system_charset_info, name->m_name.str);
+            }
 
 	    name->init_qname(YYTHD);
 	    sp_add_used_routine(lex, YYTHD, name, TYPE_ENUM_FUNCTION);
@@ -4789,9 +4829,18 @@
               LEX_STRING db;
               if (thd->copy_db_to(&db.str, &db.length))
                 YYABORT;
+
               sp_name *name= new sp_name(db, $1);
               if (name)
+              {
+                /* Lookup is lowercased in modes 1 & 2 */
+                if (lower_case_table_names)
+                {
+                  my_casedn_str(system_charset_info, name->m_db.str);
+                  my_casedn_str(system_charset_info, name->m_name.str);
+                }
                 name->init_qname(thd);
+              }
 
               sp_add_used_routine(lex, YYTHD, name, TYPE_ENUM_FUNCTION);
               if ($4)
@@ -5969,6 +6018,12 @@
 	    }
 	    lex->sql_command = SQLCOM_DROP_FUNCTION;
 	    lex->drop_if_exists= $3;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $4->m_name.str);
 	    lex->spname= $4;
 	  }
 	| DROP PROCEDURE if_exists sp_name
@@ -5981,6 +6036,12 @@
 	    }
 	    lex->sql_command = SQLCOM_DROP_PROCEDURE;
 	    lex->drop_if_exists= $3;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $4->m_name.str);
 	    lex->spname= $4;
 	  }
 	| DROP USER clear_privileges user_list
@@ -6573,6 +6634,14 @@
 	    LEX *lex= Lex;
 
 	    lex->sql_command = SQLCOM_SHOW_CREATE_PROC;
+            /* 
+              Only in mode 1 the strings is lowercased. In mode 2 the routine
+              should be created with the original name. Later the lookups will
+              use lowercased names.
+              In rule sp_name m_db is lowercased by the call to check_db_name()
+            */
+            if (lower_case_table_names == 1)
+              my_casedn_str(system_charset_info, $3->m_name.str);
 	    lex->spname= $3;
 	  }
 	| CREATE FUNCTION_SYM sp_name
@@ -6580,6 +6649,14 @@
 	    LEX *lex= Lex;
 
 	    lex->sql_command = SQLCOM_SHOW_CREATE_FUNC;
+            /* 
+              Only in mode 1 the strings is lowercased. In mode 2 the routine
+              should be created with the original name. Later the lookups will
+              use lowercased names.
+              In rule sp_name m_db is lowercased by the call to check_db_name()
+            */
+            if (lower_case_table_names == 1)
+              my_casedn_str(system_charset_info, $3->m_name.str);
 	    lex->spname= $3;
 	  }
 	| PROCEDURE STATUS_SYM wild_and_where
@@ -6609,6 +6686,12 @@
             YYABORT;
 #else
             Lex->sql_command= SQLCOM_SHOW_PROC_CODE;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $3->m_name.str);
 	    Lex->spname= $3;
 #endif
           }
@@ -6619,6 +6702,12 @@
             YYABORT;
 #else
             Lex->sql_command= SQLCOM_SHOW_FUNC_CODE;
+            /* 
+              Lowercased on lookup in modes 1 & 2.
+              m_db is lowercased in rule sp_name.
+            */
+            if (lower_case_table_names)
+              my_casedn_str(system_charset_info, $3->m_name.str);
 	    Lex->spname= $3;
 #endif
           }
@@ -9087,7 +9176,7 @@
 	    my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "TRIGGER");
 	    YYABORT;
 	  }
-	
+
 	  if (!(sp= new sp_head()))
 	    YYABORT;
 	  sp->reset_thd_mem_root(YYTHD);
@@ -9154,6 +9243,12 @@
 	  LEX *lex=Lex;
 	  lex->udf.type= $1;
 	  lex->stmt_definition_begin= $2;
+          /*
+            Lowercased on lookup only in mode 1
+            m_db is lowercased in rule sp_name.
+          */
+          if (lower_case_table_names == 1)
+            my_casedn_str(system_charset_info, $4->m_name.str);
 	  lex->spname= $4;
 	}
 	create_function_tail
@@ -9168,7 +9263,6 @@
 	    my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "PROCEDURE");
 	    YYABORT;
 	  }
-	  
 	  lex->stmt_definition_begin= $2;
 	  
 	  /* Order is important here: new - reset - init */
@@ -9211,6 +9305,10 @@
 	{
 	  LEX *lex= Lex;
 	  sp_head *sp= lex->sphead;
+
+          /* Lowercased on lookup only in mode 1 */
+          if (lower_case_table_names == 1)
+            my_casedn_str(system_charset_info, $3->m_name.str);
 
 	  sp->init_strings(YYTHD, lex, $3);
 	  lex->sql_command= SQLCOM_CREATE_PROCEDURE;
--- New file ---
+++ mysql-test/r/lowercase0_cs_fs.require	06/08/01 16:45:17
Variable_name	Value
lower_case_file_system	OFF
lower_case_table_names	0

--- New file ---
+++ mysql-test/r/lowercase1_cs_fs.require	06/08/01 16:45:17
Variable_name	Value
lower_case_file_system	OFF
lower_case_table_names	1

--- New file ---
+++ mysql-test/r/sp_lower_case0.result	06/08/01 16:45:18
use test;
show databases like "mysqltest%";
Database (mysqltest%)
create procedure MiXeDCaSePrOcEdUrE() select "MiXeDCaSePrOcEdUrE";
call MiXeDCaSePrOcEdUrE();
MiXeDCaSePrOcEdUrE
MiXeDCaSePrOcEdUrE
drop procedure mixedcaseprocedure;
create database mysqltest_MiXeDCaSeDatabase;
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE";
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
ERROR 42000: PROCEDURE mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE does not exist
"Call by real name"
call mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE
"Test case insensitivity of the name part"
call mysqltest_MiXeDCaSeDatabase.mixedcaseprocedure();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE
"The DB part is case sensitive when lower_case_table_names is 0"
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
ERROR 42000: PROCEDURE mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE does not exist
call mysqltest_MIXEDcasedatabase.MiXeDCaSePrOcEdUrE();
ERROR 42000: PROCEDURE mysqltest_MIXEDcasedatabase.MiXeDCaSePrOcEdUrE does not exist
call mysqltest_MIXEDcasedatabase.mixedcaseprocedure();
ERROR 42000: PROCEDURE mysqltest_MIXEDcasedatabase.mixedcaseprocedure does not exist
create database mysqltest_mixedcasedatabase;
show databases like "mysqltest%";
Database (mysqltest%)
mysqltest_MiXeDCaSeDatabase
mysqltest_mixedcasedatabase
use mysqltest_mixedcasedatabase;
use test;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
routine_schema	routine_name	routine_type	routine_body	definer
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE	PROCEDURE	SQL	root@localhost
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
ERROR 42000: PROCEDURE mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE does not exist
create procedure mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE() select "mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE";
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
routine_schema	routine_name	routine_type	routine_body	definer
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE	PROCEDURE	SQL	root@localhost
mysqltest_mixedcasedatabase	MiXeDCaSePrOcEdUrE	PROCEDURE	SQL	root@localhost
call mysqltest_mixedcasedatabase.mixedcaseprocedure();
mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE
mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE
select db, name from mysql.proc order by db, name;
db	name
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE
mysqltest_mixedcasedatabase	MiXeDCaSePrOcEdUrE
drop database mysqltest_mixedcasedatabase;
select db, name from mysql.proc order by db, name;
db	name
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE
drop database mysqltest_MiXeDCaSeDatabase;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
routine_schema	routine_name	routine_type	routine_body	definer

--- New file ---
+++ mysql-test/r/sp_lower_case1.result	06/08/01 16:45:18
use test;
create database mysqltest_MIXED_database;
create procedure mysqltest_MIXED_database.MIXED_procedure() select "mysqltest_MIXED_database.MIXED_procedure";
call mysqltest_mixeD_database.mixeD_procedure();
mysqltest_MIXED_database.MIXED_procedure
mysqltest_MIXED_database.MIXED_procedure
call mysqltest_MIXED_database.MIXED_procedure();
mysqltest_MIXED_database.MIXED_procedure
mysqltest_MIXED_database.MIXED_procedure
create database  mysqltest_MIXED_DATAbase;
ERROR HY000: Can't create database 'mysqltest_mixed_database'; database exists
drop database mysqltest_MIXED_database;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mysqltest_mixedcase%";
routine_schema	routine_name	routine_type	routine_body	definer

--- New file ---
+++ mysql-test/r/sp_lower_case2.result	06/08/01 16:45:18
use test;
create database mysqltest_MiXeDCaSeDatabase;
show databases like "mysqltest%";
Database (mysqltest%)
mysqltest_MiXeDCaSeDatabase
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1";
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2";
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3";
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
routine_schema	routine_name	routine_type	routine_body	definer
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE1	PROCEDURE	SQL	root@localhost
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE2	PROCEDURE	SQL	root@localhost
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE3	PROCEDURE	SQL	root@localhost
"This should work because we run in mode 2"
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE1();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1
call mysqltest_MIXEDCASEDATABASE.MiXeDCaSePROCEDURE1();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE2();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2
call MYSQLTEST_mixedcaseDATABASE.mixedcaseprocedure3();
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3
mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3
select db, name from mysql.proc order by db, name;
db	name
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE1
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE2
mysqltest_MiXeDCaSeDatabase	MiXeDCaSePrOcEdUrE3
create database mysqltest_mixedcasedatabase;
ERROR HY000: Can't create database 'mysqltest_mixedcasedatabase'; database exists
drop database MYSQLTEST_mixedcaseDATABASE;
select db, name from mysql.proc order by db, name;
db	name
create database mysqltest_MiXeDCaSeDatabase;
drop database mysqltest_MiXeDCaSeDatabase;

--- New file ---
+++ mysql-test/r/sp_lower_case_restart.result	06/08/01 16:45:18
use test;

--- New file ---
+++ mysql-test/t/sp_lower_case0.test	06/08/01 16:45:17
#
# Test that checks the behaviour of SP and lower_case_table_names option
#
--require r/lowercase0_cs_fs.require
--disable_query_log
show variables like "lower_case_%";
--enable_query_log

use test;
show databases like "mysqltest%";
create procedure MiXeDCaSePrOcEdUrE() select "MiXeDCaSePrOcEdUrE";
call MiXeDCaSePrOcEdUrE();
drop procedure mixedcaseprocedure;
create database mysqltest_MiXeDCaSeDatabase;
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE";
#there should be an error as lowercased version of the database does not exist  
--error ER_SP_DOES_NOT_EXIST
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
--echo "Call by real name"
call mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE();
--echo "Test case insensitivity of the name part"
call mysqltest_MiXeDCaSeDatabase.mixedcaseprocedure();
--echo "The DB part is case sensitive when lower_case_table_names is 0"
--error ER_SP_DOES_NOT_EXIST
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
--error ER_SP_DOES_NOT_EXIST
call mysqltest_MIXEDcasedatabase.MiXeDCaSePrOcEdUrE();
--error ER_SP_DOES_NOT_EXIST
call mysqltest_MIXEDcasedatabase.mixedcaseprocedure();
create database mysqltest_mixedcasedatabase;
show databases like "mysqltest%";
use mysqltest_mixedcasedatabase;
use test;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
--error ER_SP_DOES_NOT_EXIST
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE();
create procedure mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE() select "mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE";
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
call mysqltest_mixedcasedatabase.mixedcaseprocedure();
select db, name from mysql.proc order by db, name;
drop database mysqltest_mixedcasedatabase;
select db, name from mysql.proc order by db, name;
drop database mysqltest_MiXeDCaSeDatabase;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";

--- New file ---
+++ mysql-test/t/sp_lower_case1-master.opt	06/08/01 16:45:17
--lower-case-table-names=1

--- New file ---
+++ mysql-test/t/sp_lower_case1.test	06/08/01 16:45:17
#
# Test that checks the behaviour of SP and lower_case_table_names option
#
# This tests restarts the server in old mode
--require r/lowercase1_cs_fs.require
--disable_query_log
show variables like "lower_case_%";
--enable_query_log

use test;
create database mysqltest_MIXED_database;
create procedure mysqltest_MIXED_database.MIXED_procedure() select "mysqltest_MIXED_database.MIXED_procedure";
call mysqltest_mixeD_database.mixeD_procedure();
call mysqltest_MIXED_database.MIXED_procedure();
--error ER_DB_CREATE_EXISTS
create database  mysqltest_MIXED_DATAbase;
drop database mysqltest_MIXED_database;
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mysqltest_mixedcase%";

--- New file ---
+++ mysql-test/t/sp_lower_case2-master.opt	06/08/01 16:45:17
--lower-case-table-names=2

--- New file ---
+++ mysql-test/t/sp_lower_case2.test	06/08/01 16:45:17
#
# Test that checks the behaviour of SP and lower_case_table_names option
#
use test;
--require r/lowercase2.require
--disable_query_log
show variables like "lower_case_table_names";
--enable_query_log

create database mysqltest_MiXeDCaSeDatabase;
show databases like "mysqltest%";
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE1";
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE2";
create procedure mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3() select "mysqltest_MiXeDCaSeDatabase.MiXeDCaSePrOcEdUrE3";
select routine_schema, routine_name, routine_type, routine_body, definer from information_schema.routines where routine_name like "mixedcase%";
--echo "This should work because we run in mode 2"
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE1();
call mysqltest_MIXEDCASEDATABASE.MiXeDCaSePROCEDURE1();
call mysqltest_mixedcasedatabase.MiXeDCaSePrOcEdUrE2();
call MYSQLTEST_mixedcaseDATABASE.mixedcaseprocedure3();
select db, name from mysql.proc order by db, name;
--error ER_DB_CREATE_EXISTS
create database mysqltest_mixedcasedatabase;
drop database MYSQLTEST_mixedcaseDATABASE;
select db, name from mysql.proc order by db, name;
create database mysqltest_MiXeDCaSeDatabase;
drop database mysqltest_MiXeDCaSeDatabase;

--- New file ---
+++ mysql-test/t/sp_lower_case_restart-master.opt	06/08/01 16:45:17


--- New file ---
+++ mysql-test/t/sp_lower_case_restart.test	06/08/01 16:45:17
#
# Test that checks the behaviour of SP and lower_case_table_names option
#
# This tests restarts the server in old mode
use test;


--- 1.14/mysql-test/lib/init_db.sql	2006-08-01 16:45:35 +02:00
+++ 1.15/mysql-test/lib/init_db.sql	2006-08-01 16:45:35 +02:00
@@ -555,5 +555,7 @@
                         'HIGH_NOT_PRECEDENCE'
                     ) DEFAULT '' NOT NULL,
   comment           char(64) collate utf8_bin DEFAULT '' NOT NULL,
-  PRIMARY KEY (db,name,type)
+  db_copy           char(64) DEFAULT '' NOT NULL,
+  PRIMARY KEY (db,name,type),
+  INDEX non_unique(db_copy, name, type)
 ) character set utf8 comment='Stored Procedures';

--- 1.34/scripts/mysql_fix_privilege_tables.sql	2006-08-01 16:45:35 +02:00
+++ 1.35/scripts/mysql_fix_privilege_tables.sql	2006-08-01 16:45:35 +02:00
@@ -521,6 +521,8 @@
                          char(77) collate utf8_bin DEFAULT '' NOT NULL,
                   MODIFY comment
                          char(64) collate utf8_bin DEFAULT '' NOT NULL;
+ALTER TABLE proc  ADD db_copy char(64) DEFAULT '' NOT NULL;
+ALTER TABLE proc  ADD INDEX non_unique(db_copy, name, type);
 
 # Activate the new, possible modified privilege tables
 # This should not be needed, but gives us some extra testing that the above

--- 1.5/mysql-test/r/sp-destruct.result	2006-08-01 16:45:35 +02:00
+++ 1.6/mysql-test/r/sp-destruct.result	2006-08-01 16:45:35 +02:00
@@ -11,11 +11,11 @@
 create trigger t1_ai after insert on t1 for each row call bug14233();
 alter table mysql.proc drop type;
 call bug14233();
-ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5)
+ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -2)
 create view v1 as select bug14233_f();
-ERROR HY000: Failed to load routine test.bug14233_f. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5)
+ERROR HY000: Failed to load routine test.bug14233_f. The table mysql.proc is missing, corrupt, or contains bad data (internal code -2)
 insert into t1 values (0);
-ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5)
+ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -2)
 flush table mysql.proc;
 call bug14233();
 ERROR HY000: Incorrect information in file: './mysql/proc.frm'
@@ -37,26 +37,26 @@
 (
 db, name, type, specific_name, language, sql_data_access, is_deterministic,
 security_type, param_list, returns, body, definer, created, modified,
-sql_mode, comment
+sql_mode, comment, db_copy
 )
 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', '', ''
+'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 ),
 (
 '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', '', ''
+'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 ),
 (
 'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO',
 'DEFINER', '', '',
 'alksj wpsj sa ^#!@ ',
-'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
+'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 );
 select bug14233_1();
 ERROR HY000: Failed to load routine test.bug14233_1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

--- 1.6/mysql-test/t/sp-destruct.test	2006-08-01 16:45:35 +02:00
+++ 1.7/mysql-test/t/sp-destruct.test	2006-08-01 16:45:35 +02:00
@@ -32,7 +32,6 @@
 
 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
@@ -82,26 +81,26 @@
 (
   db, name, type, specific_name, language, sql_data_access, is_deterministic,
   security_type, param_list, returns, body, definer, created, modified,
-  sql_mode, comment
+  sql_mode, comment, db_copy
 )
 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', '', ''
+  'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 ),
 (
   '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', '', ''
+  'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 ),
 (
   'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO',
   'DEFINER', '', '',
   'alksj wpsj sa ^#!@ ',
-  'root@localhost', NOW() , '0000-00-00 00:00:00', '', ''
+  'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', 'test'
 );
 
 --error ER_SP_PROC_TABLE_CORRUPT

--- 1.113/sql/sp.cc	2006-08-01 16:45:35 +02:00
+++ 1.114/sql/sp.cc	2006-08-01 16:45:35 +02:00
@@ -63,6 +63,7 @@
   MYSQL_PROC_FIELD_MODIFIED,
   MYSQL_PROC_FIELD_SQL_MODE,
   MYSQL_PROC_FIELD_COMMENT,
+  MYSQL_PROC_FIELD_DB_COPY,
   MYSQL_PROC_FIELD_COUNT
 };
 
@@ -117,7 +118,7 @@
   TABLE_LIST tables;
   TABLE *table;
   bool not_used;
-  DBUG_ENTER("open_proc_table");
+  DBUG_ENTER("open_proc_table_for_read");
 
   /*
     Speed up things if mysql.proc doesn't exists. mysql_proc_table_exists
@@ -136,21 +137,25 @@
   {
     thd->restore_backup_open_tables_state(backup);
     mysql_proc_table_exists= 0;
-    DBUG_RETURN(0);
+    DBUG_PRINT("info", ("Returning NULL"))
+    DBUG_RETURN(NULL);
   }
 
   DBUG_ASSERT(table->s->system_table);
 
   table->reginfo.lock_type= TL_READ;
+
   /*
     We have to ensure we are not blocked by a flush tables, as this
     could lead to a deadlock if we have other tables opened.
   */
-  if (!(thd->lock= mysql_lock_tables(thd, &table, 1,
+  if ((table->s->fields != MYSQL_PROC_FIELD_COUNT) ||
+      !(thd->lock= mysql_lock_tables(thd, &table, 1,
                                      MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
   {
     close_proc_table(thd, backup);
-    DBUG_RETURN(0);
+    DBUG_PRINT("info", ("Returning NULL"))
+    DBUG_RETURN(NULL);
   }
   DBUG_RETURN(table);
 }
@@ -215,7 +220,10 @@
 static int
 db_find_routine_aux(THD *thd, int type, sp_name *name, TABLE *table)
 {
-  byte key[MAX_KEY_LENGTH];	// db, name, optional key length type
+  uint db_field= MYSQL_PROC_FIELD_DB;
+  KEY *key_info;
+  uint index_num= 0;
+  byte key[MAX_KEY_LENGTH];	// db|db_copy, name, optional key length type
   DBUG_ENTER("db_find_routine_aux");
   DBUG_PRINT("enter", ("type: %d name: %.*s",
 		       type, name->m_name.length, name->m_name.str));
@@ -229,18 +237,29 @@
   */
   if (name->m_name.length > table->field[1]->field_length)
     DBUG_RETURN(SP_KEY_NOT_FOUND);
-  table->field[0]->store(name->m_db.str, name->m_db.length, &my_charset_bin);
-  table->field[1]->store(name->m_name.str, name->m_name.length,
-                         &my_charset_bin);
-  table->field[2]->store((longlong) type, TRUE);
-  key_copy(key, table->record[0], table->key_info,
-           table->key_info->key_length);
 
-  if (table->file->index_read_idx(table->record[0], 0,
-				  key, table->key_info->key_length,
+  if (lower_case_table_names == 2)
+  {
+    db_field= MYSQL_PROC_FIELD_DB_COPY;
+    index_num= 1;
+  }
+  key_info= table->key_info + index_num;
+
+  table->field[db_field]->store(name->m_db.str, name->m_db.length,
+                                &my_charset_bin);
+  
+  table->field[MYSQL_PROC_FIELD_NAME]->
+                store(name->m_name.str, name->m_name.length, &my_charset_bin);
+  table->field[MYSQL_PROC_FIELD_TYPE]->store((longlong) type, TRUE);
+
+  key_copy(key, table->record[0], key_info, key_info->key_length);
+
+  if (table->file->index_read_idx(table->record[0], index_num, key,
+                                  key_info->key_length,
 				  HA_READ_KEY_EXACT))
     DBUG_RETURN(SP_KEY_NOT_FOUND);
 
+  DBUG_PRINT("info", ("Routine found!"));
   DBUG_RETURN(SP_OK);
 }
 
@@ -283,8 +302,9 @@
   ulong sql_mode;
   Open_tables_state open_tables_state_backup;
   DBUG_ENTER("db_find_routine");
-  DBUG_PRINT("enter", ("type: %d name: %.*s",
-		       type, name->m_name.length, name->m_name.str));
+  DBUG_PRINT("enter", ("type: %d db: %.*s name: %.*s",
+		       type, name->m_db.length, name->m_db.str,
+                       name->m_name.length, name->m_name.str));
 
   *sphp= 0;                                     // In case of errors
   if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
@@ -293,11 +313,6 @@
   if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
     goto done;
 
-  if (table->s->fields != MYSQL_PROC_FIELD_COUNT)
-  {
-    ret= SP_GET_FIELD_FAILED;
-    goto done;
-  }
 
   bzero((char *)&chistics, sizeof(chistics));
   if ((ptr= get_field(thd->mem_root,
@@ -420,6 +435,7 @@
                                          HOSTNAME_LENGTH);
   
   int ret;
+  DBUG_ENTER("db_load_routine");
 
   thd->variables.sql_mode= sql_mode;
   thd->variables.select_limit= HA_POS_ERROR;
@@ -433,6 +449,21 @@
 
   defstr.set_charset(system_charset_info);
 
+  if (lower_case_table_names)
+  {
+    /*
+      valgrind error when accessing one byte after the end of m_name
+      Hence, we have to copy it to a buffer, zero terminate, convert to
+      lowercase and copy it back.
+    */
+    char tmp_str[NAME_LEN * 3 + 1];
+    memcpy(tmp_str, name->m_name.str, name->m_name.length);
+    tmp_str[name->m_name.length]= '\0';
+    DBUG_PRINT("info", ("lowercasing procedure's name"));    
+    my_casedn_str(system_charset_info, tmp_str);
+    memcpy(name->m_name.str, tmp_str, name->m_name.length);
+  }
+  
   /*
     We have to add DEFINER clause and provide proper routine characterstics in
     routine definition statement that we build here to be able to use this
@@ -450,7 +481,7 @@
     ret= SP_INTERNAL_ERROR;
     goto end;
   }
-
+  DBUG_PRINT("info", ("%.*s", defstr.length(), defstr.c_ptr()));
   if ((ret= sp_use_new_db(thd, name->m_db, &old_db, 1, &dbchanged)))
     goto end;
 
@@ -481,7 +512,7 @@
   thd->variables.sql_mode= old_sql_mode;
   thd->variables.select_limit= old_select_limit;
   thd->lex= old_lex;
-  return ret;
+  DBUG_RETURN(ret);
 }
 
 
@@ -498,8 +529,8 @@
   delete field;
 }
 
-static int
-db_create_routine(THD *thd, int type, sp_head *sp)
+int
+sp_db_create_routine(THD *thd, int type, sp_head *sp)
 {
   int ret;
   TABLE *table;
@@ -507,7 +538,7 @@
   char old_db_buf[NAME_LEN+1];
   LEX_STRING old_db= { old_db_buf, sizeof(old_db_buf) };
   bool dbchanged;
-  DBUG_ENTER("db_create_routine");
+  DBUG_ENTER("sp_db_create_routine");
   DBUG_PRINT("enter", ("type: %d name: %.*s",type,sp->m_name.length,
                        sp->m_name.str));
 
@@ -527,12 +558,6 @@
     strxmov(definer, thd->lex->definer->user.str, "@",
             thd->lex->definer->host.str, NullS);
 
-    if (table->s->fields != MYSQL_PROC_FIELD_COUNT)
-    {
-      ret= SP_GET_FIELD_FAILED;
-      goto done;
-    }
-
     if (system_charset_info->cset->numchars(system_charset_info,
                                             sp->m_name.str,
                                             sp->m_name.str+sp->m_name.length) >
@@ -548,6 +573,8 @@
     }
     table->field[MYSQL_PROC_FIELD_DB]->
       store(sp->m_db.str, sp->m_db.length, system_charset_info);
+    table->field[MYSQL_PROC_FIELD_DB_COPY]->
+      store(sp->m_db.str, sp->m_db.length, system_charset_info);
     table->field[MYSQL_PROC_FIELD_NAME]->
       store(sp->m_name.str, sp->m_name.length, system_charset_info);
     table->field[MYSQL_PROC_FIELD_TYPE]->
@@ -679,7 +706,7 @@
     DBUG_RETURN(SP_OPEN_TABLE_FAILED);
   if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
   {
-    store_record(table,record[1]);
+    store_record(table, record[1]);
     table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
     ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
     if (chistics->suid != SP_IS_DEFAULT_SUID)
@@ -883,7 +910,11 @@
 int
 sp_drop_db_routines(THD *thd, char *db)
 {
-  TABLE *table;
+  uint db_field= MYSQL_PROC_FIELD_DB;
+  TABLE *table= NULL;
+  KEY *key_info;
+  byte *key_buf= NULL;
+  uint index_num= 0;
   int ret;
   uint key_len;
   DBUG_ENTER("sp_drop_db_routines");
@@ -893,14 +924,28 @@
   if (!(table= open_proc_table_for_update(thd)))
     goto err;
 
-  table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
-  key_len= table->key_info->key_part[0].store_length;
+  if (lower_case_table_names == 2)
+  {
+    db_field= MYSQL_PROC_FIELD_DB_COPY;
+    /* We need to use another index in this case */
+    index_num= 1;
+  }
+  key_info= table->key_info + index_num;
+
+  table->field[db_field]->store(db, strlen(db), system_charset_info);
+  key_len= key_info->key_part[0].store_length;
+
+  if (!(key_buf= (byte *)my_malloc(key_len, MYF(MY_WME))))
+  {
+    ret= 1;
+    goto err;
+  } 
+  key_copy(key_buf, table->record[0], key_info, key_len);
 
   ret= SP_OK;
-  table->file->ha_index_init(0);
-  if (! table->file->index_read(table->record[0],
-                                (byte *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
-				key_len, HA_READ_KEY_EXACT))
+  table->file->ha_index_init(index_num);
+  if (!table->file->index_read(table->record[0], key_buf, key_len,
+                                HA_READ_PREFIX))
   {
     int nxtres;
     bool deleted= FALSE;
@@ -915,8 +960,7 @@
 	nxtres= 0;
 	break;
       }
-    } while (! (nxtres= table->file->index_next_same(table->record[0],
-                                (byte *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
+    } while (!(nxtres= table->file->index_next_same(table->record[0], key_buf,
 						     key_len)));
     if (nxtres != HA_ERR_END_OF_FILE)
       ret= SP_KEY_NOT_FOUND;
@@ -925,9 +969,12 @@
   }
   table->file->ha_index_end();
 
-  close_thread_tables(thd);
-
 err:
+  if (key_buf)
+    my_free(key_buf, MYF(0));
+  if (table)
+    close_thread_tables(thd);
+
   DBUG_RETURN(ret);
 }
 
@@ -1118,18 +1165,6 @@
 
 
 int
-sp_create_procedure(THD *thd, sp_head *sp)
-{
-  int ret;
-  DBUG_ENTER("sp_create_procedure");
-  DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str));
-
-  ret= db_create_routine(thd, TYPE_ENUM_PROCEDURE, sp);
-  DBUG_RETURN(ret);
-}
-
-
-int
 sp_drop_procedure(THD *thd, sp_name *name)
 {
   int ret;
@@ -1166,11 +1201,7 @@
 
   if ((sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name,
                            &thd->sp_proc_cache, FALSE)))
-  {
-    int ret= sp->show_create_procedure(thd);
-
-    DBUG_RETURN(ret);
-  }
+    DBUG_RETURN(sp->show_create_procedure(thd))
 
   DBUG_RETURN(SP_KEY_NOT_FOUND);
 }
@@ -1192,18 +1223,6 @@
 ******************************************************************************/
 
 int
-sp_create_function(THD *thd, sp_head *sp)
-{
-  int ret;
-  DBUG_ENTER("sp_create_function");
-  DBUG_PRINT("enter", ("name: %.*s", sp->m_name.length, sp->m_name.str));
-
-  ret= db_create_routine(thd, TYPE_ENUM_FUNCTION, sp);
-  DBUG_RETURN(ret);
-}
-
-
-int
 sp_drop_function(THD *thd, sp_name *name)
 {
   int ret;
@@ -1240,11 +1259,8 @@
 
   if ((sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name,
                            &thd->sp_func_cache, FALSE)))
-  {
-    int ret= sp->show_create_function(thd);
+    DBUG_RETURN(sp->show_create_function(thd))
 
-    DBUG_RETURN(ret);
-  }
   DBUG_RETURN(SP_KEY_NOT_FOUND);
 }
 
@@ -1561,11 +1577,13 @@
     sp_name name(rt->key.str, rt->key.length);
     int type= rt->key.str[0];
     sp_head *sp;
+    DBUG_PRINT("info", ("Is %s into the cache", name.m_name.str));
 
     if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
                               &thd->sp_func_cache : &thd->sp_proc_cache),
                               &name)))
     {
+      DBUG_PRINT("info", ("Loading into the cache", name.m_name.str));
       name.m_name.str= strchr(name.m_qname.str, '.');
       name.m_db.length= name.m_name.str - name.m_qname.str;
       name.m_db.str= strmake_root(thd->mem_root, name.m_qname.str,
@@ -1577,6 +1595,7 @@
       {
       case SP_OK:
         {
+          DBUG_PRINT("info", ("SP_OK, caching"));
           if (type == TYPE_ENUM_FUNCTION)
             sp_cache_insert(&thd->sp_func_cache, sp);
           else
@@ -1584,6 +1603,7 @@
         }
         break;
       case SP_KEY_NOT_FOUND:
+        DBUG_PRINT("info", ("Not Found!"));
         ret= SP_OK;
         break;
       case SP_OPEN_TABLE_FAILED:

--- 1.34/sql/sp.h	2006-08-01 16:45:35 +02:00
+++ 1.35/sql/sp.h	2006-08-01 16:45:35 +02:00
@@ -46,12 +46,11 @@
 sp_routine_exists_in_table(THD *thd, int type, sp_name *name);
 
 int
-sp_create_procedure(THD *thd, sp_head *sp);
+sp_db_create_routine(THD *thd, int type, sp_head *sp);
 
 int
 sp_drop_procedure(THD *thd, sp_name *name);
 
-
 int
 sp_update_procedure(THD *thd, sp_name *name, st_sp_chistics *chistics);
 
@@ -60,9 +59,6 @@
 
 int
 sp_show_status_procedure(THD *thd, const char *wild);
-
-int
-sp_create_function(THD *thd, sp_head *sp);
 
 int
 sp_drop_function(THD *thd, sp_name *name);

--- 1.15/sql/sp_cache.cc	2006-08-01 16:45:35 +02:00
+++ 1.16/sql/sp_cache.cc	2006-08-01 16:45:35 +02:00
@@ -40,12 +40,30 @@
   inline void insert(sp_head *sp)
   {
     /* TODO: why don't we check return value? */
+    DBUG_ENTER("sp_cache::insert");
+    DBUG_PRINT("enter", ("qname=%.*s", sp->m_qname.length, sp->m_qname.str));
     my_hash_insert(&m_hashtable, (const byte *)sp);
+    DBUG_VOID_RETURN;
   }
 
-  inline sp_head *lookup(char *name, uint namelen)
+  inline sp_head *lookup(LEX_STRING name)
   {
-    return (sp_head *)hash_search(&m_hashtable, (const byte *)name, namelen);
+    DBUG_ENTER("sp_cache::lookup");
+    DBUG_PRINT("enter", ("name=%.*s elements=%d lower_case=%d", name.length,
+               name.str, m_hashtable.records));
+    if (lower_case_table_names)
+    {
+      char n[NAME_LEN*3*2+2];                     // 3 bytes per char in utf8
+      DBUG_PRINT("info", ("Lowercasing"));
+
+      memcpy(n, name.str, name.length);
+      n[name.length]= '\0';
+      my_casedn_str(system_charset_info, n);
+      DBUG_RETURN((sp_head *)hash_search(&m_hashtable, (const byte *)n,
+                                         name.length));
+    }
+    DBUG_RETURN((sp_head *)hash_search(&m_hashtable, (const byte *)name.str,
+                                       name.length));
   }
 
 #ifdef NOT_USED
@@ -160,7 +178,7 @@
   sp_cache *c= *cp;
   if (! c)
     return NULL;
-  return c->lookup(name->m_qname.str, name->m_qname.length);
+  return c->lookup(name->m_qname);
 }
 
 
@@ -219,9 +237,11 @@
 static byte *hash_get_key_for_sp_head(const byte *ptr, uint *plen,
 			       my_bool first)
 {
+  DBUG_ENTER("hash_get_key_for_sp_head");
   sp_head *sp= (sp_head *)ptr;
+  DBUG_PRINT("enter", ("%.*s", sp->m_qname.length, sp->m_qname.str));
   *plen= sp->m_qname.length;
-  return (byte*) sp->m_qname.str;
+  DBUG_RETURN((byte*) sp->m_qname.str);
 }
 
 
@@ -248,7 +268,7 @@
 void
 sp_cache::init()
 {
-  hash_init(&m_hashtable, system_charset_info, 0, 0, 0,
+  hash_init(&m_hashtable, &my_charset_bin, 0, 0, 0,
 	    hash_get_key_for_sp_head, hash_free_sp_head, 0);
   version= 0;
 }

--- 1.219/sql/sp_head.cc	2006-08-01 16:45:35 +02:00
+++ 1.220/sql/sp_head.cc	2006-08-01 16:45:35 +02:00
@@ -358,6 +358,23 @@
 
 
 /*
+  Constructor of class sp_name
+
+  SYNOPSIS
+    sp_name::sp_name(LEX_STRING db, LEX_STRING name)
+      db    The database
+      name  The name
+*/
+
+sp_name::sp_name(LEX_STRING db, LEX_STRING name)
+  : m_db(db), m_name(name)
+{
+  m_qname.str= m_sroutines_key.str= 0;
+  m_qname.length= m_sroutines_key.length= 0;
+}
+
+
+/*
  *
  *  sp_name
  *
@@ -608,10 +625,7 @@
   }
 #endif
 
-  if (m_type == TYPE_ENUM_FUNCTION)
-    ret= sp_create_function(thd, this);
-  else
-    ret= sp_create_procedure(thd, this);
+  ret= sp_db_create_routine(thd, m_type, this);
 
   DBUG_RETURN(ret);
 }

--- 1.86/sql/sp_head.h	2006-08-01 16:45:35 +02:00
+++ 1.87/sql/sp_head.h	2006-08-01 16:45:35 +02:00
@@ -61,12 +61,7 @@
   */
   LEX_STRING m_sroutines_key;
 
-  sp_name(LEX_STRING db, LEX_STRING name)
-    : m_db(db), m_name(name)
-  {
-    m_qname.str= m_sroutines_key.str= 0;
-    m_qname.length= m_sroutines_key.length= 0;
-  }
+  sp_name(LEX_STRING db, LEX_STRING name);
 
   /*
     Creates temporary sp_name object from key, used mainly

--- 1.227/BitKeeper/etc/ignore	2006-08-01 16:45:35 +02:00
+++ 1.228/BitKeeper/etc/ignore	2006-08-01 16:45:35 +02:00
@@ -1296,3 +1296,4 @@
 vio/viotest.cpp
 zlib/*.ds?
 zlib/*.vcproj
+extra/yassl/taocrypt/src/libtaocrypt_la-integer.loT

--- 1.28/scripts/mysql_create_system_tables.sh	2006-08-01 16:45:35 +02:00
+++ 1.29/scripts/mysql_create_system_tables.sh	2006-08-01 16:45:35 +02:00
@@ -720,7 +720,9 @@
   c_p="$c_p                         'HIGH_NOT_PRECEDENCE'"
   c_p="$c_p                     ) DEFAULT '' NOT NULL,"
   c_p="$c_p   comment           char(64) collate utf8_bin DEFAULT '' NOT NULL,"
+  c_p="$c_p   db_copy           char(64) DEFAULT '' NOT NULL,"
   c_p="$c_p   PRIMARY KEY (db,name,type)"
+  c_p="$c_p   INDEX non_unique(db_copy, name, type)"
   c_p="$c_p ) engine=MyISAM"
   c_p="$c_p character set utf8"
   c_p="$c_p comment='Stored Procedures';"
Thread
bk commit into 5.0 tree (andrey:1.2238) BUG#9051ahristov1 Aug