List:Commits« Previous MessageNext Message »
From:konstantin Date:July 4 2006 7:26pm
Subject:bk commit into 5.0 tree (konstantin:1.2214) BUG#17843
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja 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.2214 06/07/04 23:26:01 konstantin@stripped +8 -0
  A fix and a test case for Bug#17843 "Certain stored procedures fail to 
  run at startup"
  
  The server returned an error when trying to execute a 
  stored procedure that could return multiple result sets to the client
  from an init-file. A stored procedure can return multiple result
  sets if it contains PREPARE, SELECT, SHOW and similar statements.
     
  The fix is to set client_capabilites|=CLIENT_MULTI_RESULTS in
  sql_parse.cc:handle_bootstrap(). There is no "client" really, so 
  nothing is ever sent. This init-file feature behave consistently: the
  prepared statements that can be called directly in the init-file can be
  used in a stored procedure too.
  
  Re-committed the patch originally submitted by Per-Erik after review.

  sql/sql_parse.cc
    1.561 06/07/04 23:25:56 konstantin@stripped +6 -0
    Set CLIENT_MULTI_RESULTS in handle_bootstrap(), to make prepared
    statements work in stored procedures called from init-file.

  sql/sql_class.cc
    1.237 06/07/04 23:25:56 konstantin@stripped +1 -0
    Initialize Security_context::priv_host to empty string:
    when executing an init-file, sql_parse.cc:get_default_definer()
    will use this for the value of the definer if it's not set in the query.

  mysql-test/t/init_file.test
    1.8 06/07/04 23:25:56 konstantin@stripped +12 -2
    Add test coverage for new features added in 5.0 --
    stred routines, views, triggers. The actual tests are in 
    std_data/init_file.dat, here we just check the results, and clean up.

  mysql-test/t/init_connect.test
    1.7 06/07/04 23:25:56 konstantin@stripped +202 -1
    Add test coverage for new features added in 5.0.

  mysql-test/std_data/init_file.dat
    1.6 06/07/04 23:25:56 konstantin@stripped +28 -0
    Add test coverage for new features added in 5.0. Note, that what can 
    be done in init_file is very limited as it does not any other
    delimiter except ';' -- only "one liners" and no multiple statement 
    procedures. Also, this is executed with a dummy user "boot@", which 
    calls for the use of DEFINER clause.

  mysql-test/r/init_file.result
    1.2 06/07/04 23:25:56 konstantin@stripped +15 -0
    Updated results (a test case for Bug#17843)

  mysql-test/r/init_connect.result
    1.5 06/07/04 23:25:55 konstantin@stripped +114 -0
    Updated results (a test case for Bug#17843)

  mysql-test/Makefile.am
    1.70 06/07/04 23:25:55 konstantin@stripped +5 -5
    Fix re-make without make clean.

# 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:	konstantin
# Host:	bodhi.netgear
# Root:	/opt/local/work/mysql-5.0-17843

--- 1.236/sql/sql_class.cc	2006-06-21 02:21:07 +04:00
+++ 1.237/sql/sql_class.cc	2006-07-04 23:25:56 +04:00
@@ -1946,6 +1946,7 @@
 {
   host= user= priv_user= ip= 0;
   host_or_ip= "connecting host";
+  priv_host[0]= '\0';
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   db_access= NO_ACCESS;
 #endif

--- 1.560/sql/sql_parse.cc	2006-07-04 01:13:00 +04:00
+++ 1.561/sql/sql_parse.cc	2006-07-04 23:25:56 +04:00
@@ -1250,6 +1250,12 @@
   thd->version=refresh_version;
   thd->security_ctx->priv_user=
     thd->security_ctx->user= (char*) my_strdup("boot", MYF(MY_WME));
+  /*
+    Make the "client" handle multiple results. This is necessary
+    to enable stored procedures with SELECTs and Dynamic SQL
+    in init-file.
+  */
+  thd->client_capabilities|= CLIENT_MULTI_RESULTS;
 
   buff= (char*) thd->net.buff;
   thd->init_for_queries();

--- 1.4/mysql-test/r/init_connect.result	2004-12-16 13:41:47 +03:00
+++ 1.5/mysql-test/r/init_connect.result	2006-07-04 23:25:55 +04:00
@@ -22,3 +22,117 @@
 select @a;
 Got one of the listed errors
 drop table t1;
+End of 4.1 tests
+create table t1 (x int);
+insert into t1 values (3), (5), (7);
+create table t2 (y int);
+create user mysqltest1@localhost;
+grant all privileges on test.* to mysqltest1@localhost;
+set global init_connect="create procedure p1() select * from t1";
+call p1();
+x
+3
+5
+7
+drop procedure p1;
+set global init_connect="create procedure p1(x int)\
+begin\
+  select count(*) from t1;\
+  select * from t1;\
+  set @x = x;
+end";
+call p1(42);
+count(*)
+3
+x
+3
+5
+7
+select @x;
+@x
+42
+set global init_connect="call p1(4711)";
+select @x;
+@x
+4711
+set global init_connect="drop procedure if exists p1";
+call p1();
+ERROR 42000: PROCEDURE test.p1 does not exist
+create procedure p1(out sum int)
+begin
+declare n int default 0;
+declare c cursor for select * from t1;
+declare exit handler for not found
+begin
+close c;
+set sum = n;
+end;
+open c;
+loop
+begin
+declare x int;
+fetch c into x;
+if x > 3 then
+set n = n + x;
+end if;
+end;
+end loop;
+end|
+set global init_connect="call p1(@sum)";
+select @sum;
+@sum
+12
+drop procedure p1;
+create procedure p1(tbl char(10), v int)
+begin
+set @s = concat('insert into ', tbl, ' values (?)');
+set @v = v;
+prepare stmt1 from @s;
+execute stmt1 using @v;
+deallocate prepare stmt1;
+end|
+set global init_connect="call p1('t1', 11)";
+select * from t1;
+x
+3
+5
+7
+11
+drop procedure p1;
+create function f1() returns int
+begin
+declare n int;
+select count(*) into n from t1;
+return n;
+end|
+set global init_connect="set @x = f1()";
+select @x;
+@x
+4
+set global init_connect="create view v1 as select f1()";
+select * from v1;
+f1()
+4
+set global init_connect="drop view v1";
+select * from v1;
+ERROR 42S02: Table 'test.v1' doesn't exist
+drop function f1;
+create trigger trg1
+after insert on t2
+for each row
+insert into t1 values (new.y);
+set global init_connect="insert into t2 values (13), (17), (19)";
+select * from t1;
+x
+3
+5
+7
+11
+13
+17
+19
+drop trigger trg1;
+set global init_connect=default;
+revoke all privileges, grant option from mysqltest1@localhost;
+drop user mysqltest1@localhost;
+drop table t1, t2;

--- 1.6/mysql-test/t/init_connect.test	2006-05-17 17:00:57 +04:00
+++ 1.7/mysql-test/t/init_connect.test	2006-07-04 23:25:56 +04:00
@@ -35,4 +35,205 @@
 connection con0;
 drop table t1;
 
-# End of 4.1 tests
+disconnect con1;
+disconnect con2;
+disconnect con3;
+disconnect con4;
+disconnect con5;
+
+--echo End of 4.1 tests
+#
+# Test 5.* features
+#
+
+create table t1 (x int);
+insert into t1 values (3), (5), (7);
+create table t2 (y int);
+
+create user mysqltest1@localhost;
+grant all privileges on test.* to mysqltest1@localhost;
+#
+# Create a simple procedure
+#
+set global init_connect="create procedure p1() select * from t1";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+call p1();
+drop procedure p1;
+
+connection con0;
+disconnect con1;
+#
+# Create a multi-result set procedure
+#
+set global init_connect="create procedure p1(x int)\
+begin\
+  select count(*) from t1;\
+  select * from t1;\
+  set @x = x;
+end";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+call p1(42);
+select @x;
+
+connection con0;
+disconnect con1;
+#
+# Just call it - this will not generate any output
+#
+set global init_connect="call p1(4711)";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select @x;
+
+connection con0;
+disconnect con1;
+#
+# Drop the procedure
+#
+set global init_connect="drop procedure if exists p1";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+--error ER_SP_DOES_NOT_EXIST
+call p1();
+
+connection con0;
+disconnect con1;
+#
+# Execution of a more complex procedure
+#
+delimiter |;
+create procedure p1(out sum int)
+begin
+  declare n int default 0;
+  declare c cursor for select * from t1;
+  declare exit handler for not found
+    begin
+      close c;
+      set sum = n;
+    end;
+
+  open c;
+  loop
+    begin
+      declare x int;
+
+      fetch c into x;
+      if x > 3 then
+        set n = n + x;
+      end if;
+    end;
+  end loop;
+end|
+delimiter ;|
+# Call the procedure with a cursor
+set global init_connect="call p1(@sum)";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select @sum;
+
+connection con0;
+disconnect con1;
+drop procedure p1;
+#
+# Test Dynamic SQL
+#
+delimiter |;
+create procedure p1(tbl char(10), v int)
+begin
+  set @s = concat('insert into ', tbl, ' values (?)');
+  set @v = v;
+  prepare stmt1 from @s;
+  execute stmt1 using @v;
+  deallocate prepare stmt1;
+end|
+delimiter ;|
+# Call the procedure with prepared statements
+set global init_connect="call p1('t1', 11)";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select * from t1;
+
+connection con0;
+disconnect con1;
+drop procedure p1;
+#
+# Stored functions
+#
+delimiter |;
+create function f1() returns int
+begin
+  declare n int;
+
+  select count(*) into n from t1;
+  return n;
+end|
+delimiter ;|
+# Invoke a function
+set global init_connect="set @x = f1()";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select @x;
+
+connection con0;
+disconnect con1;
+#
+# Create a view
+#
+set global init_connect="create view v1 as select f1()";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select * from v1;
+
+connection con0;
+disconnect con1;
+#
+# Drop the view
+#
+set global init_connect="drop view v1";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+--error ER_NO_SUCH_TABLE
+select * from v1;
+
+connection con0;
+disconnect con1;
+drop function f1;
+
+# We can't test "create trigger", since this requires super privileges
+# in 5.0, but with super privileges, init_connect is not executed.
+# (However, this can be tested in 5.1)
+#
+#set global init_connect="create trigger trg1\
+#  after insert on t2\
+#  for each row\
+#  insert into t1 values (new.y)";
+#connect (con1,localhost,mysqltest1,,);
+#connection con1;
+#insert into t2 values (2), (4);
+#select * from t1;
+#
+#connection con0;
+#disconnect con1;
+
+create trigger trg1
+  after insert on t2
+  for each row
+  insert into t1 values (new.y);
+
+# Invoke trigger
+set global init_connect="insert into t2 values (13), (17), (19)";
+connect (con1,localhost,mysqltest1,,);
+connection con1;
+select * from t1;
+
+connection con0;
+disconnect con1;
+
+drop trigger trg1;
+set global init_connect=default;
+
+revoke all privileges, grant option from mysqltest1@localhost;
+drop user mysqltest1@localhost;
+drop table t1, t2;

--- 1.1/mysql-test/r/init_file.result	2005-12-06 23:28:07 +03:00
+++ 1.2/mysql-test/r/init_file.result	2006-07-04 23:25:56 +04:00
@@ -1 +1,16 @@
 ok
+end of 4.1 tests
+select * from t1;
+x
+3
+5
+7
+11
+13
+select * from t2;
+y
+30
+3
+11
+13
+drop table t1, t2;

--- 1.69/mysql-test/Makefile.am	2006-05-03 14:05:00 +04:00
+++ 1.70/mysql-test/Makefile.am	2006-07-04 23:25:55 +04:00
@@ -101,15 +101,15 @@
 	@RM@ -f -r $(DESTDIR)$(testdir)
 
 std_data/client-key.pem: $(top_srcdir)/SSL/$(@F)
-	@CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
+	@CP@ -f $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
 std_data/client-cert.pem: $(top_srcdir)/SSL/$(@F)
-	@CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
+	@CP@ -f $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
 std_data/cacert.pem: $(top_srcdir)/SSL/$(@F)
-	@CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
+	@CP@ -f $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
 std_data/server-cert.pem: $(top_srcdir)/SSL/$(@F)
-	@CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
+	@CP@ -f $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
 std_data/server-key.pem: $(top_srcdir)/SSL/$(@F)
-	@CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
+	@CP@ -f $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data
 
 SUFFIXES = .sh
 

--- 1.5/mysql-test/std_data/init_file.dat	2005-10-17 19:08:52 +04:00
+++ 1.6/mysql-test/std_data/init_file.dat	2006-07-04 23:25:56 +04:00
@@ -1 +1,29 @@
 select * from mysql.user as t1, mysql.user as t2, mysql.user as t3;
+use test;
+
+drop table if exists t1;
+create table t1 (x int);
+drop table if exists t2;
+create table t2 (y int);
+
+drop procedure if exists p1;
+create definer=root@localhost procedure p1() select * from t1;
+call p1();
+drop procedure p1;
+
+create definer=root@localhost procedure p1() insert into t1 values (3),(5),(7);
+call p1();
+
+drop function if exists f1;
+create definer=root@localhost function f1() returns int return (select count(*) from t1);
+insert into t2 set y = f1()*10;
+
+drop view if exists v1;
+create definer=root@localhost view v1 as select f1();
+insert into t2 (y) select * from v1;
+
+create trigger trg1 after insert on t2 for each row insert into t1 values (new.y);
+insert into t2 values (11), (13);
+drop procedure p1;
+drop function f1;
+drop view v1;

--- 1.7/mysql-test/t/init_file.test	2005-12-06 23:28:06 +03:00
+++ 1.8/mysql-test/t/init_file.test	2006-07-04 23:25:56 +04:00
@@ -6,5 +6,15 @@
 # mysql-test/t/init_file-master.opt for the actual test
 # 
 
-# End of 4.1 tests
-echo ok;
+--echo ok
+--echo end of 4.1 tests
+#
+# Chec 5.x features
+#
+# Expected:
+#   3, 5, 7, 11, 13 
+select * from t1;
+# Expected:
+#   30, 3, 11, 13
+select * from t2;
+drop table t1, t2;
Thread
bk commit into 5.0 tree (konstantin:1.2214) BUG#17843konstantin4 Jul