Osticket: DB Error #1062 for `ost_session` table

[fragtion]

Hi,

I'm running off the develop branch, and noticed the following error come to my mailbox while trying to log into the SCP backend: DB Error #1062

[INSERT INTO ost_session SET session_id = 'f43ssoh2mmosvcpe160dls0l13', session_data = 'csrf|a:2:{s:5:\"token\";s:40:\"b04275a23f3dcc955b2e56f5fd319a1374a4fffd\";s:4:\"time\";i:1461838775;}_auth|a:1:{s:5:\"staff\";N;}', session_expire = NOW() + INTERVAL 86400 SECOND, user_ip = '192.168.21.50', user_agent = 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.112 Safari/537.36']

Duplicate entry 'f43ssoh2mmosvcpe160dls0l13' for key 'PRIMARY'


---- Backtrace ----

0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1062', '[INSERT INTO `o...')

1 (root)/include/class.orm.php(3095): db_query('INSERT INTO `os...', true, true)

2 (root)/include/class.orm.php(589): MySqlExecutor->execute()

3 (root)/include/class.ostsession.php(212): VerySimpleModel->save()

4 (root)/include/class.ostsession.php(158): DbSessionBackend->update('f43ssoh2mmosvcp...', 'csrf|a:2:{s:5:"...')

5 [internal function]: SessionBackend->write('f43ssoh2mmosvcp...', 'csrf|a:2:{s:5:"...')

6 [internal function]: session_write_close()

7 {main}

This is accompanied by "Valid CSRF Token Required" errors on the Chrome client. Multiple retries allows me to log in eventually

Problem with my database , or problem with ost? I tried emptying the ost_session table, but still receive this error subsequently

Does not seem to be related to Issues #2770, #2967, #3001, or #304, unless I'm missing something?

Thank you..

[greezybacon]

I've been observing this too

[greezybacon]

My hunch is that it has something to do with #2988, but I don't know why

[greezybacon]

Perhaps the session is not loaded because it is expired, but then it cannot be inserted because there is another record with the same session_id, @protich?

[phamquocbuu]

@greezynacon You can check by do a lot of search, for example search with email, then number, then text... about 5 times or 20 times. Then it will response with 403 error and kick you out with the login form.

[ooglek]

I'm seeing this too. I'm guessing the problem is that the session still exists, but is expired. Nothing is cleaning out expired sessions, so the session is still there, AND the cookie OSTSESSID is still there, and the cookie has NOT expired but the session in the database HAS expired.

In the SQL inserting the Session, it is using session_expire = NOW() + INTERVAL 86400 SECOND but in looking at the cookie expiration, it is set for 2 days (172800 seconds).

./bootstrap.php: define('SESSION_TTL', 86400); // Default 24 hours

/etc/php.ini: session.gc_maxlifetime = 28800

./include/class.osticket.php: $this->session = osTicketSession::start(SESSION_TTL); // start DB based session

./include/class.ostsession.php:

 30     function __construct($ttl=0){
 31         $this->ttl = $ttl ?: ini_get('session.gc_maxlifetime') ?: SESSION_TTL;

Since osTicketSession is calling start() statically, with the SESSION_TTL, which in turn creates a new session, but the SESSION_TTL should be 86400 there. Unless in the __construct() the $ttl is 0, which it seems to be, due to my cookie being set for 2 days, not 1 day.

There is some confusion in the ostsession where you might use the SESSION_TTL and you might use session.gc_maxlifetime. I think the issue is that you should use the min() of either $ttl or SESSION_TTL or session.gc_maxlifetime as long as none of them are 0.

My session.gc_maxlifetime is set higher than anticipated; regardless, it should not cause the DB error and a failure to login. Changing the session.gc_maxlifetime is undesirable due to PHP being used elsewhere and requiring a 2 day lifetime.

One solution -- if I hand in a cookie and the sessionID exists in the DB, ON DUPLICATE KEY UPDATE it if I passed all the requirements and you were gonna add my session anyway.

[ooglek]

This doesn't seem like it got touched/fixed in 1.10. Can it get some love?

[chucks26]

v1.10 Stable (901e5ea) here...same problem!

Cheers,
Chuck26

[rjboer]

Have this as well in both 1.10 test instance and my 1.9.14 production instance.
After each and every login in the 1.10 instance (both agent and client login) -> wrong session key
Every time logged db error & session table (both 1.9.14 and 1.10) contains 283613 entries (and ever increasing)

In 1.9.14, we see this error https://github.com/osTicket/osTicket/issues/2320 a lot, while in 1.10 we notice the 1062 db error...

it might be connected to earlier session issues #3323, #2949,

[FireEmerald]

Got the same error today running v1.10 (901e5ea):

[INSERT INTO `ost_session` SET `session_id` = 'r1nu042bn34q6pj9929oelscp1',
`session_data` = 'csrf|a:2:{s:5:\"token\";s:40:\"c5fb79083a3a4957205e215795db3e3988d25078\";s:4:\"time\";i:1483932243;}',
`session_expire` = NOW() + INTERVAL 86400 SECOND,
`user_ip` = '66.249.76.137',
`user_agent` = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)']

Duplicate entry 'r1nu042bn34q6pj9929oelscp1' for key 'PRIMARY'<br /> <br />
---- Backtrace ----<br />
#0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1062', '[INSERT INTO `o...')<br />
#1 (root)/include/class.orm.php(3133): db_query('INSERT INTO `os...', true, true)<br />
#2 (root)/include/class.orm.php(597): MySqlExecutor->execute()<br />
#3 (root)/include/class.ostsession.php(216): VerySimpleModel->save()<br />
#4 (root)/include/class.ostsession.php(158): DbSessionBackend->update('r1nu042bn34q6pj...', 'csrf|a:2:{s:5:"...')<br />
#5 [internal function]: SessionBackend->write('r1nu042bn34q6pj...', 'csrf|a:2:{s:5:"...')<br />
#6 [internal function]: session_write_close()<br />
#7 {main}

This error never happend before upgrading from 1.9.X to 1.10.

[ooglek]

It happens to me, it is a php session issue where the session still exists, because the session time in the PHP.ini is longer than it is in the OST configuration. But the OST is hardcoded or inflexible or inconsistent about what value it uses, based on my review of the code.

[ooglek]

This is still an issue -- is this going into any fixes?

[rouing]

Still an issue. Any fixes?

[mbnoimi]

I wonder why this bug still exists although @fragtion report it since months ago!

[fdellwing]

To be fair, this only happens if you override the default settings of php!

The default for session.gc_maxlifetime is 1440 (24 mins)

[fragtion]

To be fair, this only happens if you override the default settings of php!
The default for session.gc_maxlifetime is 1440 (24 mins)

?? My /etc/php/7.0/apache2/php.ini contains:

; After this number of seconds, stored data will be seen as 'garbage' and
; cleaned up by the garbage collection process.
; http://php.net/session.gc-maxlifetime
session.gc_maxlifetime = 1440

Yet I still get the error? ...

[fdellwing]

Ok, I think I got a fix for you all.

Edit the include/class.ostsession.php and add the following after Line 86:

setcookie(session_name(),session_id(),time()+$ttl);

This will (hopefully) set the cookie to the same time as in the ost_session. It works for me (now have a 24h cookie instead of 24mins), so if it works for you, I will do a PR.

[fdellwing]

Ignore my last post please.

Ok, there are some things working together here.
The cookie will get set to expire now+agent session timeout

So, if you set this to 0 (disable it) it will set the cookie to now+ttl. So dont do what I wrote in the last post as it will cannibalise this mechanism.

But this means, I dont have a fucking clue what the problem here is. Do you all have configed 2880 mins as agent session timeout or why is this happening?

Please provide me with some of you cookies, matching ost_session row and agent session timeout setting to give me a chance to analyse the problem. (black'en the sessionid!)

[mbnoimi]

So, if you set this to 0 (disable it) it will set the cookie to now+ttl. So dont do what I wrote in the last post as it will cannibalise this mechanism.

I set it to 0. Till now it seems work. Last days ago all the agents suffered from this hideous bug (I created a cron task for empty sessions table every 30 min) and I tried to replace osTicket with other open source solution because this bug really ugly.

[l4ci]

Hey there, Im getting the same error. (Running the latest v1.10)

Settings for auto logout is set to 0.

[INSERT INTO `session` SET `session_id` = 'opcgnf8q990asrv626t5q2kuf6', `session_data` = 'csrf|a:2:{s:5:\"token\";s:40:\"a9b91fdefff42f5f2ab49c21d85a2ab61ba29c5e\";s:4:\"time\";i:1487584161;}', `session_expire` = NOW() + INTERVAL 86400 SECOND, `user_ip` = '66.249.76.82', `user_agent` = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)']

Duplicate entry 'opcgnf8q990asrv626t5q2kuf6' for key 'PRIMARY'<br />
---- Ablaufverfolgung ----<br />
#0 (root)/include/mysqli.php(204): osTicket->logDBError('DB Error #1062', '[INSERT INTO `s...')<br />
#1 (root)/include/class.orm.php(3137): db_query('INSERT INTO `se...', true, true)<br />
#2 (root)/include/class.orm.php(597): MySqlExecutor->execute()<br />
#3 (root)/include/class.ostsession.php(216): VerySimpleModel->save()<br />
#4 (root)/include/class.ostsession.php(158): DbSessionBackend->update('opcgnf8q990asrv...', 'csrf|a:2:{s:5:"...')<br />
#5 [internal function]: SessionBackend->write('opcgnf8q990asrv...', 'csrf|a:2:{s:5:"...')<br />
#6 [internal function]: session_write_close()<br />
#7 {main}

[fdellwing]

I can not reproduce this a bit..

My current session lasts in the moment over 2 days and still counting. I have no clue, how to nail down the problem...

If you have a cookie and a valid session in the ost_session table, he uses update and not insert. So, insert comes from a new login? Do you have to give your credentials before you see this error? When does this error come? After some time? Always? What is you cookie looking at that time?

[l4ci]

This seems to be an error not related to our agents logging in/out - but it seems to be related to some google bot visiting the site (as you can see from the user_agent = 'Mozilla/5.0 (compatible; Googlebot/2.1;)

For us in the frontend everything seems to work just fine. But the logs are getting filled with this error message.

[mbnoimi]

This seems to be an error not related to our agents logging in/out - but it seems to be related to some google bot visiting the site

For me this error occurs with REAL AGENTS

[mbnoimi]

I set it to 0. Till now it seems work. Last days ago all the agents suffered from this hideous bug (I created a cron task for empty sessions table every 30 min) and I tried to replace osTicket with other open source solution because this bug really ugly.

It seems setting timeout to 0 doesn't fix this ugly bug. Even my cron isn't a practical solution... I'm really frustrated :(

I'm unable to leave osTicket easily because I've ton of tasks need to be migrated to the new ticketing system. It seems I'll leave osTicket in favor of OTRS.

[fdellwing]

I still have not seen this error once in over 4 weeks now with v1.10.

[WillPresley]

Just FWIW, I happily migrated from osTicket to MantisBT long ago when it became apparent this bug wasn't getting very much attention. Good luck!

[mbnoimi]

@fdellwing

I still have not seen this error once in over 4 weeks now with v1.10.

This issue occurs on daily basis directly after upgrading process from v1.9.12 to v1.10

@WillPresley

I happily migrated from osTicket to MantisBT long ago

Did you find a script for migrating tasks from osTicket to MantisBT?

[ooglek]

It's the fact that OST is storing session data in MySQL.

1. The Session ID Cookie is set with an expiration of 48 hours.
2. The Session ID in OST is set for 24 hours.
3. After 24 hours, if you try and hit OST, OST believes your session has expired, and tries to create a new one.
4. PHP still sees the session in there and it is active, but OST tries to start a new session with your existing Session ID from the cookie.
5. PHP + MySQL barfs (this is not code in OST but code to store sessions in PHP), and you get the error.

I looked at the code and there is some inconsistency about how long OST believes the session is, but none of them as PHP how long IT thinks the session is. There wasn't an easy way to ensure that OST set session length to the same value as the php.ini, but I suspect that'd be best.

See my post from August 10, 2016 above. Therein lies the problem -- multiple sources of how long a session should last, and if PHP thinks it is longer than OST, you get this error 24 hours after you start a new session.

[fdellwing]

I'm unable to get a cookie that lasts longer as the session in the database. I dont know how you are able to. And no, gc_maxlifetime seems not to be the problem as @fragtion is using the default value and still got this error.

[jaszum]

Hi there,

I'm having the very same problem after upgrading from 1.9.14 to 1.10. Can anyone from dev group take a look at this? - protich? greezybacon? somebody? Thanks in advance.. It's not very annoying in my case and happens only with googlebot - yet I don't like having "DB Error" entries in my logs ;)

Cheers,

josuah08

[phamquocbuu]

I'm tired with debuging this bug. It took many days from me.

I changed INSERT INTO to REPLACE INTO, but the session $_SESSION['_auth']['staff'] still floats into a black hole. Therefore users must re-login.

[phamquocbuu]

Some Update

I commented out this code block, and testing this. The session seem exists and everything is ok.

//// File: class.ostsession.php
//// osTicketSession->osTicketSession()

if ($this->backend instanceof SessionBackend) {
            // Set handlers.
    session_set_save_handler(
        array($this->backend, 'open'),
        array($this->backend, 'close'),
        array($this->backend, 'read'),
        array($this->backend, 'write'),
        array($this->backend, 'destroy'),
        array($this->backend, 'gc')
    );
}

I will try to find out the reason which causes the bug. If cannot, I think the best way is comment out that block forever.

---

Update:

An easy way to fix is disable db session storage by adding this line at the end of ost-config.php file.

define('SESSION_BACKEND', 'system');

[jaszum]

Hi cosmospham,

First of all - thanks for the swift reply and taking care of the problem.

Maybe a good approach would be to check what changed between versions 1.9.14/15 and 1.10 in respect to session handling - just a clue.

I haven't observed this behavior in 1.9.14 and earlier 1.9.x versions (I believe I have started the journey with osTicket with version 1.9.12)

I think - the point is to eliminate the problem without excessive cuts in the code which may harm the functionality or have an impact on the overall system security (hence sessions/session handling is mainly a security-oriented feature after all ;) )

Cheers,

josuah08

[fdellwing]

Am I right with my conclusion, that everyone with this problem updated from 1.9.x to 1.10?

[phamquocbuu]

I use v1.9.12. However my source was added a ton of functions and customize in source code. Therefore I will try to check diff between two version for fixing.

[l4ci]

Using v1.10 (901e5ea) - so yes for me.

[jaszum]

Hi there,
almost clean v1.10 (901e5ea) here :) - some css tweaks due to broken layout - check http://osticket.com/forum/discussion/comment/112479/

p.s. I'm glad that I've managed to push "Fast forward" button and I'm feeling that thing has got some speed now ;)

[jaszum]

Hi,

I see I was a little overoptimistic with this "Fast Forward" button...:(

@cosmospham - did you managed to check diff between v. 1.9.x and 1.10 in respect to session handling ? Do you think that disabling db session storage is the best solution for the case ?

Cheers,
-J

[phamquocbuu]

Hello @jaszum
I do not disable or comment out the code. My solution is change the db storage to raw PHP (session files).

Enable the below line in ost-config.php

define('SESSION_BACKEND', 'system');

[brookemarshassoc]

Don't know if this helps, but I just made an edit to bootstrap.php

Commented out:

define('SESSION_TTL', 1440); // Default 24 hours

and put in:

define('SESSION_TTL', ini_get("session.gc_maxlifetime")); // Default 24 hours

... This way, it will match the current php configuration (so if you do want 24 hour sessions, update your phpconfig to do so)

[ortegacomputacion]

so, for the non initiated, will this last edit fix the system? thanks. just asking. thanks.

[brookemarshassoc]

Try it and find out. That said, there is more than 1 bug in the system. My posted fix shouldn't make it any worse than what it is/was.

[ortegacomputacion]

Thanks, at the end, didn't try, but will try soon, FWIW I was using a 10.04 inside a virtualbox (we all know osticket doesn't work in 16.04), so I said to myself why not try it in 14.04 and see how it goes, and hey, same issue, not so often as in 10.04 but still there, by the way, IMHO I think part of this is related with timings in the network, I had near 900 msec pings between computers (too many wifi hops) and it all went away (so far) when I went wired and got my pings under <1ms . will keep you guys posted,

[LarsFichtel]

I just found this issue, because I am getting it on a new system. Is there any working fix by now? The logoff time is set to 6000 min.
I might try:

define('SESSION_TTL', ini_get("session.gc_maxlifetime")); // Default 24 hours

if nobody else got a working solution.

[marce1582]

is possible error provoked google analytics?

[mauriciopp88]

La solución consiste en iniciar la sesión desde navegación privada "Ctrl +Shift + N" en Google Chrome, una vez ahí, se inicia con normalidad y procedemos a "Cerrar Sesión".

[systemdarena]

I am having this issue occur on average about once a day (sometimes multiple times a day, sometimes I will go a few days without seeing it) and the admin gets an email about it every time it happens, so it is kind of annoying. My "Agent Session Timeout" is set to "0". Both my php.ini session.gc_maxlifetime and ost SESSION_TTL are set to 86400. The system seems to otherwise work fine though...