Bug #14308 server hang, with recursive view (after rename table)
Submitted: 25 Oct 2005 22:26 Modified: 27 Apr 2006 14:48
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.15-nt/5.0.16 BK source OS:Microsoft Windows (windows (likely others)/Linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[25 Oct 2005 22:26] Martin Friebe
Description:
With "raname table" being allowed to rename views, it is now possible to create a "recursive" view.

Selecting from such a view sends the server into an endless loop, slowly consuming memory

Repeat the sql below.

when executing the select be ready to kill your server. (windows with taskmanager, it does not responnd to the service stop request)

How to repeat:
create table t1 (a int);
create view v1 as select * from t1;
create view v2 as select * from v1;
rename table t1 to t2;
rename table v2 to t1;

select * from v1;

Suggested fix:
check for recursion, while executing any action on the view.
[26 Oct 2005 0:42] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.04 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v2 as select * from v1;
Query OK, 0 rows affected (0.01 sec)

mysql> rename table t1 to t2;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table v2 to t1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from v1;
Aborted

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                        
23146 miguel    16   0  156m 114m 4532 S 99.9 24.3   0:35.26 mysqld                                                                         
23161 miguel    16   0  2056  964  752 R  0.7  0.2   0:00.07 top
[13 Dec 2005 13:18] 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/107
[29 Mar 2006 14:06] Konstantin Osipov
Reviewed by email, approved with one note.
[13 Apr 2006 20:12] 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/4942
[24 Apr 2006 6:47] Konstantin Osipov
Pushed into 5.0.21, merged into 5.1.10
[25 Apr 2006 6:11] Oleksandr Byelkin
pushed to 5.1.10, 5.0.21, 4.1.19 and 4.0.27
[25 Apr 2006 6:12] Oleksandr Byelkin
oops sorry, it was pushed to pushed to 5.1.10 and 5.0.21
[27 Apr 2006 14:48] Paul Dubois
Noted in 5.0.21, 5.1.10 changelogs.

Prevent recursive views caused by using <literal>RENAME
TABLE</literal> on a view after creating it. (Bug #14308)