OJS 3.2 Upgrade : ERROR: Upgrade failed: DB: Subquery returns more than 1 row

Rob_Colbourn · March 13, 2020, 11:54am

Hi,

I’m trying to upgrade my OJS installation from 3.1.2 to 3.2 and I’m encountering a problem with the DB upgrade :

[code: Installer Installer::migrateMetadataSettings]

[code: Installer Installer::createLicenseTerms]

[code: Installer Installer::installEmailTemplate]

[code: Installer Installer::changeUserRolesAndStageAssignmentsForStagePermitSubmissionEdit]
[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]
ERROR: Upgrade failed: DB: Subquery returns more than 1 row

I think I just have to specify a LIMIT 1 at the end of the query but I don’t know which subquery is concerned. Do you have any idea ?

Thank’s for you help

asmecher · March 13, 2020, 4:58pm

Hi @Rob_Colbourn,

Hmm, interesting – try turning on the debug option in config.inc.php before restoring your DB from backup and running the upgrade script. This should dump all database queries during the upgrade, so the last one you see before the error message should be the problem query.

Regards,
Alec Smecher
Public Knowledge Project Team

litvinovg · March 23, 2020, 2:20pm

I suppose it’s UPDATE submissions as s SET current_publication_id = ( SELECT p.publication_id FROM publications as p WHERE s.submission_id = p.submission_id );
Faced with it today too.

asmecher · March 23, 2020, 3:48pm

Hi @litvinovg,

That suggests that there is a submission with more than one publication entry, but when the system is upgraded to 3.2, there can only be a single publication per entry – previous versions of OJS didn’t support publications. Before you run the upgrade script, is there a publications table? If so, does it have any contents?

Regards,
Alec Smecher
Public Knowledge Project Team

litvinovg · March 23, 2020, 5:24pm

Hi!
No before upgrade script there is no publications table.

asmecher · March 23, 2020, 5:33pm

Hi @litvinovg,

Hmm, interesting – and are you using OJS 3.2.0 or 3.2.0-1? (I released a 3.2.0-1 build on Friday). If you’re not using the new build yet, I’d suggest trying it.

Regards,
Alec Smecher
Public Knowledge Project Team

litvinovg · March 23, 2020, 5:35pm

I use tarball 3.2.0-1 downloaded today. Could you give me any suggestions where to dig? What classes creating publications table ? Didn’t find that SQL statements yet ))

asmecher · March 23, 2020, 5:47pm

Hi @litvinovg,

That part of the upgrade process is scripted with SQL; the upgrade process overall is described by dbscripts/xml/upgrade.xml, which will show you the sequence the scripts run in. The publications table is described in dbscripts/xml/ojs_schema.xml:

<table name="publications">
    <field name="publication_id" type="I8">
        <KEY />
        <AUTOINCREMENT />
    </field>
    ...
</table>

That schema descriptor is applied to the database in dbscripts/xml/upgrade.xml:

<schema file="dbscripts/xml/ojs_schema.xml" />

You can see some of the other upgrade scripts work with the publications table’s content, populating it from other contents. For example, the main query to populate that table is in dbscripts/xml/upgrade/3.2.0_versioning.xml:

<query>
    INSERT INTO publications (access_status, date_published, last_modified, locale, section_id, seq, submission_id, status, version)
        SELECT COALESCE(ps.access_status, 0), COALESCE(ps.date_published, NULL), s.last_modified, s.locale, s.section_id, COALESCE(ps.seq, 0), s.submission_id, s.status, 1
        FROM temp_submissions as s
        LEFT JOIN temp_published_submissions as ps
            ON (s.submission_id = ps.submission_id)
    GROUP BY s.submission_id, ps.access_status, ps.date_published, s.last_modified, s.section_id, ps.seq, s.locale, s.status;
</query>

Regards,
Alec Smecher
Public Knowledge Project Team

litvinovg · March 24, 2020, 9:16am

