MySQL Bugs: #29542: Allow to use a table referenced by a trigger in the query

Bug #29542	Allow to use a table referenced by a trigger in the query
Submitted:	4 Jul 2007 8:42	Modified:	6 Jul 2007 6:37
Reporter:	Zigmund Bulinsh	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.37	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
I implemented stored procedure which must change rebates for some records.
This procedure call UPDATE statement. But calling this procedure raised error from mysql: "Cannot update table 'suppliers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger".

But in real situation it is not truth!

Steps in database:

call SetProjectDiscounts() -> Update tasks set field = value where... ->
tasks_after_update(trigger) -> Update suppliers set field = value where...;

One moment that updating table in procedure in WHERE section is used SELECT statement which retrievs data from view, which is based on that table and also include "suppliers" table.

And there is no other operations in this chain. So it is wrong to raise this error.

How to repeat:
Launsh this query on the dumped database:

error:

update 
  tasks A
set 
  A.rebate = ifnull(20, A.rebate)
where 
  A.project_id = 2527 and
 (select task_payment_paid + task_payment_printed from tasks_data where task_id = A.id) = 0;

no error:

update 
  tasks A
set 
  A.rebate = ifnull(20, A.rebate)
where 
  A.project_id = 2527;

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You use VIEW tasks_data in WHERE part of statement which ends with error.

So I cannot use view in this situation:

I have "tasks", have view "tasks_data" which is select .. from tasks, suppliers... and so on??? This is not mentioned in manual, sory..

At least wasnt.

I have investigated all limitations of view. I am not a newbie! You do not understand the problem I think. I think what means "Cannot update table 'suppliers' in stored function/trigger because it is already used by
statement which invoked this stored function/trigger". And I always know where is the problem - but I have never had this problem, because I know how to do things.

And in my situation this error message is not accepted!

"Cannot update table 'suppliers' in stored function/trigger because it is already used by
statement which invoked this stored function/trigger", because teher is no such situation for this error..

When i execute statement:

update tableA 
set field1 = 20
where (select ok from viewA where id = tableA.id);

and viewA is select id from tableA;

This must not affect query.. Because it is a single block UPDATE which commits changes only after changing all rows. So what happening there?

This is not verry simple situation. This can be repeated only with my dump.
And then you will see that something goes wrong.

And the main thing...People from MySQL dont want to investigate problem - they tell not a bug and sends to documentation resource... Because it is easier than spend time on such bugs..

I also know that it can be documentation bug. because there wasnt documented as limitation that I cant use view in where statement of update query on table, where table's trigger modifies another table which is included in that view.

And the main thing...People from MySQL dont want to investigate problem - they tell not a
bug and sends to documentation resource... Because it is easier than spend time on such
bugs..

I also know that it can be documentation bug. because there wasnt documented as limitation
that I cant use view in where statement of update query on table, where table's trigger
modifies another table which is included in that vie

Ja ne amerikanec.

This limitation documented at http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html:

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.

So you do not understand what I am writing!

There are the steps:

1) CALL PROCEDURE1(..);
2) in this procedure there is an update statement:
update 
  tasks A
set 
  A.rebate = ifnull(20, A.rebate)
where 
  A.project_id = 2527 and
 (select task_payment_paid + task_payment_printed from tasks_data where task_id = A.id) = 0;
3) On table `tasks` there is and AFTER UPDATE TRIGGER which updates(!) table `suppliers`. `suppliers` table have no any triggers.
4) So now trigger updates `supplier` table record(!).
5) MySQL raises exception "Cannot update table 'suppliers' in stored
function/trigger because it is already used by
statement which invoked this stored function/trigger"
6) Only workaround is that that view `tasks_data` is using data from `suppliers` table..And there can be problem.. And in your last link there is no restictions about `views`.

I DONT UPDATE SUPPLIERS AND IN THE TRIGGER I DONT UPDATE(OR CALL PROCEDURE WHICH UPDATES) TABLE FOR WHICH THIS TRIGGER IS ASSIGNED!

