Bug #48449 hang on show create view after upgrading when view contains function of view

• Submitted: 31 Oct 13:10
• Modified: 31 Oct 13:10
• Reporter: Susanne Ebrecht
• Status: Analyzing
• Category: Server: DDL
• Severity: S1 (Critical)
• Version: 5.1
• OS: Any
• Assigned to: Susanne Ebrecht
• Triage: Needs Triage: D1 (Critical)

[31 Oct 13: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 10:19] Shane Bester

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.