Thanks a lot. That one was my mistake: didn’t drop database after first try, so there were temporary tables. After clean start I faced with “Duplicate entry ‘0-1’ for key ‘citations_publication_seq’”. That was on
UPDATE citations as c
SET c.publication_id = (
SELECT s.current_publication_id
FROM submissions as s
LEFT JOIN temp_citations as tc
ON s.submission_id = tc.submission_id
WHERE c.citation_id = tc.citation_id
) ORDER BY c.publication_id ASC
Citations table was only 24 rows long so with deleting all from it upgrade finished successfully.
But that citations UPDATE query isn’t safe IMHO and could cause more troubles as at the time of execution it can create duplicate unique key, which happened in my case. On the other hand it could be that my citations table uniquely broken ))

asmecher · March 24, 2020, 3:50pm

Hi @litvinovg,

Other users reported orphaned data in the citations table that was causing problems; see e.g.: [OMP] Error upgrading from version 3.1.2-4 to 3.2.0 · Issue #5583 · pkp/pkp-lib · GitHub

Regards,
Alec Smecher
Public Knowledge Project Team

wa2nlinux · May 28, 2020, 3:55am

Also facing error upgrade to 3.2.0.3 from 3.1.2.4
[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]
ADOConnection._Execute(
UPDATE submissions as s SET current_publication_id = (
SELECT p.publication_id FROM publications as p WHERE s…, false)% line 1246, file: adodb.inc.php
ADOConnection.Execute(
UPDATE submissions as s SET current_publication_id = (
SELECT p.publication_id FROM publications as p WHERE s…)% line 442, file: Installer.inc.php
Installer.executeSQL(
UPDATE submissions as s SET current_publication_id = (
SELECT p.publication_id FROM publications as p WHERE s…)% line 437, file: Installer.inc.php
Installer.executeSQL(Array[30])% line 395, file: Installer.inc.php
Installer.executeAction(Array[3])% line 263, file: Installer.inc.php
ERROR: Upgrade failed: DB: Subquery returns more than 1 row

asmecher · May 29, 2020, 8:39pm

Hi @wa2nlinux,

Is it possible that your database has already had an upgrade attempted on it? If a previous upgrade attempt failed, make sure you drop the database, create it fresh, then reload from backup before attempting the upgrade again.

Regards,
Alec Smecher
Public Knowledge Project Team

wa2nlinux · June 1, 2020, 7:18am

OK … done

…

#5534: Announcements do not appear after enabling
#5537: Submission::getViews() method missing
#5544: urlPublished refers to article in pkp-lib
#5546: Fatal error when retrieving issues with galleys from API
#5550: Fatal error due to changes in SettingsDAO
#5553: Abstract length error message includes Array instead of correct length
#5560: Files for old versions don't load
#5563: Possible to access unpublished articles when not logged in
#5569: Fix OJS 2.x to 3.x upgrade

Successfully upgraded to version 3.2.0.3

but why all paper are missing ?

here the link
https://lenteradua.net/jurnal3

the old version link is
https://lenteradua.net/jurnal

regards

asmecher · June 3, 2020, 11:41pm

Hi @wa2nlinux,

Would you be willing to share a copy of your pre-upgrade database with me privately? If so, please send me a private message.

Regards,
Alec Smecher
Public Knowledge Project Team

asmecher · June 8, 2020, 10:19pm

Hi @wa2nlinux,

I can see the table of contents as expected after upgrading your database dump to OJS 3.2.0, so I suspect you might want to check any custom work you’ve done, e.g. theme modifications, and ensure that the theme you’re using is up-to-date (it appears to be bootstrap3).

Regards,
Alec Smecher
Public Knowledge Project Team

wa2nlinux · June 9, 2020, 10:41pm

Hi Alec

Thanks for the respon, I don’t have any modification on it. I’ll try to upgrade the theme

regards

Wa2nlinux

blackprinceaj · August 25, 2021, 4:33am

Is there any way if some one lost the database/backup original with 3.1? after failed attempts it changed and not available in actual form now. then what is the solution for upgradation?