Creating event to delete records in the table

Question

I am trying to get my mysql event to work but I am facing problem that the bus table is being created without the autoDelete event and I am not getting any error. I checked my table in phpmyadmin by executing this statement show events in the sql browser I am getting the result empty results as I said the bus table is there?!

I checked the EVENT syntax code in the phpmyadmin sql browser and it works?

DatabaseMetaData dbm = con.getMetaData();
        ResultSet tables = dbm.getTables(null, null, "bus", null);
        if (tables.next()) {
            // here if the table exist just update data. I have the most of my code her.
            return status;
        } else {
            // Create bus table
            stt.execute("CREATE TABLE IF NOT EXISTS bus"
                    + "(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                    + "mac VARCHAR(30) NOT NULL UNIQUE,"
                    + "route int(11) NOT NULL,"
                    + "latitude FLOAT(10,6) NOT NULL,"
                    + "longitude FLOAT(10,6) NOT NULL,"
                    + "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

        stt.execute("CREATE EVENT AutoDelete"
                    + "ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE "
                    + "DO "
                    + "DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 3 MINUTE)");
            insert_update_data(mac, route, latD, longD, con);
            return status;

        }

What exactly is it that you want to do? What you are doing now is creating the bus table only if it does not exist Next statement just removes all the records that have created_at before 3 minutes ago right? the second statement will be executed in 3 minutes after you do whatever it is you are doing outside of this else statement — Jeremy C., May 06 '15 at 12:57
@Jeremy: Yes, remove all records that have not been updated before 3 minutes and why is not the event being created with the bus table? — MrPencil, May 06 '15 at 13:11

score 0 · Accepted Answer · answered May 06 '15 at 13:11

0

Try putting space after the event name

"CREATE EVENT AutoDelete "

I think your code is currently creating a string that looks like :

CREATE EVENT AutoDeleteON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE DO DELETE FROM bus WHERE created_at < (NOW() - INTERVAL 3 MINUTE)

Although I am surprised that this didn't throw a SQLException

answered May 06 '15 at 13:11

DaveH

7,187
5
32
53

Yes the event is being yet created and realy I have not got any error before. My event does not delete the records which were not updated before 3 minutes? do you have an idea what's going on? – MrPencil May 06 '15 at 13:33
Events don't work on mySQL unless the EventScheduler has started. Are you sure that that is running? – DaveH May 06 '15 at 13:45
no I dont created it. How can I start it? where can I set this line `SET GLOBAL event_scheduler = ON;`? – MrPencil May 06 '15 at 13:51
Have a look here : http://stackoverflow.com/questions/10071871/enabling-mysql-event-scheduler-on-server-restarts – DaveH May 06 '15 at 14:32

Creating event to delete records in the table

1 Answers1