Bug #385

"create new forum" generates postgresql error

Added by Michael Schlechtinger about 1 year ago. Updated 12 months ago.

Status:Closed Start:08/20/2009
Priority:Normal Due date:
Assigned to:Tom Moore % Done:

100%
Category:Admin Control Panel
Target version:1.4.10
Reproducibility:Always Browser:N/A
Reported In MyBB Version:1.4.8 Database Type:PostgreSQL
PHP Version:N/A Database Version:8.3.7

Description

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.

Associated revisions

Revision 4442
Added by Tom Moore about 1 year ago

Fixes "create new forum" generates postgresql error (fixes:385)

History

Updated by Michael Schlechtinger about 1 year ago

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

pierce:

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 ?

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

pierce:
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....

laie_techie:
It should have something like:

update mybb_forums_fid_seq set last_value=2

at the end of the installation script.

Updated by Tom Moore about 1 year ago

  • Status changed from New to Resolved
  • % Done changed from 0 to 100

Applied in changeset r4442.

Because of the difference between database types and their acceptance of "NULL" or "DEFAULT", I've added an update sequence query for PgSQL rather than alter the database inserts. This way it should all work on MySQL(i), SQLite and PgSQL.

Updated by Tom Moore about 1 year ago

  • Category set to Admin Control Panel
  • Assigned to set to Tom Moore
  • Target version set to 1.4.9

Updated by Michael Schlechtinger about 1 year ago

  • Status changed from Resolved to Closed

Updated by Ryan Gordon 12 months ago

  • Target version changed from 1.4.9 to 1.4.10

Also available in: Atom PDF