[3.2.1-4 with Postgres] PHP Fatal error: Uncaught PDOException: SQLSTATE[42601] when updating to 3.3.0-7

Adriano_Jose · July 1, 2021, 8:12pm

Hey,
when trying to upgrade from 3.2.1-4 to 3.3.0-7 we encountered the following error:

[code: Installer Installer::checkPhpVersion]
[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.
PHP Fatal error:  Uncaught PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "on"
LINHA 1: ...ting_type", "locale") values ($1, $2, $3, $4, $5) on conflic...
                   ^ in /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(489): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('insert into "na...', Array)
#3 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('insert into "na...', Array, Object(Clo in /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

My config.inc.php has
client_charset = UTF-8 and
connection_charset = UTF-8

Could it be some wrong code?

asmecher · July 2, 2021, 4:05pm

Hi @Adriano_Jose,

Can you include the entire stack trace? The entries after the #3 are the ones I’d be interested in.

Thanks,
Alec Smecher
Public Knowledge Project Team

Adriano_Jose · July 2, 2021, 8:31pm

Hi @asmecher ,
how do I show the complete stack trace?
Is it some configuration?

regards

asmecher · July 2, 2021, 9:49pm

Hi @Adriano_Jose,

There should be more to the stack trace than the portion you posted – or was that the entire message?

Regards,
Alec Smecher
Public Knowledge Project Team

asmecher · July 2, 2021, 9:55pm

Hi @Adriano_Jose,

In any case, I suspect the problem is due to an incompatibility between the Laravel upsert extension (GitHub - staudenmeir/laravel-upsert: Laravel UPSERT and INSERT IGNORE queries) and your version of PostgreSQL. According to laravel-upsert/README.md at master · staudenmeir/laravel-upsert · GitHub at least PostgreSQL 9.5 is required; what version are you using?

Regards,
Alec Smecher
Public Knowledge Project Team

Adriano_Jose · July 5, 2021, 6:23pm

Yes, this is the entire message. I even searched if there is any way to show the complete satck trace, but I couldn’t find it.

Our version of postgres is 9.2.24.
I will update and report the results.

Thank you very much for the feedback. I would find it very difficult to find the solution myself.
I suggest changing the minimum requirements on the page https://pkp.sfu.ca/ojs/ojs_download/ referring to PostgreSQL.

Regards,
Adriano Moreno

asmecher · July 5, 2021, 6:40pm

Hi @Adriano_Jose,

Thanks, I’ve bumped the base requirements on the PKP website and in the README documents.

Regards,
Alec Smecher
Public Knowledge Project Team

Adriano_Jose · July 9, 2021, 9:46pm

Hi @asmecher,
We’ve updated postgres to 13 and now the following error appears:

ERROR: Upgrade failed: DB: PDOException: SQLSTATE[23502]: Not null violation: 7 ERROR:  column "review_round_id" of relation "review_assignments" contains null values in /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('ALTER TABLE rev...', Array)
#3 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('ALTER TABLE rev...', Array, Object(Closure))
#4 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run('ALTER TABLE rev...', Array, Object(Closure))
#5 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement('ALTER TABLE rev...')
#6 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\PostgresConnection), Object(Illuminate\Database\Schema\Grammars\PostgresGrammar))
#7 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#8 /var/www/ojs-3.3.0-7/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(114): Illuminate\Database\Schema\Builder->table('review_assignme...', Object(Closure))
#9 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#10 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#11 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#12 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#13 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#14 /var/www/ojs-3.3.0-7/tools/upgrade.php(22): UpgradeTool->execute()
#15 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[23502]: Not null violation: 7 ERROR:  column "review_round_id" of relation "review_assignments" contains null values in /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18
Stack trace:
#0 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(117): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
#1 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('ALTER TABLE rev...', Array)
#3 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('ALTER TABLE rev...', Array, Object(Closure))
#4 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run('ALTER TABLE rev...', Array, Object(Closure))
#5 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement('ALTER TABLE rev...')
#6 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\PostgresConnection), Object(Illuminate\Database\Schema\Grammars\PostgresGrammar))
#7 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#8 /var/www/ojs-3.3.0-7/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(114): Illuminate\Database\Schema\Builder->table('review_assignme...', Object(Closure))
#9 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#10 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#11 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#12 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#13 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#14 /var/www/ojs-3.3.0-7/tools/upgrade.php(22): UpgradeTool->execute()
#15 {main}

Next Illuminate\Database\QueryException: SQLSTATE[23502]: Not null violation: 7 ERROR:  column "review_round_id" of relation "review_assignments" contains null values (SQL: ALTER TABLE review_assignments ALTER review_round_id SET NOT NULL) in /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
Stack trace:
#0 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('ALTER TABLE rev...', Array, Object(Closure))
#1 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run('ALTER TABLE rev...', Array, Object(Closure))
#2 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement('ALTER TABLE rev...')
#3 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\PostgresConnection), Object(Illuminate\Database\Schema\Grammars\PostgresGrammar))
#4 /var/www/ojs-3.3.0-7/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))
#5 /var/www/ojs-3.3.0-7/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(114): Illuminate\Database\Schema\Builder->table('review_assignme...', Object(Closure))
#6 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#7 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#8 /var/www/ojs-3.3.0-7/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#9 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#10 /var/www/ojs-3.3.0-7/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#11 /var/www/ojs-3.3.0-7/tools/upgrade.php(22): UpgradeTool->execute()
#12 {main}

We found 15 different submissions with this sql:
select submission_id from review_assignments where review_round_id is null;

We searched for these submissions through the site and I managed to delete some, but others didn’t appear the option to delete. These are apparently incomplete submissions, without any information, just a file.
We remember that at the beginning of the system, predefined command lines were used to add the first editions. We believe it could be one of those files.
How could it delete from the database without causing damage?

asmecher · July 12, 2021, 2:58pm

Hi @Adriano_Jose,

If they’re junk submissions, you could use tools/deleteSubmissions.php to remove them from the command line by ID. (Make sure to take a good backup first!)

Regards,
Alec Smecher
Public Knowledge Project Team

asmecher · July 17, 2021, 8:00am

This topic was automatically closed after 11 days. New replies are no longer allowed.