MySQL Bugs: #8072: Procedure without handler for Warning, implicit switch of default database

Bug #8072	Procedure without handler for Warning, implicit switch of default database
Submitted:	21 Jan 2005 16:28	Modified:	3 Aug 2005 6:53
Reporter:	Matthias Leich	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have a test case derived from Trudy's nice SP tests which shows a dangerous bug.
CREATE DATABASE testdb;
delimiter //;
USE testdb//
create procedure p1 () begin
drop table if exists t1;
select 1 as "my-col";
end;//
SHOW TABLES//
Tables_in_testdb
                 <--- empty result set like expected
call p1()//
                 <--- I miss here the warning, that t1 does not exists, but that's not
my-col               so dangerous.
1
SHOW TABLES//
Tables_in_test
                  <--- Attention, my default database was "testdb" just before call p1 !!
That means every ALTER/DROP/INSERT/DELETE/UPDATE with a table name
not preceded with the database name will now hit into the wrong database !

Please have a look into the attached test cases with comments and some test
variations which might give useful additional informations. It is very simple, 
produces the boring "Packets out of order" bug and might help to fix some other 
open and much more complicated SP related bugs having the same reason.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 last Changeset around Jan 20

How to repeat:
Please use my test file ML19.test , copy it to mysql-test/t
  ./mysql-test-run ML19

test case

Attachment: ML19.test (application/test, text), 5.02 KiB.

created testdbtable  in testdb database, testtable in test database and run:

mysql> delimiter //;
mysql> USE testdb//
Database changed
mysql> create procedure p1 () begin
    -> drop table if exists t1;
    -> select 1 as "my-col";
    -> end;//
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES//
+------------------+
| Tables_in_testdb |
+------------------+
| testdbtable      |
+------------------+
1 row in set (0.00 sec)

mysql> call p1()//
+--------+
| my-col |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW TABLES//
+------------------+
| Tables_in_testdb |
+------------------+
| testdbtable      |
+------------------+
1 row in set (0.01 sec)

mysql> 

So I guess we need to find condition when this error appears. It doesn't appear in my environment   - slacware 10 kernel 2.6.7
mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.3-alpha-debug |
+-------------------+
1 row in set (0.00 sec)

I made my test with the tool "mysqltest". That's why I tried the same test like you
with the tool "mysql" and the bug disappeared.

Please reopen the bug. I can be repeated with "mysqltest".
That means there is something wrong within the communication between 
mysqltest and the server. And mysqltest is the main regression test tool.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.04 sec)

mysql> delimiter //;
mysql> USE testdb//
Database changed
mysql> create procedure p1 () begin
    -> drop table if exists t1;
    -> select 1 as "my-col";
    -> end;//
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES//
Empty set (0.01 sec)

mysql> call p1()//
+--------+
| my-col |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings//
+-------+------+--------------------+
| Level | Code | Message            |
+-------+------+--------------------+
| Note  | 1051 | Unknown table 't1' |
+-------+------+--------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES//
Empty set (0.01 sec)

mysql> call p1()//
+--------+
| my-col |
+--------+
|      1 |
+--------+
1 row in set, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

SP Locking 
==========
1. For PROCEDUREs, don't do any prelocking. Every executed statement
   sets/removes its own locks.

2. For FUNCTIONs/TRIGGERs, keep doing prelocking. 
   Non-temporary table creation/drop is forbidden.
   This rule also applies for procedures called from functions/triggers.

"True" cursors will need keep tables open, and hence may cause deadlocks.
The same applies to cursors when they are used in prepared statements. 
The solution will be to time out (Kostja's working on it).

SP replication
==============

If SPs don't do table prelocking, replicating CALL statements may cause
slaves to desynchronize. 
The solution is to replicate statements SP executes. 

Solution sketch: 
When SP is executed for every data-modifying statement detect all environment
it needs to be repeated on the slaves (the usual replication stuff + SP local
variables), and replicate everything.

Current proposal {
  ON MASTER:
    Add hooks to SP local variable uses. This will allow us to catch them.
    Then save set-of({sp_var_name, value}) to binlog.

  ON SLAVE:
    "Set" SP variables.
    Activate special parser mode so we allow SP variable refs 
    (TODO how exactly?)
    Execute the query.

  mysqlbinlog can do the same as slave does.

  In summary we'll need the following parser enhancements:
  1. Add some flag to SET/DECLARE so it can be parsed/executed outside of SP.
  2. Modify All data-modifying queries so they allow to "use SP variables 
     outside any SP"

}

Alternate proposal1 {
  Similar to current, but let MASTER replace SP variable references with user 
  variables references (taking care about name clashes).
  No changes needed on slave/mysqlbinlog side.
}

Alternate proposal2 {
  Let MASTER produce something like
  "
    CREATE PROCEDURE REPL_XXXX
      BEGIN SET spvar1=y, ...;  {data-modify-stmt-here} 
    END;
    CALL REPL_XXXX; 
    DROP PROCEDURE REPL_XXXX;
  "

  No changes needed on slave/mysqlbinlog side.
}

See BUG#11126