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 19:58	Modified:	12 Nov 2009 19:09
Reporter:	Kevin Regan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

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.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090617073019-azsawauatv99124t) (version source revid:jon.hauglid@sun.com-20090608082432-o27d1guc3g6qmk5r) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@ibmvm-20091009114750-utkhs9029vewkluf) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 5.4.4, 6.0.14 changelogs.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.

So is this problem fixed so that a view should be able to call a function defined in a different database. I am experiencing a problem in v5.5.11. General form is:

CREATE VIEW db1.view1 AS
  SELECT db2.function1(SUM(col1))
  FROM table1;

I am still debugging on my end, but wanted to be sure this was not an open bug.

It gets worse. I cannot even call a function in the same database from a view, using the dbname.function() syntax. Statement of the form:

CREATE VIEW db1.view1 AS SELECT db1.function1(col1) FROM table1;

produces this error message:

Unexpected error: StatementCallback; bad SQL grammar [CREATE VIEW db1.view1 AS SELECT db1.function1(col1) FROM table1]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION db1.function1 does not exist

If I create the function in the same database as the view, and also remove the database name prefix on the function invocation, then it works. My workaround is to declare all functions in all databases so I can always reference them without the database name prefix. Luckily, that works for me right now.

Again, I am using MySQL/InnoDB v5.5.11.