I have a table which acts as a short term cache of some status information. Each row in the table holds the number of images to process and the number of processed images.
I have multiple servers writing to this table (Amazon EC2 setup).
When all the images are processed, I want to delete the row.
`ImagesToProcess` = `ImagesProcessed`;
Putting that in a AFTER UPDATE ON trigger, I get an error upon execution :
Error Code: 1142. Can't update table 'im_retailerimageprocessing' in stored function/trigger because it is already used by statement which invoked this function/trigger.
For me, as I'm using it in an AFTER statement, there should be no conflict. But, obviously, I'm wrong and I don't know how to achieve the desired result.
I don't want to modify the app as it is very simple and just "ticks" a box when it has done its job. The scheduler/dispatcher deals with creating the threads and I want to use the DB to keep all the DB uptodate.
What am I missing?