MyBB Community Forums

Using pgsql 8.3.7 on CentOS 5.3, on a brand new 1.4.8 test install...

Almost the very first thing I did after finishing the install was to go to Admin CP, Forums, and try and add a new forum, and get...

MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    23505 - ERROR: duplicate key value violates unique constraint "mybb_forums_pkey" 
Query:
    INSERT INTO mybb_forums (name,description,linkto,type,pid,parentlist,disporder,active,open,allowhtml,allowmycode,allowsmilies,allowimgcode,allowpicons,allowtratings,usepostcounts,password,showinjump,modposts,modthreads,mod_edit_posts,modattachments,style,overridestyle,rulestype,rulestitle,rules,defaultdatecut,defaultsortby,defaultsortorder) VALUES ('Another Forum','testing a 2nd forum','','f','1','','2','1','1','0','1','1','1','1','1','1','','1','0','0','0','0','0','0','0','','','0','','') 

I ran into this in my original (now live) forum... apparently the default category and forum were pre-created with a pkey of '1' and '2', but the sequence associated with the SERIAL "fid" was not advanced, so this first INSERT is trying to insert forum #1.

see...

mybbtest=# select fid,name from mybb_forums;
 fid |    name     
-----+-------------
   1 | My Category
   2 | My Forum
(2 rows)

mybbtest=# select * from mybb_forums_fid_seq;
    sequence_name    | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 mybb_forums_fid_seq |          1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t

I'm guessing the 'fix' is to NOT hardcode the fid=1 and fid=2 for the default category/forum (eg, use DEFAULT, or don't insert the field at all), or to manually advance the sequence by invoking select nextval('mybb_forums_fid_seq'::regclass); twice.

We don't hardcode fid=1 or fid=2 in the query - This looks like a PostgreSQL bug to me

(2009-07-02, 09:04 PM)Ryan Gordon Wrote: [ -> ]We don't hardcode fid=1 or fid=2 in the query - This looks like a PostgreSQL bug to me

oh?

install/resources/mysql_db_inserts.php:$inserts[] = "INSERT INTO mybb_forums (fid, name, description, linkto, type, pid, parentlist, disporder, active, open, threads, posts, lastpost, lastposter, lastposttid, allowhtml, allowmycode, allowsmilies, allowimgcode, allowpicons, allowtratings, status, usepostcounts, password, showinjump, modposts, modthreads, modattachments, style, overridestyle, rulestype, rulestitle, rules) VALUES (1, 'My Category', '', '', 'c', 0, '1', 1, 1, 1, 0, 0, 0, '0', 0, 0, 1, 1, 1, 1, 1, 1, 1, '', 1, 0, 0, 0, 0, 0, 0, '', '');";
install/resources/mysql_db_inserts.php:$inserts[] = "INSERT INTO mybb_forums (fid, name, description, linkto, type, pid, parentlist, disporder, active, open, threads, posts, lastpost, lastposter, lastposttid, allowhtml, allowmycode, allowsmilies, allowimgcode, allowpicons, allowtratings, status, usepostcounts, password, showinjump, modposts, modthreads, modattachments, style, overridestyle, rulestype, rulestitle, rules) VALUES (2, 'My Forum', '', '', 'f', 1, '1,2', 1, 1, 1, 0, 0, 0, '0', 0, 0, 1, 1, 1, 1, 1, 1, 1, '', 1, 0, 0, 0, 0, 0, 0, '', '');";

looks hard coded to me ?

Ah, you quoted a different query - I took that as the one you were talking about.

yeah, sorry. i should have been clearer, what I meant was the original insertion queries bypassed the DEFAULT expression for the SERIAL, so the associated sequence didn't get advanced. this is a 'feature' of how pg's SERIAL is implemented (its just an integer field that has an associated sequence, and default nextval() expression....

It should have something like:

update mybb_forums_fid_seq set last_value=2

at the end of the installation script.

Corresponding ticket: http://dev.mybboard.net/issues/385