Bug #48449 hang on show create view after upgrading when view contains function of view
Submitted: 31 Oct 2009 12:10 Modified: 18 Jun 2010 1:19
Reporter: Susanne Ebrecht Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version:5.1 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[31 Oct 2009 12:10] Susanne Ebrecht
Description:
A customer run into this issue.

Show create view ...;

CPU = 100 %

MySQL hangs. You can do SHOW databases; further on but not even SELECT user FROM mysql.user; or something similar what is not even related to the customer database.

Why is it hanging?

It costs customer and me long time to figure this out. The problem is very easy:

On MySQL 5.0 you have a view named 'a' depending on a view named 'x'. Means name of depending view > name of view

Now upgrade to MySQL 5.1 and use mysql_upgrade.

All looks fine but in information schema all definitions of views are empty.

Now say show create view 'a';

System will hang.

You need to drop view 'a'; Then drop and create view 'x' and then re-create view 'a' again.

How to repeat:
I will make a test case
[13 Nov 2009 9:19] MySQL Verification Team
how to repeat:
--------------

1. on 5.0.87 run this sql:

delimiter ;

drop table if exists t1,t2,v1,v2;
drop function if exists f;
create table t1(a int)engine=myisam;
insert into t1 values (1),(2);
create or replace view v1 as select * from t1;
delimiter $
create function f() returns int
begin
        select * from v1 left join v1 t1 using(a) limit 1 into @a;
        return @a;
end $
delimiter ;
create or replace view v2 as select f() from v1;
show create table v2;

2. Now, shutdown 5.0.87 and place this datadir into 5.1.40.
3. startup 5.1.40 and run mysql_upgrade -uroot.
4. login to 5.1.40 and run 'show create table v2'.
5. cpu flatlines at 100%.

we also notice a bug in the mysql_upgrade procedure, the following messages appear in the error log:

Stored routine 'test'.'f': invalid value in column mysql.proc.character_set_client.
Stored routine 'test'.'f': invalid value in column mysql.proc.collation_connection.
Stored routine 'test'.'f': invalid value in column mysql.proc.db_collation.
[30 Nov 2009 16:52] Susanne Ebrecht
Many thanks Shane for providing the test case.
[9 Feb 2010 17:46] 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/99763

3374 Davi Arnaut	2010-02-09
      Bug#48449: hang on show create view after upgrading when view contains function of view
      
      SHOW CREATE TABLE on a view (v1) that contains a function whose
      statements use view (v2), could trigger a infinite loop if the
      view referenced within the function caused a warning to be raised
      while opening the said view (v2).
      
      The problem was a infinite loop over the stack of internal error
      handlers. The problem would be triggered if the stack contained
      two or more handlers and the first two handlers didn't handle the
      raised condition. In this case, the loop variable would always
      point to the second handler in the stack.
      
      The solution is to correct the loop variable assignment so that
      the loop is able to iterate over all handlers in the stack.
     @ mysql-test/r/view.result
        Add test case result for Bug#48449.
     @ mysql-test/std_data/bug48449.frm
        Add a incomplete view definition that causes a warning to be
        issued.
     @ mysql-test/t/view.test
        Add test case for Bug#48449
     @ sql/sql_class.cc
        Iterate over all handlers in the stack.
[10 Feb 2010 18:11] 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/99867

3376 Davi Arnaut	2010-02-10
      Bug#48449: hang on show create view after upgrading when view contains function of view
      
      SHOW CREATE TABLE on a view (v1) that contains a function whose
      statement uses another view (v2), could trigger a infinite loop
      if the view referenced within the function causes a warning to
      be raised while opening the said view (v2).
      
      The problem was a infinite loop over the stack of internal error
      handlers. The problem would be triggered if the stack contained
      two or more handlers and the first two handlers didn't handle the
      raised condition. In this case, the loop variable would always
      point to the second handler in the stack.
      
      The solution is to correct the loop variable assignment so that
      the loop is able to iterate over all handlers in the stack.
     @ mysql-test/r/view.result
        Add test case result for Bug#48449.
     @ mysql-test/std_data/bug48449.frm
        Add a incomplete view definition that causes a warning to be
        issued.
     @ mysql-test/t/view.test
        Add test case for Bug#48449
     @ sql/sql_class.cc
        Iterate over all handlers in the stack.
[11 Feb 2010 21:01] Davi Arnaut
Queued to mysql-5.1-bugteam.
[1 Mar 2010 8:42] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:32] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:37] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[6 Apr 2010 12:09] Jon Stephens
Documented as follows in the 5.1.45, 5.5.3, and 6.0.14 changelogs:

        Running SHOW CREATE TABLE on a view v1 that contained a function
        which accessed another view v2 could trigger a infinite loop if
        the view (v2) referenced within the function caused a warning to
        be raised while being opened.

Closed.
[17 Jun 2010 11:44] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:22] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:10] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)