Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mySQL Managing a login table

Posted on 2011-03-14
6
Medium Priority
?
278 Views
Last Modified: 2012-05-11
Hi,

I'm building a scheduled task in mySQL that will, every night, run through a table of users who are logged into the system and automatically logoff anyone who's been logged in for 2 weeks.

My current draft runs through the Login table, identifying the users logged in for over 2 weeks and puts their details into a temp table. I then loop through the temp table running the stored proc that does the log-off for each temp table entry.

Can anyone think of a simpler way of doing this? Especially one that doesn't involve using a loop?

Thanks,
John.
0
Comment
Question by:GroganJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 35130310
Is the app using PHP? if so, why not use sessions and set the expiry time to two weeks?
0
 

Author Comment

by:GroganJ
ID: 35130515
I thought about using session timeout, but there are a couple of other events in the DB that are triggered by a user logout. For that reason, I want to control / track it within the DB rather than from php.
0
 
LVL 3

Expert Comment

by:sbickerstaff
ID: 35130622
how are you maintaining / checking that someone is logged in?

1) are you setting the last login time when they log in?
2) have you a field to set that user is active each time they visit a page or do you just use last login time? or how do you check if user is active?

as someone can close the browser without logging out, then obviously they're not active anymore, so you could be forcing a log off that is an un-necessary overhead.

can you give an example of these 'other events' triggered by a log out?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:GroganJ
ID: 35130670
I am tracking when they login and when there is activity. After a predetermined period of inactivity, I want to log them out (for security reasons). At the moment, this is set to 2 weeks, but if when we change that, it's very easy to just update a stored proc and let it run.

The other events that get triggered don't really matter, but it's important that they get run when a user logs out (or is logged out by the system).
0
 
LVL 3

Accepted Solution

by:
sbickerstaff earned 1500 total points
ID: 35130770
From what you've been saying then, in my opinion, whats you're doing at the minute sounds like the only way with one possible ammendment:

instead of putting all records out into a temp table and then processing them again, why not just process these records on the first loop through the database?
0
 

Author Closing Comment

by:GroganJ
ID: 35176723
Thanks for your input on this.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question