OJS 3.1.2.1 issue adding new Category postgres

I’m getting an error using a postgres database after upgrading form OJS 3.1.1.4 to the newest OJS version (downloaded from the website).

I tried to create a new category and the form doesn’t change (the cog icon in the bottom right corner keeps spinning). If I view the network traffic the below error is displayed. I’ve reviewed the database and it looks like the auto increment sequence isn’t being created in postgres. The XML schema under lib/pkp/xml/schema/categories.xml looks like it’s specifying autoincrement but the database does not have the sequence (see second screen shot).

image
image

Also apologies as this is my first post. I’m pretty sure I can fix it by adding a sequence but I’m not sure what naming convention/format I should use to keep it compatible with future versions.

Thanks.

Hi @cpockett,

You’re correct, there should be a sequence there – and it should’ve been automatically created when the table was created; not sure why that didn’t happen. It should be named categories_category_id_seq:

                                        Table "public.categories"
   Column    |          Type          |                            Modifiers                             
-------------+------------------------+------------------------------------------------------------------
 category_id | integer                | not null default nextval('categories_category_id_seq'::regclass)
 context_id  | bigint                 | not null
 parent_id   | bigint                 | not null
 seq         | bigint                 | 
 path        | character varying(255) | not null
 image       | text                   | 
Indexes:
    "categories_pkey" PRIMARY KEY, btree (category_id)
    "category_path" UNIQUE, btree (context_id, path)
    "category_context_id" btree (context_id, parent_id)

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks.

I’ve fixed it with

create sequence categories_category_id_seq;
alter table categories alter column category_id set default nextval(‘categories_category_id_seq’);
UPDATE categories SET category_id = nextval(‘categories_category_id_seq’);

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ojs_user;

Now I’ve got an issue where the category doesn’t seem to show it’s name properly. I might need to re-upgrade and apply fixes :confused:

image

Hi @cpockett,

The category name should be stored in the category_settings table – it’s localized, so there may be an entry for each locale. Not sure why the name wouldn’t be displayed, but that may be a place to start looking.

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks.

category_settings table is empty. I suspect some disconnect when saving categories then if that’s the case. I’ve tried a few and it doesn’t seem to work properly with my current setup.

Hi @cpockett,

I suspect this might be due to the sequence/index configuration. We use ADODB for database abstraction and it makes some assumptions about what the sequence and index are named, and how they need to be created; when a category is created it involves an insert into categories, then a check for the last-inserted category_id, then inserts using that category ID into category_settings. I’d suggest removing your categories, category_settings, and submission_categories tables (assuming they probably don’t have anything you’ll need to keep), then run the following to re-create them:

php tools/dbXMLtoSQL.php -schema execute lib/pkp/xml/schema/categories.xml

You may also need to remove any related sequences.

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks Alex,

It seems like the sequence was still being the issue. The sequence needs to be created by the same user that created the table for it to work properly. I was logging in as the postgres user since it’s, in my view, a higher level operation to make a sequence. Rather than just using the ojs_user that the script uses.

So that being said for completeness the proper fix is

create sequence categories_category_id_seq;
alter sequence categories_category_id_seq owned by categories.category_id;
alter table categories alter column category_id set default nextval(‘categories_category_id_seq’);
UPDATE categories SET category_id = nextval(‘categories_category_id_seq’);

And as a side note rerunning the script still didn’t create the sequence, just so you know.

Hi @cpockett,

Thanks for the thorough follow-up – I did a quick check over here and didn’t have trouble with the sequence creation, so I’m not sure if this is platform-related, version-related, etc. I’d be interested in hearing if others run into this (or if you do on other tables).

Regards,
Alec Smecher
Public Knowledge Project Team

We’re using postgres 9.2 on RedHat 7. We upgrade our previous installation following the upgrade directions after downloading from https://pkp.sfu.ca/ojs/ojs_download/