Error when updating from 3.1.2-4 to 3.2.0-1 with postgres

Hi,
We are encountering the following error during the update:

php tools/upgrade.php upgrade

[pre-install]
[load: upgrade.xml]
[version: 3.2.0.1]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::migrateSubmissionCoverImages]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning_articleGalleySettings.xml (skipped)]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_last_activity.xml]
[schema: lib/pkp/xml/schema/common.xml]
^[[B[schema: lib/pkp/xml/schema/log.xml]
[schema: lib/pkp/xml/schema/announcements.xml]
[schema: lib/pkp/xml/schema/scheduledTasks.xml]
[schema: lib/pkp/xml/schema/temporaryFiles.xml]
[schema: lib/pkp/xml/schema/metadata.xml]
[schema: lib/pkp/xml/schema/reviews.xml]
[schema: lib/pkp/xml/schema/reviewForms.xml]
[schema: lib/pkp/xml/schema/controlledVocab.xml]
[schema: lib/pkp/xml/schema/submissions.xml]
[schema: lib/pkp/xml/schema/submissionFiles.xml]
[schema: lib/pkp/xml/schema/categories.xml]
[schema: lib/pkp/xml/schema/notes.xml]
[schema: lib/pkp/xml/schema/genres.xml]
[schema: lib/pkp/xml/schema/tombstone.xml]
[schema: lib/pkp/xml/schema/rolesAndUserGroups.xml]
[schema: lib/pkp/xml/schema/metrics.xml]
[schema: lib/pkp/xml/schema/views.xml]
[schema: lib/pkp/xml/schema/libraryFiles.xml]
[schema: lib/pkp/xml/schema/navigationMenus.xml]
[schema: dbscripts/xml/ojs_schema.xml]
[data: dbscripts/xml/indexes.xml]

[code: Installer Installer::installDefaultNavigationMenus]
WARNING: The NavigationMenu (ContextId: 66, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 66, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 3, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::migrateUserAndAuthorNames (skipped)]
[data: dbscripts/xml/upgrade/3.2.0_stylesheet.xml]
[data: dbscripts/xml/upgrade/3.2.0_archiving_settings.xml]
[data: dbscripts/xml/upgrade/3.2.0_update.xml]
[data: dbscripts/xml/upgrade/3.2.0_navigation_menu_items_locale_change.xml]

[code: Installer Installer::migrateSiteLocales]

[code: Installer Installer::migrateSidebarBlocks]

[code: Installer Installer::migrateSiteStylesheet]

[code: Installer Installer::migrateMetadataSettings]
PHP Fatal error: Uncaught Exception: DB Error: ERROR: null value in column “setting_type” violates not-null constraint
DETALHE: Failing row contains (7, , coverage, request, null). Query:
INSERT INTO journal_settings (
journal_id,
locale,
setting_name,
setting_value
) VALUES (?, ?, ?, ?) in /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres8), ‘\n\t\t\t\t\t\tINSERT I…’)
#1 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(941): DAO->update(’\n\t\t\t\t\t\tINSERT I…’, Array)
#2 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(417): Installer->migrateMetadataSettings(Object(Upgrade), Array)
#3 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /var/www/ojs-3.2.0-1/lib/pkp/classes/cliTool/ in /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php on line 703
You have new mail in /var/spool/mail/root

We use postegres 7

could you help us?

Hi @Adriano_Jose,

It looks like the ADODB library (which OJS relies on for schema management) is failing to drop the NOT NULL constraint on the journal_settings table. You can work around this by running the following query before the upgrade script:

ALTER TABLE journal_settings ALTER COLUMN setting_type DROP NOT NULL;

Regrads,
Alec Smecher
Public Knowledge Project Team

1 Like

Hi @asmecher,
thank you very much for your very quick response. I am now encountering the following error:

[code: Installer Installer::migrateSiteLocales]
[code: Installer Installer::migrateSidebarBlocks]
[code: Installer Installer::migrateSiteStylesheet]
[code: Installer Installer::migrateMetadataSettings]
[code: Installer Installer::createLicenseTerms]
PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  syntax error at or near "SET"
LINHA 1: INSERT INTO journal_settings SET
                                      ^ Query:
                                INSERT INTO journal_settings SET
                                        journal_id = ?,
                                        locale = ?,
                                        setting_name = ?,
                                        setting_value = ?
                                 in /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres8), '\n\t\t\t\tINSERT INT...')
