Crons, Timestamp or none?

Place to place any code snippets, completed games, or even uncompleted games for IR users to use.
Post Reply
User avatar
Lithium
Posts: 21
Joined: Wed Jan 08, 2014 2:48 pm

Crons, Timestamp or none?

Post by Lithium »

At first, this is my first humble contribution, of an article i published some time ago on other place, and decided to place it here as it might be useful for someone around the community, so bare with me! :)

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.

Code: Select all

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 ;
then...

Code: Select all

CREATE EVENT gettime
ON SCHEDULE EVERY 1 MINUTE DO
INSERT INTO test.table values ('',current_timestamp)
Now let's enable the event (MySQL default value is off (0))

Code: Select all

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

Code: Select all

SELECT * FROM test.table
 
1   2014-01-08 18:12:17
2   2014-01-08 18:13:17
3   2014-01-08 18:14:17
4   2014-01-08 18:15:17
5   2014-01-08 18:16:17
6   2014-01-08 18:17:17
7   2014-01-08 18:18:17
8   2014-01-08 18:19:17
9   2014-01-08 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.

I'll give a practical example on a query i used to have running on my own game that was 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.

Code: Select all

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 told me a few yeas ago "everyone is born naked"

And finally... the resource on MySQL website can be found here... http://dev.mysql.com/doc/refman/5.7/en/ ... event.html

Code: Select all

You laugh at me because I'm different, I laugh at you because you are all the same!
[/size]
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: Crons, Timestamp or none?

Post by Jackolantern »

Interesting! I didn't know MySQL had something like that built in, but it makes sense. Thanks! :)
The indelible lord of tl;dr
User avatar
MikuzA
Posts: 395
Joined: Thu Aug 08, 2013 8:57 am

Re: Crons, Timestamp or none?

Post by MikuzA »

Wow this is good stuff!
Now tick calcs can be done inside MySQL!
Why so serious?

Business Intelligence, Data Engineering, Data Mining
PHP, HTML, JavaScript, Bash/KornShell, Python, C#, PL/SQL
MySQL, DB2, Oracle, Snowflake
Pentaho, DataStage, Matillion, Unity3D, Blender
User avatar
Lithium
Posts: 21
Joined: Wed Jan 08, 2014 2:48 pm

Re: Crons, Timestamp or none?

Post by Lithium »

One "minor" con on this, is either you have control on mysql config, or being the case you have your stuff on a shared host, the possibility to have event scheduler enabled, even as a per user basis (yes you can enable it only for given mysql users, or globally), is near to impossible, as hosting companies do not enable it, but still if this is your case, get in touch with the hosting company, and see if it is possible for them to enable it!

Also, if you have a lot of cron jobs that you actually can replace with this, you will notice a sgnificant improvement on cpu load on the time they run! :)

Code: Select all

You laugh at me because I'm different, I laugh at you because you are all the same!
[/size]
User avatar
MikuzA
Posts: 395
Joined: Thu Aug 08, 2013 8:57 am

Re: Crons, Timestamp or none?

Post by MikuzA »

Ah, yes.

I took some investigation on this and the following>
-If it's in DISABLED-state, it cannot be changed to ON/OFF state without MySQL server restart.
-You need "SUPER"-privileges in order to change the state from ON/OFF.

So I have one webservice where have this in OFF-mode but do not have SUPER privileges.
But I also have an Virtual Server where I can try this out instead of crons.

But there might be cases that webservices allow you to have an 'database of your own', where you have root access. Then you will be able to use this awesome feature :)
Why so serious?

Business Intelligence, Data Engineering, Data Mining
PHP, HTML, JavaScript, Bash/KornShell, Python, C#, PL/SQL
MySQL, DB2, Oracle, Snowflake
Pentaho, DataStage, Matillion, Unity3D, Blender
User avatar
Lithium
Posts: 21
Joined: Wed Jan 08, 2014 2:48 pm

Re: Crons, Timestamp or none?

Post by Lithium »

MikuzA wrote:Ah, yes.

I took some investigation on this and the following>
-If it's in DISABLED-state, it cannot be changed to ON/OFF state without MySQL server restart.
-You need "SUPER"-privileges in order to change the state from ON/OFF.

So I have one webservice where have this in OFF-mode but do not have SUPER privileges.
But I also have an Virtual Server where I can try this out instead of crons.

But there might be cases that webservices allow you to have an 'database of your own', where you have root access. Then you will be able to use this awesome feature :)
Exactly! :) VPS owners can easily enable this. Others... well most likely can on a per-user basis if on a dedicated server, shared hosting almost impossible.

Code: Select all

You laugh at me because I'm different, I laugh at you because you are all the same!
[/size]
Post Reply

Return to “Code Sharing”