CODEWITHSUNDEEP
mysqltriggersmysql-clustermysql-error-1442
OldYogurtPot
OldYogurtPot

Reputation: 1

MySQL trigger throws ERROR 1442, even though its updating a table different to the one calling the trigger

I'm in the process of trying to optimise my queries, part of which is storing a value like "number of comments" in its own column instead of having to use COUNT() each time. To do this, I plan to create a couple of triggers in the "comments" table. On insert/delete it will update the posts.numComments column by +/- 1 respectively.

However the trigger is where my problems are at, I'm getting this error:

General error: 1442 Can't update table 'posts' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Which I understand means you can't update the table that called the trigger, however as far as I can tell, no part of the trigger would attempt to alter the table it was being called from; the "comments" table. It should only be updating the posts table

The trigger causing this error:

CREATE TRIGGER `increase cache` 
AFTER INSERT ON `comments`
FOR EACH ROW 
UPDATE `posts` 
SET `numComments` = (numComments + 1) 
WHERE postID = NEW.postID

What's really stumping me is I'm also using this approach for numLikes too, and have an almost identical trigger that runs without any errors:

CREATE TRIGGER `increase cached likes` 
AFTER INSERT ON `likes`
FOR EACH ROW 
UPDATE `posts` 
SET `numlikes` = (numlikes + 1) 
WHERE postID = NEW.postID

There are no triggers on the post table, and only one trigger on the comments table that runs on DELETE so I really have no idea what could be causing this problem. Any help would be much appreciated!

If it helps, I'm using MySQL Cluster (I know about triggers not auto copying between nodes that's not my problem)

I've tried to find a solution to this online but I can't find anyone with this problem.

What should be happening is when a comment gets added to the comments table, the numComments column for the post commented on should increase by one, but I keep getting the error above.

Upvotes: 0

Views: 133

Answers (1)

OldYogurtPot
OldYogurtPot

Reputation: 1

This might have been caused by the INSERT INTO comments query selecting from posts. I fixed this by moving numLikes and numComments to their own interactions table

Upvotes: 0

Related Questions