Scheduled event to copy data from one table to another in MySQL returns empty results

Question

Run on phpMyAdmin local server: I have two tables: user (with data) and user_t (empty). They have the same column names, order, and type (the user_t table has an extra column for timestamping the input).

I want to create a scheduled event to check if the timestamp of each user log has a specific value, and if it exists, I want to copy it to user_t Also remove it from user. very simple.

Deletion works fine. It was INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;< CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; that gave me trouble.

This is the code I use to create the event:

CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
INSERT INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;

However, it keeps returning empty results. So, I thought I must be doing something wrong. Can you sincerely help me get this code working? After all, it's from a tutorial and should be fine.

I've tried using code and even considered looking into transactions , but that seems too bulky for this kind of operation.

edit

So after playing around with nbk's code I finally figured it out and here's the code that worked for me (I generalized the table and column names so they don't mislead anyone):

DELIMITER $$
CREATE EVENT event_name 
ON SCHEDULE EVERY 1 MINUTE 
STARTS CURRENT_TIMESTAMP 
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO 
    BEGIN 
    INSERT INTO empty_table (col_name1, col_name2, col_name3, col_name4, col_name5) 
    SELECT col_name1, col_name2, col_name3, col_name4, col_name5 FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
    DELETE FROM data_table WHERE col_name < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE; 
    END $$
DELIMITER ;

Hope it helps people like me in the future. Thanks again nbk for the quick help. Thank you very much, of course your answer is correct.

Answer 1

You run the event every minute and want to input the last three minutes, I don't know if you have that many new inserts or you need a python script or insert for an hour like this ==>For testing purposes I set the frequency to 1 minute...

You should still override your events.

First, do you really want duplicates, maybe INSERT IGNORE which will enter a new row if the constraint is violated

Second, if you have more than one statement, you need a BEGIN END

DELIMITER $$
CREATE EVENT users_u
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO 
    BEGIN
        INSERT IGNORE INTO user_t SELECT * FROM user WHERE user.reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
        DELETE FROM user WHERE reg < CURRENT_TIMESTAMP - INTERVAL 3 MINUTE;
    END $$
DELIMITER ;