And please fill this Dump to instance and look only on that query and trigger "AFTER UPDATE ON TASKS" it has only few simple lines of code. This is small part of my system.

Thanks for patience. :)

P.S. Mi mozhem i po russki, Sveta. Ne serdisj, chto ja tut zaparivaju tebja s etimi reopenami - prosto hochetsja vse-taki donesti do vas informaciju...

Zigmund,

thank you for the feedback. Bug database use worldwide users, this is why English is only language we can discuss bugs here.

> Only workaround is that that view `tasks_data` is using data from `suppliers`
> table..And there can be problem.. And in your last link there is no
> restictions about `views`.

Yes, VIEW tasks_data uses TABLE suppliers, so the TABLE is locked by read lock then SELECT statement is executed.

More simple test case:

create table t1(f1 int, f2 int);

insert into t1 values(1,1), (2,2), (3,3);

create table t2(f1 int, f2 int);

insert into t2 values(1,1), (2,2), (3,3);

create view v1 as select f1, f2 from t1;

create trigger tr1 after update on t2 for each row update t1 set f1=NEW.f1;

update t2 set f1 = 4 where f1 = 1 and (select f1 from v1 where f1 = 1) = 1;

Quote from the manual posted above contains phrase "a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.". This seems to be clear reference to any statement including which selects from views, uses functions etc.

So I can not consider this as a bug. But you can reclassify the report as feature request if you want.

Thanks for explanation, Sveta. Yes it will be cool if you can change this issue as Feature request. Becuase in your and my example there no need for view, simply select in where section from that table, which is updated by first table's trigger. In some situations it is verry incomfortant. And will be verry good feature if this will be released like in some other DBMS.

And also in documentation... that line about "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." is not clear for this situation. Will be better if there will be explained also this problem. Because this is verry specific situation I think which dont rise problems in other DBMS, but in MySQL yes.

Thanks, Sveta
Best regards,

But about documentation thats ok. Maybe it is only for my stupid mind.. :)

Thank you for the feedback.

Reclassified as feature request.

Zigmund is not the only one who would like this feature which is already found in so many other industrial strength databases.

Triggers and Stored Procedures are used to take the code out of applications.

In our case, many applications are written by suppliers of equipment which needs to store test result data into our database, and to use SP's and triggers is the only way we have because we cannot get all the contractors for this equipment back every time we have to make a database change.

In one case we have tests performed in many locations which must be in sequence. When the product is re-tested, the same sequence must be followed, but may be re-started at any point, depending on the nature of the fault found in the product. So, results for any later test stations must be flagged as invalid as soon as a test result is inserted which is older, but has an earlier test station sequence. But these results are written by the proprietary software we cannot access, so we must use triggers. To use queries involving aggregate functions based on timestamp and sequence are extremely cumbersome and slow.

The really easy way would be to do an update query on the same table within an insert trigger - either before the table is locked for the insert, or after it is unlocked after the insert to flag selected earlier results. So please give this feature serious consideration.

When you search for answers on this error message, there are many many people out there who are clearly going through a lot of pain as a result of this common feature not yet implemented - common in other dbs, that is.

Thanks

Bug #40371 was marked as a duplicate of this one.

Bug #14887 was marked as a duplicate of this one.

Major (some of them crippling), longstanding limitations om MySQL Triggers are hidden in the Trigger FAQ, which is hard to find. They should be included, indeed they should be emphasised, in the Main Trigger manual page.

And the 5.7 FAQ still misleadingly misstates one severe limitation:

"A trigger can also affect other tables, but 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."

Not only may a Trigger not modify such a table; it also may not modify a table from which it itself reads, even if the statement calling the Trigger contains no such reference.

Please, even if you're not going to fix MySQL Triggers, fix the Trigger docs!

I was mistaken about:

"Not only may a Trigger not modify such a table; it also may not modify a table from which it itself reads, even if the statement calling the Trigger contains no such reference."

Another Trigger caused that response, apologies for the mixup.

But please do move the Trigger FAQ to the main Trigger manual page.