Mysql Create Event

[mrt003003]

Hi there im tryng to get a simple example of creating a continuous event that updates a record every 5 seconds.

 

<?php mysql_query
("CREATE EVENT player_update 
ON SCHEDULE
EVERY 5 SECONDS DO 
UPDATE player SET ShipYard = '5' 
"); ?>

 

Heres the structure of my table:

 

PlayerID

PlayerName

ts (Time Stamp)

ShipYard

 

Could you point me in the right direction please??

 

Thanks for listening.

[mikosiko]

other than a syntax error, because you are using SECONDS instead of SECOND do you have any other problem?... which one?

[mrt003003]

There are no errors but the records are never updated to 5. I manually entered a current timestamp into the table and tested it. Later once i get this working ill update a new current time stamp every 5 seconds as well.

 

But yeah its just not updating at all??

 

Do i need to set any global events??

 

Thanks

[mrt003003]

I got it working by adding the following line:

mysql_query ("SET GLOBAL event_scheduler = 1");

 

Yay! Thank you

[mikosiko]

that means that the EVENT scheduler wasn't enabled to begin with... plus the syntax error (in case that one was not a typo)

[mrt003003]

Hmm it really is not working correctly at all. It updates immediately and doesnt at all there after???

 

Then i changed:

UPDATE player SET ShipYard = '5' 

To:

UPDATE player SET ShipYard = '1'

 

and it still updates to a 5 in the table immediately not waiting at all?? Ive restarted all my services and even retart my pc but its just not working.

 

Whats going on?

 

Thanks

[mikosiko]

in your EVENT creation code you are not specifying when the event should STARTS... therefore it is executed upon creation and should be executed thereafter depending on the EVENT's  definition.

 

check the EVENT syntax

 

post your current code and what exactly are your objectives for further help

[mrt003003]

Heres my code :

<?php mysql_query ("SET GLOBAL event_scheduler = 1");
mysql_query("CREATE EVENT player_update 
ON SCHEDULE
EVERY 2 MINUTE DO 
UPDATE player SET ShipYard = 1");?>

 

I changed it to a 2 minute interval but still its updating 5 in ShipYard and it does it straight after ShipYard is updated and a new current timestamp is set. I just need it to update ShipYards to a number every 2 minutes from the time the page loads. ITs totally strange that even thought now ShipYard is set to 1 and its updating to 5??

 

Thanks

 

[mikosiko]

guessing....

 

you created your first script version saying:

<?php mysql_query ("SET GLOBAL event_scheduler = 1");
mysql_query("CREATE EVENT player_update 
ON SCHEDULE
EVERY 5 MINUTE DO 
UPDATE player SET ShipYard = 5");?>

 

right?

 

then you updated your code to say:

<?php mysql_query ("SET GLOBAL event_scheduler = 1");
mysql_query("CREATE EVENT player_update 
ON SCHEDULE
EVERY 2 MINUTE DO 
UPDATE player SET ShipYard = 1");?>

 

and executed it again... right?

 

if so... your second code should have gave you an error saying "The event player_update already exists"... which probably you didn't see it because you don't have the  error reporting active in your code (include the 2 lines in my signature after your <?php line and you will see the error) .,.. therefore the original event (update every 5 second) still active.

 

 

[mrt003003]

The first script was:

<?php mysql_query ("SET GLOBAL event_scheduler = 1");
mysql_query("CREATE EVENT player_update 
ON SCHEDULE
EVERY 5 SECOND DO 
UPDATE player SET ShipYard = 5");?> 

 

I tested it and it looked like it was working because 5 seconds is a short time to update a record manually. So i wanted to restest but using a longer time period to:

<?php mysql_query ("SET GLOBAL event_scheduler = 1");
mysql_query("CREATE EVENT player_update 
ON SCHEDULE
EVERY 2 MINUTE DO 
UPDATE player SET ShipYard = 1");?>

 

I tested it and again it updates ShipYard to 5 and seems to be straightaway or in 5 seconds. However ive tried both of them at the same time and there is no error saying update already exists.

I also have error_reporting from your signature on too.??? If its still active how to i reset it?

 

Thanks

 

[mikosiko]

as I said... if you executed those 2 codes one after the other the first one should remain active and 2nd one will give you an error (assuming that you are connecting to Mysql with the same user in the same DB).

 

suggestions:

- You should activate the EVENT scheduler using mysql configuration file (activate it just one time in one place).

- You should create your EVENTS only one time using any GUI that you are familiar with (PHPMYADMIN, MYSQL-WORKBENCH are some options)

- Thereafter you can use ALTER EVENT syntax in case that you need to modify your EVENT definition or change its STATUS (enable or disable it)

 

read the link that I did provide for you in my 3rd post.

[mrt003003]

Thank you ill check it out. But how do i delete the 1st one ON SCHEDULE EVERY 5 SECOND? because thats happening all the time.  :|

 

Thanks 

[mikosiko]

DROP EVENT player_update;

 

and also you can disable the EVENT scheduler

SET GLOBAL event_scheduler=OFF;  (or 0)

[mrt003003]

Thank you thats brilliant! I was just a bit confused because it was running even without any create event code. Thanks again