About OAI Validation

seisense · July 15, 2023, 6:46am

Hi @asmecher ,
I have reported the fatal error here

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.

While investigating this error, I tried to revalidate and register my journal. This revalidation and update of OAI record failed with the same error as reported above by @IJBSRR_Journal throwing this error in php error log.

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.

[15-Jul-2023 06:21:51 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 = 1 and GREATEST(a.last_modified, i.last_modified, p.last_modified) >= 1640440721 and GREATEST(a.last_modified, i.last_modified, p.last_modified) <= 1640440721) 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 >= 1640440721 and dot.date_deleted <= 1640440721) 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

Thanks and regards
seisense