MyBB Community Forums - duplicate key value violates unique constraint "mybb_sessions_sid

Hello,
I'm running myBB 1.8.30 on a Linux-x86_64 system with a PosrgreSQL-12.10 backend. In the past few days, I've been seeing this error whenever the Bing (search engine) is crawling my forum:

duplicate key value violates unique constraint "mybb_sessions_sid_key"

I've not been able to reproduce this manually. Is this a known issue?

Full error output is here:

Type: 20
File:  (Line no. 0)
Message
SQL Error: 0 - ERROR:  duplicate key value violates unique constraint "mybb_sessions_sid_key"
DETAIL:  Key (sid)=(bot=2) already exists.

Query: INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0)
Back Trace: #0  errorHandler->email_error(20, SQL Error: 0 - ERROR:  duplicate key value violates unique constraint "mybb_sessions_sid_key"
DETAIL:  Key (sid)=(bot=2) already exists.

Query: INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0), , 0) called at [/var/www/html/dv/inc/class_error.php:229]
#1  errorHandler->error(20, Array ([error_no] => 0,[error] => ERROR:  duplicate key value violates unique constraint "mybb_sessions_sid_key"
DETAIL:  Key (sid)=(bot=2) already exists.
,[query] => INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0))) called at [/var/www/html/dv/inc/db_pgsql.php:585]
#2  DB_PgSQL->error(INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0), Resource id #7) called at [/var/www/html/dv/inc/db_pgsql.php:330]
#3  DB_PgSQL->query(INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0), 0, 1) called at [/var/www/html/dv/inc/db_pgsql.php:355]
#4  DB_PgSQL->write_query(
                        INSERT
                        INTO mybb_sessions (uid,sid,time,ip,location,useragent,location1,location2,nopermission)
                        VALUES (0,'bot=2',1652188516,'\x9d37273a','/dv/showthread.php?tid=26013','Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',7,26013,0)
                ) called at [/var/www/html/dv/inc/db_pgsql.php:784]
#5  DB_PgSQL->insert_query(sessions, Array ([uid] => 0,[sid] => 'bot=2',[time] => 1652188516,[ip] => '\x9d37273a',[location] => '/dv/showthread.php?tid=26013',[useragent] => 'Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',[location1] => 7,[location2] => 26013,[nopermission] => 0), ) called at [/var/www/html/dv/inc/db_pgsql.php:1373]
#6  DB_PgSQL->replace_query(sessions, Array ([uid] => 0,[sid] => bot=2,[time] => 1652188516,[ip] => '\x9d37273a',[location] => /dv/showthread.php?tid=26013,[useragent] => Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm),[location1] => 7,[location2] => 26013,[nopermission] => 0), sid, ) called at [/var/www/html/dv/inc/class_session.php:556]
#7  session->create_session() called at [/var/www/html/dv/inc/class_session.php:473]
#8  session->load_spider(2) called at [/var/www/html/dv/inc/class_session.php:104]
#9  session->init() called at [/var/www/html/dv/global.php:49]
#10 require_once(/var/www/html/dv/global.php) called at [/var/www/html/dv/showthread.php:28]

thanks!

Still seeing this error periodically. I don't understand what I can do to fix this. Seems like a bug.

Yes, this seems to be related to https://github.com/mybb/mybb/issues/4541

Seems like this will be fixed when 1.8.33 is released?

Yes, possibly.

If you'd like to apply the planned fix manually: replace the inc/db_pgsql_pdo.php with the new version, and execute the following SQL queries:

CREATE UNIQUE INDEX fid_uid ON mybb_forumsread (fid, uid);
CREATE UNIQUE INDEX tid_uid ON mybb_threadsread (tid, uid);

This is expected to fix the duplicate key problem when using PostgreSQL >= 9.5.0, and the database type is set to pgsql_pdo in the configuration file:

$config['database']['type'] = 'pgsql_pdo';

(2022-12-27, 03:21 PM)Devilshakerz Wrote: [ -> ]Yes, possibly.

If you'd like to apply the planned fix manually: replace the inc/db_pgsql_pdo.php with the new version, and execute the following SQL queries:
CREATE UNIQUE INDEX fid_uid ON mybb_forumsread (fid, uid);
CREATE UNIQUE INDEX tid_uid ON mybb_threadsread (tid, uid);
This is expected to fix the duplicate key problem when using PostgreSQL >= 9.5.0, and the database type is set to pgsql_pdo in the configuration file:
$config['database']['type'] = 'pgsql_pdo';

My setting is different:

$config['database']['type'] = 'pgsql';

Does that imply that this bug is not what is causing the duplicate key value violations ?

(2022-12-27, 11:12 PM)netllama Wrote: [ -> ]My setting is different:

$config['database']['type'] = 'pgsql';

Does that imply that this bug is not what is causing the duplicate key value violations ?

No - the issue affects forums using PostgreSQL in general.

You should be able to simply change pgsql to pgsql_pdo (and back) without any side effects.

The latter requires the PDO driver for PostgreSQL to be installed on the server (to verify it, you can search for PDO in ACP's Tools & Maintenance → View PHP Info).

The old pgsql may be left out as we intend for MyBB to only use pgsql_pdo for PostgreSQL in the future.