MySQL Bugs: #17199: Problem when view calls function from another database.

Bug #17199	Problem when view calls function from another database.
Submitted:	7 Feb 2006 20:58	Modified:	7 Jul 2006 21:37
Reporter:	Kevin Regan
Status:	Closed
Category:	Server: General	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	Target Version:

Description:
When creating a view that calls a function from a different database, it seems that the
parsing fails (it looks like a string may be being used before it is initialized).

Here is an example:

drop database if exists a;
drop database if exists b;
create database a;
create database b;

use a;

DROP FUNCTION IF EXISTS func1;

delimiter |
CREATE FUNCTION func1 (name VARCHAR(127), value VARCHAR(128))
    RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN CONCAT(name, '---', value);
END;
|
delimiter ;

use b;

DROP TABLE IF EXISTS table1;
DROP VIEW IF EXISTS view1;

CREATE TABLE table1
(
   uid INT UNSIGNED PRIMARY KEY,
   name VARCHAR(128),
   value VARCHAR(128)
) Engine=InnoDB;

CREATE VIEW view1 AS
    SELECT *, a.func1(name, value) AS name_value FROM table1;

When attempting to insert the above file into the database, I get:

mysql -u... -p... < /tmp/example.sql
ERROR 1146 (42S02) at line 34: Table 'X�       �W     .table1' doesn't exist

The other thing to note is that the error message seems to be slightly different each
time, possibly pointing to some uninitialized variable:

ERROR 1146 (42S02) at line 34: Table '(�      �       .table1' doesn't exist
[regan@regan95 f5em]$ mysql -uroot -p4Dm1n < /home/regan/dev/test/view_example.sql
ERROR 1146 (42S02) at line 34: Table '��       �W     .table1' doesn't exist
[regan@regan95 f5em]$ mysql -uroot -p4Dm1n < /home/regan/dev/test/view_example.sql
ERROR 1146 (42S02) at line 34: Table '��      �       .table1' doesn't exist

Sincerely,
Kevin Regan

How to repeat:

Copying the above commands into a file and sending this to the mysq client.

One other thing to mention is that if you attempt this enough times, it will actually work
once in a while -- the view will be created without an error.  From my testing, it almost
seems like it is succeeding every 7th time that it is run, and failing all other times.

I've noticed two other things:

1) The problem seems to be transitive -- if you call a function from a view that calls a
function from another database, the problem still occurs.
2) I've tried the latest build (5.0.19-20060207), and the problem is still there. 
However, the command seems to succeed every other time (rather than once in every seven
tries).

The error from 5.0.19 seems to always have the same output:

$ /opt/mysql-5.0.19-20060207/bin/mysql -u... -p... <
/home/regan/dev/test/view_example.sql
ERROR 1146 (42S02) at line 34: Table 'HT�1F.table1' doesn't exist

Putting in simpler version and OS strings.

verified with latest 5.0bk, 5.1bk seems to be ok though

I've tracked down the problem to the following line of code (sql_db.cc:1166):

end:
  if (!(thd->slave_thread))
>>>>>>    x_free(thd->db);
  if (dbname && dbname[0] == 0)
  {

It seems that this string is shared by other data structures.  When it gets freed (to
change the database name), the other structures that have the old database string now
point to invalid memory.

Commenting out this x_free call "fixes" the problem -- mysqld can now parse the previous
example successfully.  However, I'm guessing that this will introduce a memory leak
(although, I haven't yet confirmed this).

I think I may need the help of more experienced mysql developers to determine the proper
solution to this.

The diff is below.  However, again, I'm not sure if this is the best long term solution. 
It may be better to allow this (possible) small memory leak to fix the parsing problem
until a better solution can be determined by a mysql developer:

--- mysql-5.0.18/sql/sql_db.cc  2006-02-08 12:01:54.000000000 -0800
+++ mysql-5.0.18/sql/sql_db.cc.new      2006-02-08 12:01:40.000000000 -0800
@@ -1163,7 +1163,7 @@
   }
 end:
   if (!(thd->slave_thread))
-    x_free(thd->db);
+      //    x_free(thd->db);
   if (dbname && dbname[0] == 0)
   {
     if (!(thd->slave_thread))

This does, indeed, cause the running executable to grow when making calls to other
database, so a better solution is needed.

This one seems to work without a memory leak:

--- mysql-5.0.18/sql/sql_parse.cc       2006-02-08 17:14:24.000000000 -0800
+++ mysql-5.0.18/sql/sql_parse.cc.new   2006-02-08 17:14:07.000000000 -0800
@@ -5934,7 +5934,7 @@
     ptr->db= empty_c_string;
     ptr->db_length= 0;
   }
-  if (thd->stmt_arena->is_stmt_prepare_or_first_sp_execute())
+  //  if (thd->stmt_arena->is_stmt_prepare_or_first_sp_execute())
     ptr->db= thd->strdup(ptr->db);
                                                                                
   ptr->alias= alias_str;

For some reason when reaching the problem code (x_free(thd->db)), these table db strings
are still pointing to the same place in memory.  This change gives them their own copy.

It is probable that the real bug is thd->stmt_arena->is_stmt_prepare_or_first_sp_execute()
being false in this situtation, but I would probably need some help tracking that down.

p.s.  Please tell me to shut up if I'm being too noisy. :-)

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/5424

When the mysql parser adds a table to the query table list it can set table db
from the thd->db variable by simply copying address and not creating a copy for
the table. If the statement contains a call of a stored procedure from another
database then the thd->db value will be freed and all tables that have db set
without copying will contain a garbage in their db variables. This happens 
because when the procedure is being loaded it will change current database and
thus change thd->db and free the old value.

Igor, I'm taking this over, this is very similar to the other bugs we're already handling.

Pushed into 5.0 tree tagged 5.0.23

Pushed into 5.0.23

Merged into 5.1 tree currently tagged 5.1.12

Noted in 5.0.23, 5.1.12 changelogs.

A table not found error could occur for statements that called a function defined in
another database.