#1 /var/www/ojs-3.2.0-1/classes/install/Upgrade.inc.php(2952): DAO->update('\n\t\t\t\tINSERT INT...', Array)
#2 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->createLicenseTerms(Object(Upgrade), Array)
#3 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /var/www/ojs-3.2.0-1/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /var/www/ojs-3.2.0-1/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer->execute()
#6 /v in /var/www/ojs-3.2.0-1/lib/pkp/classes/db/DAO.inc.php on line 703

Hi @Adriano_Jose,

Thanks, it looks like some non-PostgreSQL-compatible SQL crept into the upgrade script. I’ve filed this for the next release at Upgrade error using PostgreSQL in createLicenseTerms · Issue #5701 · pkp/pkp-lib · GitHub. You can resolve it by applying this change: pkp/pkp-lib#5701 Fix Upgrade::createLicenseTerms for PostgreSQL · pkp/ojs@95d1d0c · GitHub

Please confirm whether that helps!

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like

Hi @asmecher,
we are progressing.
Now, I encountered the following error:

Blockquote [code: Installer Installer::installEmailTemplate]

[code: Installer Installer::changeUserRolesAndStageAssignmentsForStagePermitSubmissionEdit]
[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]
ERROR: Upgrade failed: DB: ERROR: null value in column “setting_type” violates not-null constraint
DETALHE: Failing row contains (47515, , country, , null).
You have new mail in /var/spool/mail/root

Another similar error,
I found it early in the update:

[data: dbscripts/xml/upgrade/3.2.0_archiving_settings.xml]
[data: dbscripts/xml/upgrade/3.2.0_update.xml]
ERROR: Upgrade failed: DB: ERROR: null value in column “email_id” violates not-null constraint
DETALHE: Failing row contains (null, pt_BR, subject, Avaliação da edição de texto concluída).
You have new mail in /var/spool/mail/root

I noticed that in the \var\www\ojs-3.2.0-1\dbscripts\xml\upgrade\3.2.0_preupdate_email_templates.xml file,
on line 34, for some reason it was not running properly, I think.

UPDATE email_templates_data SET email_id=email_templates.email_id FROM email_templates WHERE email_templates_data.email_key = email_templates.email_key AND email_templates_data.assoc_type = email_templates.assoc_type AND email_templates_data.assoc_id = email_templates.assoc_id

So, I analyzed the keys for each line and made a command for each email_id, like this:

UPDATE email_templates_data SET email_id = 7 WHERE email_templates_data.email_key = ‘REVIEW_REQUEST’ AND email_templates_data.assoc_id = 20 ; COMMIT;

Then, after starting the installation, I manually run this script to fill in the entire email_id column.
What could also be done to improve this?

Hi @Adriano_Jose,

The error:

[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]
ERROR: Upgrade failed: DB: ERROR: null value in column “setting_type” violates not-null constraint
DETALHE: Failing row contains (47515, , country, , null).

…is similar to the one you posted above, but this time for the author_settings table instead of journal_settings. It is again caused by ADODB not dropping a constraint. It can be resolved by executing this query before running the upgrade script:

ALTER TABLE author_settings ALTER COLUMN setting_type DROP NOT NULL;

For this error:

[data: dbscripts/xml/upgrade/3.2.0_update.xml]
ERROR: Upgrade failed: DB: ERROR: null value in column “email_id” violates not-null constraint
DETALHE: Failing row contains (null, pt_BR, subject, Avaliação da edição de texto concluída).

…this looks like a data error. You either appear to have entries in email_templates without an email_id being set (this should be impossible due to constraints), or you have entries in email_templates that don’t have corresponding entries in email_templates_data by the conditions in the above query. If you haven’t customized those templates, the best solution is probably to remove the rows that correspond with the error from email_templates_data.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like

Hi @asmecher,
this worked.

Now, I encountered the following error:

[data: dbscripts/xml/upgrade/3.2.0_last_activity.xml]
[data: dbscripts/xml/upgrade/3.2.0_url_path.xml]
ERROR: Upgrade failed: DB: ERROR: value too long for type character varying(64)

could you help me?

Hi @Adriano_Jose,

It sounds like you have a publisher ID that’s too long. What do you get for the following query (on your OJS 3.1.2-4 database)?

SELECT setting_value FROM submission_settings WHERE setting_name='pub-id::publisher-id' AND LENGTH(setting_value) >= 64;

[edit: see this note on the query]

Regards,
Alec Smecher
Public Knowledge Project Team

2 Likes

We found the record with more than 64 characters in the submission_galley_settings table.

Now it worked. The installation was completed successfully. Thank you very much.

Hi @Adriano_Jose,

