MySQL plugin development to notify circular trigged query sequence
Budget: $30 – $250 USD
Suppose Table A, Table B and Table C respond to 3 triggers,
Table A is a place to store business-driven events by time sequence, Table B is a storage for success logs and Table C is a storage for failure logs.
Trigger 1 is fired, once an event (for an example, a successful HTTP re-connection) is updated in Table A. Trigger 1 will insert a row to Table B and delete a row from Table C.
Trigger 2 is fired, once an event failed to be inserted (for example, a duplicated row) to Table A. Trigger 2 will insert a row to Table C.
Trigger 3 is fired once a row was unexpectedly deleted (for example, a logger exception) in Table C. Trigger 3 will update some row in Table A.
When some failure logs are unexpectedly deleted in Table C, Trigger 3 fires and updates some rows in Table A, then Trigger 1 fires and tries to delete some rows from Table C.
It fails because Trigger 1 tries to delete some rows in Table C that have being deleted by Trigger 3. Included but not limited to such cases, we call it a circular trigger query sequence.
A sample business process,
Thread 1
HTTP connect for 1st time: failed
-> insert a row to Table A -> succeeded
-> insert a row to Table C -> succeeded
Thread 2
HTTP connect for 1st time: failed
-> insert a row to Table A (duplicated) -> failed
(Trigger 2) -> insert a row to Table C -> succeeded
Thread 1
HTTP connect for 2nd time: failed
-> insert a row to Table A (duplicated) -> failed
(Trigger 2) -> insert a row to Table C -> succeeded
Thread 2
HTTP connect for 2nd time: succeeded
-> update a row in Table A -> succeeded
(Trigger 1) -> insert a row to Table B -> succeeded
-> delete all related rows in Table C -> succeeded
Circular case: Thread 1
HTTP connect for 3rd time: succeeded
-> update a row in Table A -> succeeded
(Trigger 1) -> insert a row to Table B (duplicated) -> failed
-> delete all related rows in Table C (already deleted) -> failed
Circular case: Thread 3 (for example, a watchdog caused an exception)
Deleted some row in Table C -> succeeded
(Trigger 3) -> update some row in Table A -> succeeded
(Trigger 1) -> delete all related rows in Table C (already deleted) -> failed
We are looking for a Mysql plugin solution that can notify such query sequences. With no requirements for NDA, you are encouraged to design a sample diagram as simple as above (not necessary to use the same HTTP connection sample) to start your PoC and a working sample is required. MySQL version >= 8.0 or MariaDB version >= 10.0.
Table A is a place to store business-driven events by time sequence, Table B is a storage for success logs and Table C is a storage for failure logs.
Trigger 1 is fired, once an event (for an example, a successful HTTP re-connection) is updated in Table A. Trigger 1 will insert a row to Table B and delete a row from Table C.
Trigger 2 is fired, once an event failed to be inserted (for example, a duplicated row) to Table A. Trigger 2 will insert a row to Table C.
Trigger 3 is fired once a row was unexpectedly deleted (for example, a logger exception) in Table C. Trigger 3 will update some row in Table A.
When some failure logs are unexpectedly deleted in Table C, Trigger 3 fires and updates some rows in Table A, then Trigger 1 fires and tries to delete some rows from Table C.
It fails because Trigger 1 tries to delete some rows in Table C that have being deleted by Trigger 3. Included but not limited to such cases, we call it a circular trigger query sequence.
A sample business process,
Thread 1
HTTP connect for 1st time: failed
-> insert a row to Table A -> succeeded
-> insert a row to Table C -> succeeded
Thread 2
HTTP connect for 1st time: failed
-> insert a row to Table A (duplicated) -> failed
(Trigger 2) -> insert a row to Table C -> succeeded
Thread 1
HTTP connect for 2nd time: failed
-> insert a row to Table A (duplicated) -> failed
(Trigger 2) -> insert a row to Table C -> succeeded
Thread 2
HTTP connect for 2nd time: succeeded
-> update a row in Table A -> succeeded
(Trigger 1) -> insert a row to Table B -> succeeded
-> delete all related rows in Table C -> succeeded
Circular case: Thread 1
HTTP connect for 3rd time: succeeded
-> update a row in Table A -> succeeded
(Trigger 1) -> insert a row to Table B (duplicated) -> failed
-> delete all related rows in Table C (already deleted) -> failed
Circular case: Thread 3 (for example, a watchdog caused an exception)
Deleted some row in Table C -> succeeded
(Trigger 3) -> update some row in Table A -> succeeded
(Trigger 1) -> delete all related rows in Table C (already deleted) -> failed
We are looking for a Mysql plugin solution that can notify such query sequences. With no requirements for NDA, you are encouraged to design a sample diagram as simple as above (not necessary to use the same HTTP connection sample) to start your PoC and a working sample is required. MySQL version >= 8.0 or MariaDB version >= 10.0.