MySQL Bugs: #13683: INSERT DELAYED into a view create an infinite loop

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

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

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>

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

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").

I'm not setting the bug to "patch approved" because of the above concerns.

Note by Alexander: In current 5.1, "INSERT DELAYED INTO insertable_view" produces an error (but mysqld doesn't enter an infinite loop).

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.

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

Fixed in 5.0.21 (removes only infinite looping).
New BUG#19009 is opened (other issues concerning INSERT DELAYED into a view).

Noted in 5.0.21 changelog.

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