MySQL Event Scheduler

Question

I have set 1 mysql events that transferring all data with 0 flag from one table to another (table 2) every 12 hours, then update query to set as 1 the flag after transferring. INSERT and UPDATE is on one event process.

My problem is, all transferred data have duplicate on the table 2 that looks like 2 event is running.

Any idea?

Here is my event codes:

DELIMITER |
CREATE
    EVENT
    IF NOT EXISTS
    event_transfer
    ON SCHEDULE EVERY 12 HOUR
    STARTS '2018-08-10 06:00:00'
    ON COMPLETION NOT PRESERVE
    ENABLE

DO

BEGIN


INSERT INTO dbsample.tblmirror (Column1, Column2, Column3)
       (SELECT Column1, Column2, Column3
               FROM tblmaster
                    WHERE is_transfer = 0);

UPDATE dbsample.tblmaster
   SET is_transfer = 1
       WHERE is_transfer = 0;


END |

DELIMITER;

No duplicate event, simple code, no triggers - I doubt that the problem lies here at all. — P.Salmon, Aug 13 '18 at 11:51

score 0 · Answer 1 · answered Aug 13 '18 at 12:01

Quoting P.Salmon:

No duplicate event, simple code, no triggers - I doubt that the problem lies here at all.

P.Salmon is right here, and it would be good, if you figured out, what other job is doing this.
But, you can also avoid this problem in another way. Since you don't want to have duplicates, there is a way to detect duplicates, right? Be it, that the combination of those 3 columns has to be unique, or whatever, then indicate so in your database.

Create a unique index (or primary key) on the columns that make each row unique.

Then, instead of a simple INSERT you do INSERT IGNORE, and voilà, you won't have duplicates any more.

Quoting the manual:

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

MySQL Event Scheduler

1 Answers1