OMP Upgrade Failed 3.1.2 -> 3.2.1-1

Software Support
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.

2

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

3

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
4

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

5

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
6

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

7

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

It should be 3.1.2 to 3.2.1-1

8

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

9

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
11

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

12

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

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

14 
[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
15

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

16

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

17

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

1 Like
18

Hello,
I just have this error, and I have not been able to solve it.

Could you tell me how you solved it?

Driver: postgres (9.6)
OS: Rocky Linux 8.7

thanks.-