OMP Upgrade Failed 3.1.2 -> 3.2.1-1

Upgrading from 3.1.2 to 3.2.1-1 with postgres, I get the following error:

/var/www/3.2# php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.2.1.1]

[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_chapters.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_categories.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_pub_id.xml (skipped)]

[code: Installer Installer::migrateSubmissionCoverImages]
PHP Fatal error: Uncaught Exception: DB Error: ERROR: column reference “submission_id” is ambiguous
LINE 7: GROUP BY submission_id
^ Query: SELECT
ps.submission_id as submission_id,
ps.cover_image as cover_image,
s.context_id as context_id
FROM published_submissions ps
LEFT JOIN submissions s ON (s.submission_id = ps.submission_id)
GROUP BY submission_id in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/3.2/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_postgres8), ‘SELECT\n\t\t\t\tps.s…’)
#1 /var/www/3.2/classes/install/Upgrade.inc.php(743): DAO->retrieve(‘SELECT\n\t\t\t\tps.s…’)
#2 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->migrateSubmissionCoverImages(Object(Upgrade), Array)
#3 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /var/www/3.2/lib/pkp/classes/cliTool/Up in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php on line 703

I see that someone has posted a similar error here:
https://forum.ibict.br/t/column-reference-submission-id-is-ambiguous/2703

But there’s no hint on how to move past the error there.

I’m using Ubuntu 18.04 with PHP 7.2 and Postgres 10.12.

Hi @seanc,

Thanks for reporting – I’ve filed and fixed the issue here: [OMP] DB Error: ERROR: column reference “submission_id” is ambiguous · Issue #6179 · pkp/pkp-lib · GitHub
To resolve it, you’ll need to apply the small change linked in the issue, reload your database from backup, and try running the upgrade again.

Regards,
Alec Smecher
Public Knowledge Project Team

Thank you very much, @asmecher. I replied to your email, but it looks like it didn’t go through.

Unfortunately, after changing the file, restoring the db, and running the upgrade again, I get another error:

[code: Installer Installer::migrateSubmissionCoverImages]
PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  column "ps.cover_image" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     ps.cover_image as cover_image,
            ^ Query: SELECT
ps.submission_id as submission_id,
ps.cover_image as cover_image,
s.context_id as context_id
FROM published_submissions ps
LEFT JOIN submissions s ON (s.submission_id = ps.submission_id)
GROUP BY ps.submission_id in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/3.2/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_postgres8), 'SELECT\n\t\t\t\tps.s...')
#1 /var/www/3.2/classes/install/Upgrade.inc.php(743): DAO->retrieve('SELECT\n\t\t\t\tps.s...')
#2 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->migrateSubmissionCoverImages(Object(Upgrade), Array)
#3 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeI in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php on line 703

The installer continued complaining about wanting SELECT fields in the GROUP BY clause until my GROUP BY looked like this (l. 749):

GROUP BY ps.submission_id, ps.cover_image, s.context_id'

I’m not sure if that makes sense in the code, but the installer got past that bit.

However, I now get the following error:

[code: Installer Installer::migrateSubmissionCoverImages]
PHP Notice:  Undefined offset: 1 in /var/www/3.2/classes/install/Upgrade.inc.php on line 762
PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  syntax error at or near "SET"
LINE 2:       SET
              ^ Query: INSERT INTO submission_settings
SET
submission_id = ?,
setting_name = ?,
setting_value = ?,
locale = ? in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/3.2/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres8), 'INSERT INTO sub...')
#1 /var/www/3.2/classes/install/Upgrade.inc.php(820): DAO->update('INSERT INTO sub...', Array)
#2 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->migrateSubmissionCoverImages(Object(Upgrade), Array)
#3 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /var/www/3.2/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /var/www/3.2/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer->execute()
#6 /var/www/3.2/lib/pkp/classes/cliTool/UpgradeTool.inc.php(64): UpgradeTool->upgrade()
#7 /var/www/ in /var/www/3.2/lib/pkp/classes/db/DAO.inc.php on line 703

Hi @seanc,

You’ve run into a couple of cases where the code has not kept PostgreSQL-compatible! Here’s how to fix it:

  • Your change to the GROUP BY is OK; alternately, I’ve reviewed the SQL and that clause is not necessary at all. You can remove it with this change.
  • To make the INSERT statement PostgreSQL-compatible, you can apply this change.

I’ve merged these changes already, but if you can confirm that they work for you, it’ll be a better fix in the next OMP release :+1:

Thanks,
Alec Smecher
Public Knowledge Project Team

I downloaded the source code from the git repository and built it according to the instructions, then ran the upgrade again and got another error:

/var/www/omp$ php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.0]
[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_chapters.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_categories.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_pub_id.xml (skipped)]
[code: Installer Installer::migrateSubmissionCoverImages]
PHP Notice:  Undefined offset: 1 in /var/www/omp/classes/install/Upgrade.inc.php on line 760
PHP Warning:  copy(public/presses/1/submission_1_1_coverImage.png): failed to open stream: Permission denied in /var/www/omp/lib/pkp/classes/file/FileManager.inc.php on line 180
PHP Warning:  copy(public/presses/1/submission_1_1_coverImage_t.png): failed to open stream: Permission denied in /var/www/omp/lib/pkp/classes/file/FileManager.inc.php on line 180
PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  null value in column "setting_type" violates not-null constraint
DETAIL:  Failing row contains (1, en_US, coverImage, a:3:{s:10:"uploadName";s:29:"submission_1_1_coverImage.png";s:12..., null). Query: INSERT INTO submission_settings (submission_id, setting_name, setting_value, locale)
						VALUES (?, ?, ?, ?) in /var/www/omp/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /var/www/omp/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres8), 'INSERT INTO sub...')
