MySQL Bugs: #39953: Triggers are not working properly with multi table updates

Bug #39953	Triggers are not working properly with multi table updates
Submitted:	9 Oct 2008 14:00	Modified:	13 May 2009 16:37
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.68, 5.1.28	OS:	Any
Assigned to:	Staale Smedseng	CPU Architecture:	Any
Tags:	qc

Description:
Hi,

In the Stored Routines / Triggers section, in the MySQL manual, we have the following statement : 

"Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger." 

When using a multi table update, if some tables are only used for join purpose, there will never be updated. 
However, triggers seems to still consider those tables will be updated, and fails.

Regards,
  Jocelyn

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));
INSERT INTO t1 (a) VALUES (2);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
DROP TRIGGER IF EXISTS t_insert;
DELIMITER "//"
CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END //
DELIMITER ";"
INSERT INTO t2 (a,b) VALUES (1,2);

ERROR 1442 (HY000): Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

As you can see, the t2 table, althought in the multitable update, will never be updated, so the message is not correct, and MySQL should allow this kind of update (no infinite loop here).

Suggested fix:
Allow this kind of triggers execution.

Thank you for a problem report. Verified with 5.1.28 also.

In some cases subquery to t2 can be used as a workaround, like this:

mysql> CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1
    -> set
    -> date_insert=NOW() WHERE t1.a IN (select t2.b from t2 where t2.a=NEW.a); E
ND //
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t2 (a,b) VALUES (1,2)//
Query OK, 1 row affected (0.14 sec)

mysql> select * from t1//
+---+---------------------+
| a | date_insert         |
+---+---------------------+
| 2 | 2008-10-10 09:13:13 |
+---+---------------------+
1 row in set (0.00 sec)

Hi Valeriy,

Thanks for the subselect workaround.
Note that stored procedures are also affected by this issue.

Regards,
  Jocelyn

triage: setting tag to SR51MRU

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/67397

2804 Staale Smedseng	2009-02-24
      Bug#39953 Triggers are not working properly with multi table updates
                  
      open_table() is modified to take a flag instructing it not to check
      whether a table for update is used by the calling statement in a
      SF/trigger context.
                        
      mysql_multi_update_prepare() is modified to do this check after the
      tables are opened and locks are (possibly) downgraded.

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/68866

2828 Staale Smedseng	2009-03-11
      Bug#39953 Triggers are not working properly with multi
      table updates
      
      The check for conflicting use of tables in SP/triggers
      is moved to lock_tables(), to be performed after the
      possible downgrade of write locks has been done.

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/69002

2839 Staale Smedseng	2009-03-12
      Bug#39953 Triggers are not working properly with multi
      table updates
      
      The problem is that tables used in a calling statement 
      cannot currently be referenced in an SP/trigger. Read-
      only use of the table should be permitted.
      
      The check for conflicting use of tables in SP/triggers
      is moved to lock_tables(), to be performed after the
      possible downgrade of write locks has been done.
     @ mysql-test/r/trigger.result
        Results for the added test case is added.
     @ mysql-test/t/trigger.test
        A new test case is added, verifying correct table 
        conflict resolution, both read-only and write.
     @ sql/sql_base.cc
        The check for conflicting use of tables in SP/triggers
        is moved to lock_tables(), to be performed after the
        possible downgrade of write locks has been done.

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/70659

2717 Staale Smedseng	2009-03-27
      Bug#39953 Triggers are not working properly with multi table
      updates
      
      Attempt to execute trigger or stored function with multi-UPDATE
      which used - but didn't update - a table that was also used by
      the calling statement led to an error. Read-only reference to
      tables used in the calling statement should be allowed.
       
      This problem was caused by the fact that check for conflicting
      use of tables in SP/triggers was performed in open_tables(),
      and in case of multi-UPDATE we didn't know exact lock type at
      this stage.
      
      We solve the problem by moving this check to lock_tables(), so
      it can be performed after exact lock types for tables used by
      multi-UPDATE are determined.
     @ mysql-test/r/trigger.result
        Results for the added test case is added.
     @ mysql-test/t/trigger.test
        A new test case is added, verifying correct table multi-update
        conflict resolution, both read-only and write.
     @ sql/sql_base.cc
        The check for conflicting use of tables in SP/triggers is moved
        to lock_tables(), to be performed after the exact lock types
        have been determined. Also, an assert is added to open_ltable()
        to ensure this func is not used in a prelocked context.

Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:horst@mysql.com-20090327184517-25eq077q2beocs6y) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.0.82, 5.1.35, 6.0.12 changelogs.

Multiple-table UPDATE statements did not properly activate triggers.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)

Did this fix make is into 5.1.59? I'm having the same problem in 5.1.59. I'm calling a function that updates a table. A before-update triggers executes on the same table. I can do single table updates, but not multi table updates.

ERROR 1442 (HY000): Can't update table '*****' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

sucks to be me