Jump to content
MakeWebGames

Crons, Timestamp or none?


Lithium

Recommended Posts

At first, let me give a small intro about myself.

I am Pedro, born, raised, and living Portugal. I work in IT related jobs since 93. Currently i work as a sysadm at one of the major ISP's around here and am in charge of near 500 servers, with a few different flavours. My knowledge on programming is limited, and i do not intend at this stage of my life, to increase it further more, only on a job needed base and that's all. As of DB's i work mainly with Oracle and SQL. And this is the reason that lead me to write these lines, not to teach but to give a tiny small approach on MySQL capabilities.

As the title says, the use of crons is the most widely use because mainly the limitations of the offered DB solution by the hosting companies (MySQL).

What if you could create jobs from inside the DB itself to make the work that usually you would be doing by the use of crons or even timestamped events?

Well as of MySQL 5.1 you can do it. Jobs on MySQL are simply called events, and they work EXACTLY the same way as your crons.

I will ilustrate with a small example, but remember that only MySQL 5.1 or higher is capable to do this. (xampp releases already have MySQL 5.1.30 (v1.7.0))

 

CREATE TABLE IF NOT EXISTS `table` (
 `tID` int(11) NOT NULL AUTO_INCREMENT,
 `tDATETIME` datetime NOT NULL,
 UNIQUE KEY `tID` (`tID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

And after

CREATE EVENT gettime
ON SCHEDULE EVERY 1 MINUTE DO
INSERT INTO test.table values ('',current_timestamp)

 

Now let's enable the event

set global event_scheduler = 1;

 

As you can see the semanthics is very similar to what you are already used to...

And 9 minutes later


SELECT * FROM test.table

1 	2009-03-28 18:12:17
2 	2009-03-28 18:13:17
3 	2009-03-28 18:14:17
4 	2009-03-28 18:15:17
5 	2009-03-28 18:16:17
6 	2009-03-28 18:17:17
7 	2009-03-28 18:18:17
8 	2009-03-28 18:19:17
9 	2009-03-28 18:20:17

 

So far so good, but you might be asking... what limitations am i going to find?

You will not be able to have $vars stucked there, as it only works with plain db calls, meaning that if you are thinking on using it let's say on a game engine like mccodes... you will need to edit it a lot.

I'll give a practical example on a query i have running on my own game that is using events (yes this is the names for mysql jobs).

I have redrawn the bank system to it's own table and have the event running once a week, and you might be asking how do i make calculations on interest right? i have an extra field that shows the users bank interest (my system allows about 7 different interest rates) so the only thing i need is to call the field to make interest calculations and the query would be set like this.

 

CREATE EVENT bank_interest
ON SCHEDULE EVERY 1 WEEK DO
UPDATE uBANK SET bMONEY = bMONEY + floor( bINTEREST * bMONEY / 100 ) WHERE bMONEY > 100

 

Hope this is usefull, and if you need any assistance, feel free to ask, even if you think "its a dumb question", as someone here told me a few days ago "everyone is born naked" ;)

And finally... the resource on MySQL website can be found here...

http://dev.mysql.com/tech-resources/art ... vents.html

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

I've placed 2 examples... 1 for minute 1 for week... so read a bit and figure out the rest, as i said, semanthics is pretty much just like the normal SQL except for a few bits which are pretty much easy caught to who is used to handle these crons and the SQL statements. I'm pretty much certain that whoever reads this article, is capable, without further reading to drop an example on how to refill any stat :)

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

Very good way of doing so. You will also encounter fields like ages and dates which also don't need a cron, just use a timestamp then you can calculate from that, but for replacement of crons this is a nice idea. I will have check the method out.

Have you tried any tests out to see the differences in speed??

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

 

Very good way of doing so. You will also encounter fields like ages and dates which also don't need a cron, just use a timestamp then you can calculate from that, but for replacement of crons this is a nice idea. I will have check the method out.

Have you tried any tests out to see the differences in speed??

A few, and i must say that it is definetly "the future" if properly applied. I am also already used to this line of updating, though not on MySQL, where best practices do suggest this is the best method. Also, when changing servers, you don't need 2 or more operations, you simply backup the db, as events are associated, they come along, saving you the hassle of "redoing" all the cron jobs :)

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

Just started playing around with this.....Its very simple stuff. I noticed if you choose every 5 minutes its true every 5 mintues to the time you started it. So it wont be on the 5s of like 2:05 , 2:10, but you can fix that by editing the time of it in phpmyadmin and making it on those time.....also when you make one it executes it then..Obvously to set the time stamp to know when to run again. This can very very handy.

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

 

Just started playing around with this.....Its very simple stuff. I noticed if you choose every 5 minutes its true every 5 mintues to the time you started it. So it wont be on the 5s of like 2:05 , 2:10, but you can fix that by editing the time of it in phpmyadmin and making it on those time.....also when you make one it executes it then..Obvously to set the time stamp to know when to run again. This can very very handy.

Not quite like that Bama ;) You can start an event at a fixed time, making the event trigger at the correct time like 00, 05, 10, 15 etc. How?

Like this...

 

CREATE EVENT gettime
ON SCHEDULE EVERY 1 MINUTE
START AT '2009-04-01 00:00:00'
DO
INSERT INTO test.table values ('',current_timestamp)

 

This allows to run the event at every minute starting from that date. If you don't specify the START, it will be recurring, according the time that you enable the event with the "set global event_scheduler = 1;" instruction.

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

I have been running the employment/job feature in my game without crons for quite sometime. It works excellent. They punch in to go to work and get paid for time work etc. No need for crons, and it works great. Just my 2 cents.

Link to comment
Share on other sites

Re: Crons, Timestamp or none?

 

I have been running the employment/job feature in my game without crons for quite sometime. It works excellent. They punch in to go to work and get paid for time work etc. No need for crons, and it works great. Just my 2 cents.

Same here, though not with mysql, nor game related ;)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...