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

[7 Feb 2006 19:58] Kevin Regan
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.
[7 Feb 2006 20:02] Kevin Regan
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.
[7 Feb 2006 22:34] Kevin Regan
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).
[7 Feb 2006 22:36] Kevin Regan
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
[7 Feb 2006 22:48] Kevin Regan
Putting in simpler version and OS strings.
[8 Feb 2006 2:02] Hartmut Holzgraefe
verified with latest 5.0bk, 5.1bk seems to be ok though
[8 Feb 2006 20:00] Kevin Regan
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.
[8 Feb 2006 20:06] Kevin Regan
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))
[8 Feb 2006 23:21] Kevin Regan
This does, indeed, cause the running executable to grow when making calls to other database, so a better solution is needed.
[9 Feb 2006 1:26] Kevin Regan
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. :-)
[24 Apr 2006 19:35] Bugs System
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
[24 Apr 2006 19:41] Evgeny Potemkin
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.
[24 Apr 2006 22:10] Konstantin Osipov
Igor, I'm taking this over, this is very similar to the other bugs we're already handling.
[27 Jun 2006 11:19] Konstantin Osipov
Pushed into 5.0 tree tagged 5.0.23
[29 Jun 2006 12:38] Konstantin Osipov
Pushed into 5.0.23
[7 Jul 2006 18:12] Konstantin Osipov
Merged into 5.1 tree currently tagged 5.1.12
[7 Jul 2006 19:37] Paul Dubois
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.
[18 Jun 2009 6:48] Bugs System
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)
[3 Nov 2009 7:16] Bugs System
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)
[3 Nov 2009 15:44] Paul Dubois
Noted in 5.4.4, 6.0.14 changelogs.
[12 Nov 2009 8:21] Bugs System
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)
[12 Nov 2009 19:09] Paul Dubois
Noted in 5.5.0 changelog.
[25 Jul 2011 18:17] Greg Burcher
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.
[25 Jul 2011 19:05] Greg Burcher
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.