#740 Browsing a publication list or browsing as a readOnly user randomly bumps back to the login screen

[Mark Grimshaw]

Hi Stéphane,

I've puzzled over this for some time and can't figure it out. Browsing a publication list or logging on as a readOnly user and trying different operations on it I end up, seemingly randomly, getting bumped back to the login page despite the nonauthorised session. This is because the sessionbrowsertab row for the unauthorised user gets removed.

maxlifetime of an anonymous session is set to 59 minutes—the error happens within a minute or two of logging on to viewing the publication list or entering as a readOnly user.

Perhaps you could have a look as I'm finding it difficult to track the path and processes of garbage collection?

I'm using browserTabID—I haven't yet tested with normal sessions.

[Mark Grimshaw]

Normal sessions as a readOnly user seem fine.

With browserTabID, it seems to be something to do with SESSIONHANDLERS::gc() and the condition in the SQL at line 379.

It could be something to do with TEMPSTORAGE line 66.

And I have noticed something curious: a new row in the session table is written with the local(host) timestamp; a new row in session_browser_tab is written with the local(host) timestamp minus 6 hours. The database tables are created with the same timestamp protocols.

Mark

[Stéphane Aulery]

A 6-hour time difference doesn't correspond to your time zone. That's very strange.

[Mark Grimshaw]

Some things I notice (MySQL database):

1. session has type TIMESTAMP for sessionLastAccessTimestamp
2. session_browser_tab_id has type DATETIME for sessionbrowsertabTimestamp
3. session.sessionLastAccessTimestamp keeps track with current local time
4. session_browser_tab.sessionbrowsertabTimestamp lags 6 hours behind current local time
5. session.sessionLastAccessTimestamp . . . update 140 should have modified the type to DATETIME . . . but this did not happen (see 1.)

[Mark Grimshaw]

And the lag of 6 hours also occurs on MariaDB.

Interestingly, resource_timestamp timestamp fields are also datetime and also lag 6 hours behind.

I guess it has something to do with changing timezones on my laptop moving between Denmark and China. timestamp responds to the server timezone setting, datestamp does not.

I notice the mysql 'system time zone' variable is set to CST which is . . . 13 hours behind . . .
. . . whereas the mysql server 'time zone' variable is set to 'SYSTEM' (in effect CST).

Currently, my macBook is set to: Time Zone: Asia/Shanghai

[Stéphane Aulery]

My policy is to use the same date type for all database fields. I also use the time provided by the database server as the source for inserting and updating data whenever possible to ensure consistency. Furthermore, I use automatic date options when creating and updating records. I'm not sure if this is universally applied in the code.

In fact, the time difference between programs isn't a problem if the time zone is properly included in the recorded information. Errors occur when the time is recorded without a time zone and multiple programs interpret it as local time.

[Stéphane Aulery]

session.sessionLastAccessTimestamp . . . update 140 should have modified the type to DATETIME . . . but this did not happen (see 1.)

And if you run it by hand, did it fail?

[Mark Grimshaw]

ALTER TABLE session MODIFY COLUMN sessionLastAccessTimestamp DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp();

run by hand works. So, not sure what happened during update. What is the type on your database?

I will try changing again with update code.

[Stéphane Aulery]

run by hand works. So, not sure what happened during update. What is the type on your database?

Mine is DATETIME for both.

[Stéphane Aulery]

Hi Mark,

As a first step you have only to comment "DELETE" SQL commands in WkxDbSessionHandler\gc(), and test.

If the problem disappears, then we will be certain that the origin is the expiration of sessions in this script.

Regards,

[Mark Grimshaw]

I did something similar by commenting out the line at 396 (execution of the SQL delete statement). Everything works as expected (no booting back to login).

I've been looking more closely at the SQL to delete from session_browser_tab but still puzzling. I think I need a separate SQL for deleting nonAuth rows as a) there is never a usersId in the users table of 0; b) sessionbrowsertabId of 0 simply means this a nonAuth access; so c) there is no need to join the users table: if sessionbroswertabId is 0, delete the row if the timestamp is over the maxSessionNotAuthLifetime.

But I need to look more closely at the previous SQL for DELETE FROM session as, again, there will never be a users.usersId == 0 to match a session.sessionUserId of 0.

I'll look again tomorrow.

[Stéphane Aulery]

The join is LEFT OUTER, not INNER join, so the users table is not required when sessionbroswertabId is 0.

The ELSE case return maxSessionNotAuthLifetime for nonAuth access when sessionbroswertabId is 0.

This query is 3 in one!

[Mark Grimshaw]

Got it!

[Mark Grimshaw]

The random logging out is solved. The issue is now the lag . . .

[Stéphane Aulery]

Your modification introduces a subtle bug. When the database is not accessed $maxSessionAuthRememberMeLifetime, $maxSessionAuthLifetime, and $maxSessionNotAuthLifetime should take the default values from constants. This is done by replacing the NULL value returned by queryFetchFirstField by PHP_INT_MAX, and after that taking the most low value.

I didn't init these variables with 0 because if code is changed or fail in unexpected way, It is better for subsequent queries to crash, rather than emptying the session tables. Because adding 0 to a past date will always be lower than CURRENT_TIMESTAMP().

[Mark Grimshaw]

What about putting this (with a check for pre-existing value) after the two if() statements:

        $maxSessionNotAuthLifetime = $db->queryFetchFirstField("
            SELECT configInt
            FROM config
            WHERE configName = 'configSessionNotAuthMaxlifetime';
        ") ?? PHP_INT_MAX;