#1 /var/www/omp/classes/install/Upgrade.inc.php(814): DAO->update('INSERT INTO sub...', Array)
#2 /var/www/omp/lib/pkp/classes/install/Installer.inc.php(441): Upgrade->migrateSubmissionCoverImages(Object(Upgrade), Array)
#3 /var/www/omp/lib/pkp/classes/install/Installer.inc.php(265): Installer->executeAction(Array)
#4 /var/www/omp/lib/pkp/classes/install/Installer.inc.php(188): Installer->executeInstaller()
#5 /var/www/omp/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer->execut in /var/www/omp/lib/pkp/classes/db/DAO.inc.php on line 703

Hi @seanc,

Just to double-check – what version of OMP are you upgrading from? The title of the thread says 3.2.1 to 3.2.1-1, but the first post says 3.1.2 to 3.2.1-1.

Regards,
Alec Smecher
Public Knowledge Project Team

Sorry, @asmecher, that’s a typo (or a sign of my muddled brain).

It should be 3.1.2 to 3.2.1-1

Hi @seanc,

It looks like you’ve moved to a checkout from the master branch of GitHub – that’s unreleased code and will definitely be buggy! If you’re looking for something to use in production, try the stable-3_2_1 branch.

Regards,
Alec Smecher
Public Knowledge Project Team

Sorry. Hopefully I did everything right this time, but still the same error:

$ php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.2.1.1]
[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_chapters.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_categories.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_pub_id.xml (skipped)]
[code: Installer Installer::migrateSubmissionCoverImages]
PHP Notice:  Undefined offset: 1 in /home/omp/omp/classes/install/Upgrade.inc.php on line 760
PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  null value in column "setting_type" violates not-null constraint
DETAIL:  Failing row contains (1, en_US, coverImage, a:3:{s:10:"uploadName";s:29:"submission_1_1_coverImage.png";s:12..., null). Query: INSERT INTO submission_settings (submission_id, setting_name, setting_value, locale)
						VALUES (?, ?, ?, ?) in /home/omp/omp/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/omp/omp/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_postgres8), 'INSERT INTO sub...')
#1 /home/omp/omp/classes/install/Upgrade.inc.php(814): DAO->update('INSERT INTO sub...', Array)
#2 /home/omp/omp/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->migrateSubmissionCoverImages(Object(Upgrade), Array)
#3 /home/omp/omp/lib/pkp/classes/install/Installer.inc.php(263): Installer->executeAction(Array)
#4 /home/omp/omp/lib/pkp/classes/install/Installer.inc.php(186): Installer->executeInstaller()
#5 /home/omp/omp/lib/pkp/classes/cliTool/UpgradeTool.inc.php(88): Installer- in /home/omp/omp/lib/pkp/classes/db/DAO.inc.php on line 703

Hi @seanc,

I’ve added another commit that should resolve this – see [OMP] Null value in column setting_type during Upgrade::migrateSubmissionCoverImages · Issue #6216 · pkp/pkp-lib · GitHub for details.

Thanks,
Alec Smecher
Public Knowledge Project Team

I hope it’s nothing I’m doing wrong, but I got another error:

php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.2.1.1]
[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_chapters.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_categories.xml (skipped)]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_pub_id.xml (skipped)]
[code: Installer Installer::migrateSubmissionCoverImages]
PHP Notice:  Undefined offset: 1 in /home/omp/omp/classes/install/Upgrade.inc.php on line 760
PHP Notice:  Undefined offset: 1 in /home/omp/omp/classes/install/Upgrade.inc.php on line 760
PHP Notice:  Undefined offset: 1 in /home/omp/omp/classes/install/Upgrade.inc.php on line 760
[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
ERROR: Upgrade failed: DB: ERROR:  syntax error at or near "email_templates_assoc"
LINE 1: ALTER TABLE email_templates DROP INDEX email_templates_assoc...

Hi @seanc,

That’s odd – it looks like ADODB is attempting to use MySQL-style SQL. What driver do you have configured in your config.inc.php configuration file?

Regards,
Alec Smecher
Public Knowledge Project Team

[database]

driver = postgres
host = localhost
username = omp
password = xxxx
name = omp
; Set the non-standard port and/or socket, if used
; port = 3306
; unix_socket = /var/run/mysqld/mysqld.sock


; Enable persistent connections (recommended)
persistent = Off

; Enable database debug output (very verbose!)
debug = Off

Hi @seanc,

Hmm, I can’t figure out why that SQL syntax would be used. Are you able to send me a private message with a link to a database dump?

Regards,
Alec Smecher
Public Knowledge Project Team

Just encountered this issue on MariaDB 10.5. After implementation of above modifications, upgrade scripts brakes with info:

ERROR: Upgrade failed: DB: Column ‘publication_id’ cannot be null

Hi @open2source,

See e.g.: OJS 3.2 Upgrade Error -- publication_id cannot be null in 3.2.0_versioning.xml

Regards,
Alec Smecher
Public Knowledge Project Team