DB ERROR: ERROR: INVALID INPUT SYNTAX FOR INTEGER in submission

Hello,
I can not understand why I get the error

DB ERROR: ERROR: INVALID INPUT SYNTAX FOR INTEGER: ""

when I try to save a submission via quick submit plugin and at the step 1 of submission process in my OJS 2.4.1 using Postgres.

Some day ago I’ve installed the PDF.js plugin and upgraded the db aftper the plugin installation; there wasn’t other operations on the files and DB.

In the error log I can not get any details about the error.

How can I solve the problem?

Thanks,
Andrea

Hi @marchitelli,

That sounds like a code problem – you should be able to get more information by turning on the show_stacktrace option in config.inc.php.

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks, Alec

Here the stacktrace, but I can not figure what is wrong:

DB Error: ERROR: invalid input syntax for integer: ""

Stack Trace:

File: /var/www/ojs/lib/pkp/classes/db/DAO.inc.php line 541
Function: DAO->update("DELETE FROM article_settings WHERE article_id = ? AND setting_na...", Array(8))

File: /var/www/ojs/classes/article/ArticleDAO.inc.php line 76
Function: DAO->updateDataObjectSettings("article_settings", Object(Article), Array(1))

File: /var/www/ojs/classes/article/ArticleDAO.inc.php line 218
Function: ArticleDAO->updateLocaleFields(Object(Article))

File: /var/www/ojs/classes/author/form/submit/AuthorSubmitStep1Form.inc.php line 160
Function: ArticleDAO->insertArticle(Object(Article))

File: /var/www/ojs/pages/author/SubmitHandler.inc.php line 154
Function: AuthorSubmitStep1Form->execute()

File: (unknown) line (unknown)
Function: SubmitHandler->saveSubmit(Array(1), Object(Request))

File: /var/www/ojs/lib/pkp/classes/core/PKPRouter.inc.php line 370
Function: call_user_func(Array(2), Array(1), Object(Request))

File: /var/www/ojs/lib/pkp/classes/core/PKPPageRouter.inc.php line 266
Function: PKPRouter->_authorizeInitializeAndCallRequest(Array(2), Object(Request), Array(1), False)

File: /var/www/ojs/lib/pkp/classes/core/Dispatcher.inc.php line 133
Function: PKPPageRouter->route(Object(Request))

File: /var/www/ojs/lib/pkp/classes/core/PKPApplication.inc.php line 189
Function: Dispatcher->dispatch(Object(Request))

File: /var/www/ojs/index.php line 63
Function: PKPApplication->execute()

It is an OJS 2.4.1 on Postgres

Hi @marchitelli,

It sounds like you’ve been running OJS 2.4.1 for a while, but are suddenly encountering problems adding articles. Are you sure you haven’t introduced an accidental change in the process to ArticleDAO.inc.php or DAO.inc.php recently? I’d suggest comparing those against the stock versions from OJS 2.4.1.

What’s confusing me is that the broken function is fairly central to OJS, so I’m wondering why you’d encounter this unexpectedly on an established installation.

Regards,
Alec Smecher
Public Knowledge Project Team

This is the same reason because I am confusing, too!

ArticleDAO.inc.php and DAO.inc.php recently?
Are the same that in ojs 2.4.1 base Version

Hi @marchitelli,

I wonder whether this is related to the problem you were having earlier with notifications. It appears that PostgreSQL isn’t properly assigning article IDs for newly inserted article entries; there should be a sequence used to do that. Can you check that the sequence is working properly? Did you have to do something in particular to get notifications going again? The articles table might need the same treatment.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher, thanks for the replies.

I checked all files and it seems there is any difference with the 2.4.1 ones.
The only modification that I found is the plugin PDF.js. After I installed the plugin, I launched the util/upgrade.php upggrade tool and I think it may have caused the error.

I tried to reset the sequences in articles table as I did for notifications, but with no results.

I’ve noticed that when a submission is started, the error is in step 1, but the submission is saved and if the author click to modify the just started submission, he can finish the steps later.

Andrea

Hi @marchitelli,

Hmm, I still suspect that sequence as the prime candidate. Can you send me a DB dump?

Regards,
Alec Smecher
Public Knowledge Project Team

Ho @asmecher and thanks for reply.
We inspected better the problem and we found a lot of broken sequences:

  • article_galleys_g
  • alley_id_seq1
  • article_supplementary_files_supp_id_seq1
  • articles_article_id_seq1
  • issues_issue_id_seq1
  • notifications_notification_id_seq1

In the upgrade process there are old sequences that are moved to the new ones (the *1 sequences listed above) but these new sequences start from 0 and not from the last number of the old sequence.

Maybe the problem is related only to postgres.

Since we are planning a massive upgrade of our OJS to 2.4.6 I am wondering if the problem is still unsolved.

Best regards,
andrea

Hi @marchitelli,

It’s certainly a PostgreSQL-specific issue, since PostgreSQL manages sequences for auto-increment columns in its own peculiar way. ADODB attempts to abstract these differences away from the application, but doesn’t always do a good job. Can you replicate the sequence breakage by performing some action with OJS, like running the upgrade script? If you can show how the sequences got broken, that would help give me a place to start debugging.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,
the problem is the same that in DB Error: ERROR: duplicate key value violates unique constraint "notifications_pkey"

During the upgrade script some sequences were duplicated. I can’t get where during the upgrade. We solved with this sql

-- ALTER TABLE notifications ALTER COLUMN notification_id SET DEFAULT nextval('notifications_notification_id_seq');
-- ALTER TABLE issues ALTER COLUMN issue_id SET DEFAULT nextval('issues_issue_id_seq');
-- ALTER TABLE article_galleys ALTER COLUMN galley_id SET DEFAULT nextval('article_galleys_galley_id_seq');
-- ALTER TABLE article_supplementary_files ALTER COLUMN supp_id SET DEFAULT nextval('article_supplementary_files_supp_id_seq');
-- ALTER TABLE articles ALTER COLUMN article_id SET DEFAULT nextval('articles_article_id_seq');

and after dropping the duplicated sequences.

Hi @marchitelli,

Thanks for adding the details. Unfortunately I fear this is a consequence of the way ADODB manages PostgreSQL schemas, and unfortunately maintenance and fixes on ADODB are not reliable in the last couple of years. We’re looking for an alternative to it but so far haven’t identified a good candidate.

Regards,
Alec Smecher
Public Knowledge Project Team