Great, glad to hear it!

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @Adriano_Jose,

Is this the same issue you posted here? Problem after upgrading to OJS 3.2.0-2. Crossref Plugin and another errors

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like

Sorry, it really is very similar
I will deleted this.

in fact, I wasn’t finding this topic, so I thought I hadn’t published it

Hi Alec,

This is Ahmed from the university of Oslo
You directed me to this thread since we were experiencing this similar problem:
ERROR: Upgrade failed: DB: ERROR: null value in column “email_id” violates not-null constraint
The problem is when i remove the particular row from the email_templates_data table and restart the upgrade there are more and more similar errors. I have removed about 11 such rows, each time resetting the database and restarting the upgrade process.
Is there some easier way to bypass this step and add it later after upgrade, since i fear there are many more such rows with similar errors.
Thanks

Ahmed

Hi @ahmed.maxamed,

I solved this problem as follows, before starting the update I made this select:

 select email_id, email_key, assoc_id from email_templates order by email_id asc;

So, with the result, I put it in excel and for each row of the result in this table, I edited it to update it during the installation process.

For example, in the first and second lines row:

 '1', 'COPYEDIT_ACK', '7'
 '2', 'COPYEDIT_ACK','9'

I updated it like this:

UPDATE email_templates_data SET email_id = 1 WHERE email_templates_data.email_key = 'COPYEDIT_ACK' AND email_templates_data.assoc_id = 7; COMMIT;
UPDATE email_templates_data SET email_id = 2 WHERE email_templates_data.email_key = 'COPYEDIT_ACK' AND email_templates_data.assoc_id = 9; COMMIT;

I did this for all the rows in the table.
Right after starting the update, I ran all these lines and this problem was solved.

I did this, because line 34 of the file
ojs-3.2.0-1/dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml:

 <query driver = "postgres7"> UPDATE email_templates_data SET email_id = email_templates.email_id FROM email_templates WHERE email_templates_data.email_key = email_templates.email_key AND email_templates_data.assoc_type = email_templates.assoc_type AND email_templates_data. email_templates_data.

it didn’t seem to be generating the correct result. Everything was still null, but the information was there.
Detail, in my table all records had the same value in the email_key column, so I ignored this data. Checks if you have more than one value in that column as well.

Hope this helps.
Regards,
Adriano Moreno

Hi @ahmed.maxamed,
set your driver in config.inc.php with postgres7.

My driver was only postgres and so it gave an error.

Hi Adriano,

thanks for the help. We finally got it working. We tried your solution but this query resulted in many rows over 100. So we had to find another way of solving this.
We removed these lines from the file 3.2.0_preupdate_email_templates.xml
ALTER TABLE email_templates_data
ADD email_id BIGINT

UPDATE email_templates_data SET email_id=email_templates.email_id FROM email_templates WHERE email_templates_data.email_key = email_templates.email_key AND email_templates_data.assoc_type = email_templates.assoc_type AND email_templates_data.assoc_id = email_templates.assoc_id

After that we ran the above queries through the database. We then ran the upgrade process and got a successful result.
@asmecher i hope this work around is alright and will not have unintended problems.

Regards
Ahmed

Hi @ahmed.maxamed,

See this post I left today on another thread:

That query won’t work without adaptation in a PostgreSQL environment, but it should help you identify the problem data.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,
change this sql:

SELECT setting_value FROM submission_settings WHERE setting_name='pub-id::publisher-id' AND LENGTH(setting_value) >= 64;

for this to avoid doubts in other queries:

SELECT setting_value FROM submission_galley_settings WHERE setting_name='pub-id::publisher-id' AND LENGTH(setting_value) >= 64; 

Dear all

I’m trying to this upgrade:

Code version: 3.2.1.1
Database version: 3.0.2.0
Latest version: 3.2.1.1

After run “php tools/upgrade.php check” I have this error message?

"
PHP Fatal error: Uncaught Exception: DB Error: ERROR: null value in column “locale” violates not-null constraint
DETAIL: Failing row contains (31992, null, givenName, Angelica, string). Query: INSERT INTO author_settings (author_id, locale, setting_name, setting_value, setting_type) SELECT DISTINCT a.author_id, s.locale, ?, a.first_name, ‘string’ FROM authors_tmp a, submissions s WHERE s.submission_id = a.submission_id in /var/www/html/ojslatestb/lib/pkp/classes/db/DAO.inc.php:703"

Seems the same error but in different table.
Any ideias?
Should I open a new topic?

Sorry, it is not the same error as I said