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.
This event auto_delete_last_order_event would call a store procedure. If the store procedure was run manually , it successfully affected the table.
On 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.
On manually run the store procedure , it does affect database and it takes less than 0.5 seconds to complete.
UPDATE 1:
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
