OJS 3.4.0.1 - PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation

Software Support
1

Hello everyone,
I have updated my OJS from 3.3.0.14 to 3.4.0.1 and now I have noticed that following fata error in the PHP error log:

[12-Jul-2023 20:35:17 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘.last_modified) >= ? and GREATEST(a.last_modified, i.last_modified, p…’ at line 1 in /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:414
Stack trace:
#0 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(414): PDO->prepare()
#1 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(753): Illuminate\Database\Connection->Illuminate\Database{closure}()
#2 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\Database\Connection->runQueryCallback()
#3 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(422): Illuminate\Database\Connection->run()
#4 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2706): Illuminate\Database\Connection->select()
#5 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2694): Illuminate\Database\Query\Builder->runSelect()
#6 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3230): Illuminate\Database\Query\Builder->Illuminate\Database\Query{closure}()
#7 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2695): Illuminate\Database\Query\Builder->onceWithColumns()
#8 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3157): Illuminate\Database\Query\Builder->get()
#9 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3085): Illuminate\Database\Query\Builder->aggregate()
#10 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/PKPOAIDAO.php(148): Illuminate\Database\Query\Builder->count()
#11 /home/seisense/journal.seisense.com/classes/oai/ojs/JournalOAI.php(195): PKP\oai\PKPOAIDAO->getRecords()
#12 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/OAI.php(542): APP\oai\ojs\JournalOAI->records()
#13 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/OAI.php(95): PKP\oai\OAI->ListRecords()
#14 /home/seisense/journal.seisense.com/pages/oai/OAIHandler.php(47): PKP\oai\OAI->execute()
#15 [internal function]: APP\pages\oai\OAIHandler->index()
#16 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPRouter.php(334): call_user_func()
#17 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPPageRouter.php(277): PKP\core\PKPRouter->_authorizeInitializeAndCallRequest()
#18 /home/seisense/journal.seisense.com/lib/pkp/classes/core/Dispatcher.php(165): PKP\core\PKPPageRouter->route()
#19 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPApplication.php(373): PKP\core\Dispatcher->dispatch()
#20 /home/seisense/journal.seisense.com/index.php(21): PKP\core\PKPApplication->execute()
#21 {main}

Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘.last_modified) >= ? and GREATEST(a.last_modified, i.last_modified, p…’ at line 1 (SQL: select count(*) as aggregate from ((select GREATEST(a.last_modified, i.last_modified, p.last_modified) AS last_modified, a.submission_id as submission_id, i.issue_id, NULL AS tombstone_id, NULL AS set_spec, NULL AS oai_identifier, j.journal_id as journal_id, s.section_id as section_id from submissions as a inner join publications as p on a.current_publication_id = p.publication_id inner join publication_settings as psissue on psissue.publication_id = p.publication_id and psissue.setting_name = ‘issueId’ and psissue.locale = ‘’ inner join issues as i on CAST(i.issue_id AS CHAR(20)) = psissue.setting_value inner join sections as s on s.section_id = p.section_id inner join journals as j on j.journal_id = a.context_id where i.published = 1 and j.enabled = 1 and a.status = 3 and j.journal_id = 2 and GREATEST(a.last_modified, i.last_modified, p.last_modified) >= 1688342400 and GREATEST(a.last_modified, i.last_modified, p.last_modified) <= 1689033599) union (select dot.date_deleted as last_modified, dot.data_object_id as submission_id, NULL AS issue_id, dot.tombstone_id, dot.set_spec, dot.oai_identifier, tsoj.assoc_id, NULL AS assoc_id from data_object_tombstones as dot inner join data_object_tombstone_oai_set_objects as tsoj on tsoj.tombstone_id = dot.tombstone_id and tsoj.assoc_type = 256 and tsoj.assoc_id = 1 where dot.date_deleted >= 1688342400 and dot.date_deleted <= 1689033599) order by journal_id, submission_id asc) as temp_table) in /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760
Stack trace:
#0 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\Database\Connection->runQueryCallback()
#1 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(422): Illuminate\Database\Connection->run()
#2 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2706): Illuminate\Database\Connection->select()
#3 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2694): Illuminate\Database\Query\Builder->runSelect()
#4 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3230): Illuminate\Database\Query\Builder->Illuminate\Database\Query{closure}()
#5 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2695): Illuminate\Database\Query\Builder->onceWithColumns()
#6 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3157): Illuminate\Database\Query\Builder->get()
#7 /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3085): Illuminate\Database\Query\Builder->aggregate()
#8 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/PKPOAIDAO.php(148): Illuminate\Database\Query\Builder->count()
#9 /home/seisense/journal.seisense.com/classes/oai/ojs/JournalOAI.php(195): PKP\oai\PKPOAIDAO->getRecords()
#10 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/OAI.php(542): APP\oai\ojs\JournalOAI->records()
#11 /home/seisense/journal.seisense.com/lib/pkp/classes/oai/OAI.php(95): PKP\oai\OAI->ListRecords()
#12 /home/seisense/journal.seisense.com/pages/oai/OAIHandler.php(47): PKP\oai\OAI->execute()
#13 [internal function]: APP\pages\oai\OAIHandler->index()
#14 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPRouter.php(334): call_user_func()
#15 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPPageRouter.php(277): PKP\core\PKPRouter->_authorizeInitializeAndCallRequest()
#16 /home/seisense/journal.seisense.com/lib/pkp/classes/core/Dispatcher.php(165): PKP\core\PKPPageRouter->route()
#17 /home/seisense/journal.seisense.com/lib/pkp/classes/core/PKPApplication.php(373): PKP\core\Dispatcher->dispatch()
#18 /home/seisense/journal.seisense.com/index.php(21): PKP\core\PKPApplication->execute()
#19 {main}
thrown in /home/seisense/journal.seisense.com/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 760
[12-Jul-2023 21:16:43 UTC] PHP Warning: Undefined array key “issueId” in /home/seisense/journal.seisense.com/plugins/generic/citationStyleLanguage/pages/CitationStyleLanguageHandler.php on line 163

and this error appeared 26 times in one hour after every every two minutes as you can see below:

[12-Jul-2023 21:27:10 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:29:13 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:31:15 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:33:17 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:35:17 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:37:20 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:39:21 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
[12-Jul-2023 21:41:22 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]…
…

Any experte advice to resolve this will be highly appreciated.

OJS: 3.4.0.1
PHP: 8.1

Regards
seisense

2

Hi @seisense

What version of MySQL or MariaDB are you using?

Best
Jason

3

Hi @jnugent ,
Please find below the screenshot of my server settings:

image
image1672×489 24.6 KB

I suspect openarchive validation is causing this error. Although my journal is registerd in openarchive since 2018 and I never encountered this fatal error before.
Since I updated my OJS to 3.4.0.1, I started receiving this error. This error appers once in day, I believe when Acron jobs are scheduled.

Even the same error is produced everytime, if I try to revalidate and register on openarchive.
Steps to reproduce this error:
Validate and register on openarchive (https://www.openarchives.org/Register/ValidateSite ).
Step1 - it shows successful
Step 2 - Following the link received in email to complete the registeration process as a final step, caused this error in php error log.

Thanks
seisense

4

Thanks for resoving the issue.

1 Like