0

Hi I am quite new to MySQL events and triggers, I have a table called userstatus(userid, timestamp,count, flag) which is used to store data about the user if he's blocked or not. When count reaches 4, the flag becomes 0(blocked). Can you please help me on what I can do to automatically reset the flag to 1 after it remains blocked for an hour. I have tried the following code:

create event testevent
on schedule
every 1 minute
starts CURRENT_TIMESTAMP
do 
update demo.userstatus set flag=1 
where timestampdiff(hour,timestamp,CURRENT_TIMESTAMP)>1

This seems to work but is not efficient. Any help would be appreciated. Thanks!

Apoorva Sinha
  • 1
  • either you have one job that runs/resets all of the relevant flags, or you schedule individual jobs per-user to run one hour after they got blocked. the one job is easier to maintain, but would run more frequently (depending on just how close to 1hour you want to do the unblocking). e.g. if you don't care that a user might be blocked for 1hr59m (e.g. blocked at 9:01am, next unblock runs at 10am, which means they're blocked for only 59 minutes, so you unblock them at 11am). – Marc B Mar 07 '16 at 15:16

1 Answers1

0

It would be a lot more efficient if you added a datetime field to the usersratus table that contains the date and time until a user is blocked, or use the existing timestamp field that stores when the user was blocked rather than having just a flag.

During login or any activity you deem to fit, you just check if the current time is on or earlier than the block time (or on or before timestamp + 1 hour). If yes, then the user is blocked and you can prevent the activity. If not, then the user is not blocked and can allow the activity. You can even restore the counter at the first successful activity to 1.

This way you avoid the need to periodically check the database and clear the status flag.

Shadow
  • 33,525
  • 10
  • 51
  • 64