Bug #17726 mysqld hangs at update of view with check option
Submitted: 26 Feb 2006 19:47 Modified: 14 Mar 2006 17:34
Reporter: Ghica van Emde Boas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.18/5.0.19 BK OS:Windows (Win XP Prof/Linux Suse)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[26 Feb 2006 19:47] Ghica van Emde Boas
Description:
This bug may be related to 16813 but the symptoms are more serious: mysqld turns up the cpu consumption to 100% and the only way to stop is to power-off the PC.

I have three tables and two views, the definitions of which I will attach as a file.
The first view is a join of the three tables, and it is perfectly updatable on all three tables. The second view is something like: all employees with a salary > 25000 with check option.
I am trying an UPDATE statement which takes a row outside the view and increases the salary to be in the view. I am not sure whether the check option should fail or not, in any case it should not hang!
 

How to repeat:
Import the three tables and two views, then do this update statement:

UPDATE Topverdiener 
 SET salaris = 35000
 WHERE id =5;

and mysqld will hang.

Suggested fix:
An OK result, or an error message, whatever is appropriate.
[26 Feb 2006 19:50] Ghica van Emde Boas
sample table and view defintions to recreate bug

Attachment: viewbug.sql (application/octet-stream, text), 3.93 KiB.

[26 Feb 2006 20:28] MySQL Verification Team
Thank you for the bug report. I was able to repeat on Linux/Windows.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.19-debug

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

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+----------------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                                                     |
+----+------+-----------+------+---------+------+-------+----------------------------------------------------------+
|  2 | root | localhost | db15 | Query   |  190 | init  | update topverdiener 
 set salaris = 35000
 where id =5 |
|  3 | root | localhost | NULL | Sleep   |   83 |       | NULL                                                     |
<cut>
[3 Mar 2006 13:20] 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/3430
[9 Mar 2006 12:29] Evgeny Potemkin
When the Item_cond::fix_fields() function reduces cond tree, it in loop
scans it's own list and when it founds Item_cond with same function (AND
or OR) it does next things: 1) replaces that item with item's list. 2)
empties item's list. Due to this operation is done twice - for update and
for view, at the update phase cond's list of lower view is already empty.
Empty list returns ref to itself, thus making endless loop by replacing
list with itself, emptying, replacing again and so on. This results in
server hung up.

Fixed in 5.0.20, cset 1.2053.40.1
[9 Mar 2006 21:18] Evgeny Potemkin
Fixed in 5.1.8
[14 Mar 2006 17:34] Mike Hillyer
Documented in 5.0.20 and 5.1.8 changelogs.