How do we troubleshoot MYSQL scheduled event that did not work as expected?
We got numerous events on our database. All these event that have been scheduled and executed by event scheduler.
auto_delete_last_order_event would call a store procedure. If the store procedure was run manually , it successfully affected the table.
INFORMATION_SCHEMA.events , it states all my events are working fine.
My other events are affecting the database but only this
auto_delete_last_order_event event does not affect the table.
SHOW CREATE PROCEDURE db.auto_delete_lastorder
Procedure : auto_delete_lastorder sql_mode :STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION character_set_client : utf8 collation_connection : utf8_general_ci database_collection : latin1_swedish_ci
Create Procedure :
CREATE DEFINER=`root`@`localhost` PROCEDURE `auto_delete_lastorder`() BEGIN declare totalLastData int; set totalLastData = 2; DROP TEMPORARY TABLE IF EXISTS tempData; CREATE TEMPORARY TABLE tempData select join_id from lastorder jl where not exists( select join_id from ( select user_id,join_id from ( SELECT user_id, join_id, (@row:=if(@prev=user_id, @row +1, if(@prev:= user_id, 1, 1))) rn FROM lastorder tt CROSS JOIN (select @row:=0, @prev:=null) c order by user_id, join_id desc ) src where rn <= totalLastData order by user_id, join_id )tt where jl.join_id = tt.join_id ); delete lastorder FROM lastorder JOIN tempData ON lastorder.join_id = tempData.join_id; END