1

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. enter image description here

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
  • Please provide SHOW CREATE PROCEDURE -- We need to see not only the body, but the meta information about the SP. – Rick James Jan 8 at 5:34
  • @RickJames Hi Rick. I already updated my question. – xyonme Jan 8 at 6:42
  • Seems like the inner couple of selects could be simplified to SELECT user_id, MIN(join_id) FROM join_lastorder GROUP BY user_id – Rick James Jan 8 at 6:56
  • @RickJames hi Rick. this query is to keep the last two row for each user_id . your suggestion would only keep last one row for each user id. but my question is why it is not affecting database if it was run by event scheduler. how do we trouble shoot this? – xyonme Jan 8 at 7:16

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.