Bug #13683 INSERT DELAYED into a view create an infinite loop
Submitted: 1 Oct 2005 15:23 Modified: 14 Apr 2006 14:35
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0.14-rc/5.0.15-rc BK source OS:Linux (linux)
Assigned to: Alexander Ivanov CPU Architecture:Any

[1 Oct 2005 15:23] jocelyn fournier
Description:
Hi,

When trying to use an INSERT DELAYED into a view, the query never ends.

Regards,
  Jocelyn

How to repeat:
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v;
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1);
CREATE VIEW v as SELECT * FROM t1;
INSERT INTO v VALUES (1);
Query OK, 1 row affected (0.00 sec)
INSERT DELAYED INTO v VALUES (1);

=> infinite loop
[1 Oct 2005 15:36] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-rc-debug

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

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected (0.00 sec)

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

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE VIEW v as SELECT * FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT DELAYED INTO v VALUES (1);
Aborted
miguel@hegel:~/dbs/5.0>
[4 Apr 2006 16:32] 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/4452
[7 Apr 2006 12:33] Sergey Petrunya
The fix at http://lists.mysql.com/commits/4452 removes the infinite looping and causes MySQL to produce error for "INSERT DELAYED INTO view ... " statements.

However I have doubts if producing an error is ok in this case.

1. I don't see any reason for "INSERT DELAYED INTO view" not to work for an "insertable" VIEW over a table handled by insert-delayed-capable engine. I had a short consultation with Sanja and he couldn't see any reasons either.

2. Even if there is some reason that INSERT DELAYED should not work for a VIEW, then INSERT DELAYED should work as regular INSERT, and not produce errors, (such behavior will be consistent with behavior of  "INSERT DELAYED INTO non_insert_delayed_capable_table").
[7 Apr 2006 12:34] Sergey Petrunya
I'm not setting the bug to "patch approved" because of the above concerns.
[7 Apr 2006 12:49] Sergey Petrunya
Note by Alexander: In current 5.1, "INSERT DELAYED INTO insertable_view" produces an error (but mysqld doesn't enter an infinite loop).
[11 Apr 2006 10:27] Lars Thalmann
I discussed this with Sergey and we agreed that Alex will push his 
temporary fix and create a new bug report for the complete fix.
[11 Apr 2006 10:51] jocelyn fournier
Hi,

About errors with INSERT DELAYED, if the INSERT DELAYED queue is full, they are processed as normal INSERT.
However INSERT DELAYED doesn't display errors when there are duplicate keys, but if it switches in normal INSERT mode, then I get duplicate key error, which I was not expecting.

Shouldn't we switch INSERT DELAYED to INSERT IGNORE if the delayed queue is full ?
(right now I have to use INSERT IGNORE DELAYED to workaround this issue and get the appropriate behaviour).

Thanks,
  Jocelyn
[11 Apr 2006 14:02] Alexander Ivanov
Fixed in 5.0.21 (removes only infinite looping).
New BUG#19009 is opened (other issues concerning INSERT DELAYED into a view).
[14 Apr 2006 14:35] Paul Dubois
Noted in 5.0.21 changelog.

<literal>INSERT DELAYED</literal> into a view caused an
infinite loop. (Bug #13683)