How to deal with an unexpected shutdown in MySQL to ensure database consistency?

Question

I have this "tbluser" from the database (MySQL) wherein it stores the user account information such as username and password etc. It also contains a boolean (type TINYINT) column wherein if the user is logged in, the boolean column turns to value of "1", and logged out turns to value "0" so that I can easily identify users who are online or not.

My problem is, what if the user already logs in; then there is an electricity interruption or unusual shutdown occurs. I want that boolean column or that user to automatically turns to "0" so that when there's already an electricity, the user can log in again.

Is that possible? I'm thinking of about a ROLLBACK COMMIT, but maybe someone has another idea?

I'd suggest using some kind of session/token mechanism instead. Significantly more reliable and secure (particularly if this is a web application). — CollinD, Feb 01 '17 at 16:11
@CollinD Im creating a desktop application with VS 2010, vb.net... not web — Ivan Chiong, Feb 01 '17 at 16:14
You may add a good UPS (uninterruptible power supply) to protect from power outages. — Yoan, Feb 01 '17 at 16:15
@Yoan it may do so, but assuming that there is no budget to buy UPS.. hehehe.. i want the system can detect if there is an unusual shutdown or electricity interruption.. hehehe — Ivan Chiong, Feb 01 '17 at 16:19
You're probably better at creating a form that shows you who is logged in and allows admins to unlock users. It's not something you can detect I don't think. — Bugs, Feb 01 '17 at 16:19
@Jinx88909 you mean adding some buttons into the form like refresh button in order for the user can log in again? — Ivan Chiong, Feb 01 '17 at 16:23
That's up to you how you want to see it. We use an application (not written by us) that has an admin side which allows us to view who is logged in. The application detects when it's lost network connectivity and then creates a "crashed lock" which we can then clear. However the software does hold some sort of session for each login as it allows them to log back in and continue working. If however they have been in a record that they've locked, that won't unlock until we've cleared the "crashed lock". — Bugs, Feb 01 '17 at 16:27

score 0 · Answer 1 · edited May 23 '17 at 11:45

The only way that you will have some sort of control over whether the user is logged in or not will be making use of system resources which I think will be unnecessary, but it is do-able.

You need to track the users idle time, which will not necessarily means that their pc crashed, they are just not doing anything inside your app for a certain time period. Me personally will get agitated if you will log me out and i have to login again just because I went for a smoke break.

If user is idle you can log him out, change your field to 0 and force them to log back in. It will however work if their electricity fails for some or other reason and they have been idle.

You can have a look at THIS link in Stackoverflow. You will also find a nice code sample from MSDN HERE explaining idle time tracking.

Should the link die for some reason for future readers, here is the extracts and code from MSDN - showing access but easily converted to .net -

This topic shows how to create a procedure that will run if your Access application does not detect any user input for a specified period of time. It involves creating a hidden form, DetectIdleTime, which keeps track of idle time. Follow these steps to create the DetectIdleTime form.

Create a blank form that is not based on any table or query and name it DetectIdleTime. Set the following form properties:

Property

Value OnTimer [Event Procedure] TimerInterval 1000 ##

Enter the following code for the OnTimer property event procedure:

Sub Form_Timer() 
     ' IDLEMINUTES determines how much idle time to wait for before 
     ' running the IdleTimeDetected subroutine. 
     Const IDLEMINUTES = 5 

     Static PrevControlName As String 
     Static PrevFormName As String 
     Static ExpiredTime 

     Dim ActiveFormName As String 
     Dim ActiveControlName As String 
     Dim ExpiredMinutes 

     On Error Resume Next 

     ' Get the active form and control name. 

     ActiveFormName = Screen.ActiveForm.Name 
     If Err Then 
        ActiveFormName = "No Active Form" 
        Err = 0 
     End If 

     ActiveControlName = Screen.ActiveControl.Name 
        If Err Then 
        ActiveControlName = "No Active Control" 
        Err = 0 
     End If 

     ' Record the current active names and reset ExpiredTime if: 
     '    1. They have not been recorded yet (code is running 
     '       for the first time). 
     '    2. The previous names are different than the current ones 
     '       (the user has done something different during the timer 
     '        interval). 
     If (PrevControlName = "") Or (PrevFormName = "") _ 
       Or (ActiveFormName <> PrevFormName) _ 
       Or (ActiveControlName <> PrevControlName) Then 
        PrevControlName = ActiveControlName 
        PrevFormName = ActiveFormName 
        ExpiredTime = 0 
     Else 
        ' ...otherwise the user was idle during the time interval, so 
        ' increment the total expired time. 
        ExpiredTime = ExpiredTime + Me.TimerInterval 
     End If 

     ' Does the total expired time exceed the IDLEMINUTES? 
     ExpiredMinutes = (ExpiredTime / 1000) / 60 
     If ExpiredMinutes >= IDLEMINUTES Then 
        ' ...if so, then reset the expired time to zero... 
        ExpiredTime = 0 
        ' ...and call the IdleTimeDetected subroutine. 
        IdleTimeDetected ExpiredMinutes 
     End If 
  End Sub

Create the following procedure in the form module:

Sub IdleTimeDetected(ExpiredMinutes) 
     Dim Msg As String 
     Msg = "No user activity detected in the last " 
     Msg = Msg & ExpiredMinutes & " minute(s)!" 
     MsgBox Msg, 48 
  End Sub

How to deal with an unexpected shutdown in MySQL to ensure database consistency?

1 Answers1

Property