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

Software Support
#6

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
#7

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?

DB ERROR value too long for type character varying(64)
#8

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
#9

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.

#10

Hi @Adriano_Jose,

Great, glad to hear it!

Regards,
Alec Smecher
Public Knowledge Project Team

#12

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
#13

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

#14

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

#15

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

Problem after upgrading to OJS 3.2.0-2. Crossref Plugin and another errors
#16

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

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

#17

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

#18

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

#19

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;
#20

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?

#21

Sorry, it is not the same error as I said

#22

Hi @fsf,

It appears as though your OJS 3.0.2 database has entries in the submissions table with a null locale. That’s a data error – there should always be a locale set. I’d suggest reviewing your database and fixing it before running the upgrade.

Regards,
Alec Smecher
Public Knowledge Project Team

#23

Hi @asmecher

Thank you for your reply.
Now I have this error:
"
[note: docs/release-notes/README-3.1.2]
[data: dbscripts/xml/upgrade/event_log_oneclickuserid.xml]
[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]
ERROR: Upgrade failed: DB: ERROR: null value in column “setting_type” violates not-null constraint
DETAIL: Failing row contains (47, , enableOai, 1, null).
"

I’m investigating. If you have any tips I will really be grateful :slight_smile:

#24

Hi @fsf,

That’s the same issue as the top of this thread: Error when updating from 3.1.2-4 to 3.2.0-1 with postgres - #2 by asmecher

Regards,
Alec Smecher
Public Knowledge Project Team

#25

Hi @asmecher

Thanks a lot for you help.

I could succesful migration from base version 3.0.2.0 to version 3.2.1.1

Now I am facing a new problem
Seem that OJS search tools it is not work at all, even after tools/rebuildSearchIndex.php
I do not know if I can talk about this issue here or open a new topic

Thanks for help again.
Kind regards,
@fsf

#26

Hi @fsf,

Great, that’s progress! Please do post your other question as a new topic; that’ll help keep the forum organized.

Regards,
Alec Smecher
Public Knowledge Project Team