A trigger or stored function fails with error 1442 on its own table
ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.Short answer
MySQL forbids a stored function or trigger from modifying a table that the invoking statement is already reading or writing. That is error 1442. If a trigger needs to change the row being saved, assign to NEW in a BEFORE trigger. If a function is the problem, turn it into a stored procedure and CALL it.
Fix it fast
1Find out which cause you have
Read the table name in the message, then print the trigger or function body to see which statement inside it touches that table.
SHOW CREATE TRIGGER shop.orders_after_update\G SHOW CREATE FUNCTION shop.my_func\G2Match what you see, then jump to its fix
- 1
The table named in the error is the trigger's own table, for example an UPDATE orders inside a trigger on orders.A trigger writes to the table that fired itFix - 2
The error appears when a query such as UPDATE t SET c = my_func(id) runs, and my_func changes table t.A stored function modifies a table used by the calling statementFix
- 1
What does the error look like?
ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Cause 1 of 2
A trigger writes to the table that fired it
1Check
The trigger body runs a separate UPDATE or INSERT on the table it is attached to. The statement that fired the trigger is already using that table, so the rule applies.
SHOW CREATE TRIGGER shop.orders_after_update\G2Fix
To change a value in the row being inserted or updated, do not run an UPDATE. Use a BEFORE trigger and assign to the column on NEW.
DROP TRIGGER shop.orders_after_update;
DELIMITER //
CREATE TRIGGER shop.orders_before_update
BEFORE UPDATE ON shop.orders
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
END//
DELIMITER ;3Verify
Run the statement that failed before.
UPDATE shop.orders SET status = 'paid' WHERE id = 1;Confirmed by dev.mysql.com
Cause 2 of 2
A stored function modifies a table used by the calling statement
1Check
Stored functions, and triggers, may not modify a table the invoking statement is using for reading or writing. Stored procedures are not under this limit, unless the procedure is itself called from a function or a trigger.
SHOW CREATE FUNCTION shop.my_func\G2Fix
Move the table-changing work into a stored procedure and call it with CALL from your application or script, instead of calling a function inside the statement.
DELIMITER //
CREATE PROCEDURE shop.refresh_totals(IN p_id INT)
BEGIN
UPDATE shop.orders SET status = 'checked' WHERE id = p_id;
END//
DELIMITER ;
CALL shop.refresh_totals(1);3Verify
CALL returns without error 1442.
CALL shop.refresh_totals(1);Confirmed by dev.mysql.com
How do I stop it coming back?
- Use BEFORE triggers when the aim is to adjust the row being written.
- Keep logic that changes other rows of the same table out of triggers and functions; put it in a procedure.
- Remember that a procedure called from inside a trigger carries the same limits as the trigger.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix