Hi,
After upgrade my OJS to version 3.2.1.1 the search and advanced search stop working.
No error on logs, the search is jousting return no results.
Regards,
Tarcisio Pereira
Hi
I suspect that there is something wrong with the method:
function getAuthorsAlphabetizedByJournal($journalId = null, $initial = null, $rangeInfo = null, $includeEmail = false){}
In the file: classes/article/AuthorDAO.inc.php
Because there is this error on postgresql log:
2020-10-02 15:15:52 -03 [62252]: [7-1] user=revistas,db=revistas3 ERROR: syntax error at or near “AND” at character 6778
2020-10-02 15:15:52 -03 [62252]: [8-1] user=revistas,db=revistas3 STATEMENT: SELECT a.*, ug.show_title, p.locale,
COALESCE(agl.setting_value, agpl.setting_value) AS author_given,
CASE WHEN agl.setting_value <> ‘’ THEN afl.setting_value ELSE afpl.setting_value END AS author_family
FROM authors a
JOIN user_groups ug ON (a.user_group_id = ug.user_group_id)
JOIN publications p ON (p.publication_id = a.publication_id)
JOIN submissions s ON (s.current_publication_id = p.publication_id)
LEFT JOIN author_settings agl ON (a.author_id = agl.author_id AND agl.setting_name = $1 AND agl.locale = $2)
LEFT JOIN author_settings agpl ON (a.author_id = agpl.author_id AND agpl.setting_name = $3 AND agpl.locale = p.locale)
LEFT JOIN author_settings afl ON (a.author_id = afl.author_id AND afl.setting_name = $4 AND afl.locale = $5)
LEFT JOIN author_settings afpl ON (a.author_id = afpl.author_id AND afpl.setting_name = $6 AND afpl.locale = p.locale)
JOIN (
SELECT
MIN(aa.author_id) as author_id,
CONCAT(
CAST(‘’ AS CHAR),
’ ',
ac.setting_value,
’ ’
,
COALESCE(asg0.setting_value, ‘’), ’ ',
COALESCE(asf0.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa0.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg1.setting_value, ‘’), ’ ',
COALESCE(asf1.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa1.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg2.setting_value, ‘’), ’ ',
COALESCE(asf2.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa2.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg3.setting_value, ‘’), ’ ',
COALESCE(asf3.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa3.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg4.setting_value, ‘’), ’ ',
COALESCE(asf4.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa4.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg5.setting_value, ‘’), ’ ',
COALESCE(asf5.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa5.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg6.setting_value, ‘’), ’ ',
COALESCE(asf6.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa6.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg7.setting_value, ‘’), ’ ',
COALESCE(asf7.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa7.setting_value FROM 1 FOR 255), ‘’), ’ ’
,
COALESCE(asg8.setting_value, ‘’), ’ ',
COALESCE(asf8.setting_value, ‘’), ’ ',
COALESCE(SUBSTRING(asa8.setting_value FROM 1 FOR 255), ‘’), ’ ’
) as names
FROM authors aa
JOIN publications pp ON (pp.publication_id = aa.publication_id)
LEFT JOIN publication_settings ppss ON (ppss.publication_id = pp.publication_id)
JOIN submissions ss ON (ss.submission_id = pp.submission_id AND ss.current_publication_id = pp.publication_id AND ss.status = 3)
JOIN journals j ON (ss.context_id = j.journal_id)
JOIN issues i ON (ppss.setting_name = $7 AND ppss.setting_value = CAST(i.issue_id AS CHAR) AND i.published = 1)
LEFT JOIN author_settings ac ON (ac.author_id = aa.author_id AND ac.setting_name = 'country')
LEFT JOIN author_settings asg0 ON (asg0.author_id = aa.author_id AND asg0.setting_name = 'givenName' AND asg0.locale = 'en_US')
LEFT JOIN author_settings asf0 ON (asf0.author_id = aa.author_id AND asf0.setting_name = 'familyName' AND asf0.locale = 'en_US')
LEFT JOIN author_settings asa0 ON (asa0.author_id = aa.author_id AND asa0.setting_name = 'affiliation' AND asa0.locale = 'en_US')
LEFT JOIN author_settings asg1 ON (asg1.author_id = aa.author_id AND asg1.setting_name = 'givenName' AND asg1.locale = 'es_ES')
LEFT JOIN author_settings asf1 ON (asf1.author_id = aa.author_id AND asf1.setting_name = 'familyName' AND asf1.locale = 'es_ES')
LEFT JOIN author_settings asa1 ON (asa1.author_id = aa.author_id AND asa1.setting_name = 'affiliation' AND asa1.locale = 'es_ES')
LEFT JOIN author_settings asg2 ON (asg2.author_id = aa.author_id AND asg2.setting_name = 'givenName' AND asg2.locale = 'it_IT')
LEFT JOIN author_settings asf2 ON (asf2.author_id = aa.author_id AND asf2.setting_name = 'familyName' AND asf2.locale = 'it_IT')
LEFT JOIN author_settings asa2 ON (asa2.author_id = aa.author_id AND asa2.setting_name = 'affiliation' AND asa2.locale = 'it_IT')
LEFT JOIN author_settings asg3 ON (asg3.author_id = aa.author_id AND asg3.setting_name = 'givenName' AND asg3.locale = 'pt_BR')
LEFT JOIN author_settings asf3 ON (asf3.author_id = aa.author_id AND asf3.setting_name = 'familyName' AND asf3.locale = 'pt_BR')
LEFT JOIN author_settings asa3 ON (asa3.author_id = aa.author_id AND asa3.setting_name = 'affiliation' AND asa3.locale = 'pt_BR')
LEFT JOIN author_settings asg4 ON (asg4.author_id = aa.author_id AND asg4.setting_name = 'givenName' AND asg4.locale = 'de_DE')
LEFT JOIN author_settings asf4 ON (asf4.author_id = aa.author_id AND asf4.setting_name = 'familyName' AND asf4.locale = 'de_DE')
LEFT JOIN author_settings asa4 ON (asa4.author_id = aa.author_id AND asa4.setting_name = 'affiliation' AND asa4.locale = 'de_DE')
LEFT JOIN author_settings asg5 ON (asg5.author_id = aa.author_id AND asg5.setting_name = 'givenName' AND asg5.locale = 'ru_RU')
LEFT JOIN author_settings asf5 ON (asf5.author_id = aa.author_id AND asf5.setting_name = 'familyName' AND asf5.locale = 'ru_RU')
LEFT JOIN author_settings asa5 ON (asa5.author_id = aa.author_id AND asa5.setting_name = 'affiliation' AND asa5.locale = 'ru_RU')
LEFT JOIN author_settings asg6 ON (asg6.author_id = aa.author_id AND asg6.setting_name = 'givenName' AND asg6.locale = 'ja_JP')
LEFT JOIN author_settings asf6 ON (asf6.author_id = aa.author_id AND asf6.setting_name = 'familyName' AND asf6.locale = 'ja_JP')
LEFT JOIN author_settings asa6 ON (asa6.author_id = aa.author_id AND asa6.setting_name = 'affiliation' AND asa6.locale = 'ja_JP')
LEFT JOIN author_settings asg7 ON (asg7.author_id = aa.author_id AND asg7.setting_name = 'givenName' AND asg7.locale = 'fr_FR')
LEFT JOIN author_settings asf7 ON (asf7.author_id = aa.author_id AND asf7.setting_name = 'familyName' AND asf7.locale = 'fr_FR')
LEFT JOIN author_settings asa7 ON (asa7.author_id = aa.author_id AND asa7.setting_name = 'affiliation' AND asa7.locale = 'fr_FR')
LEFT JOIN author_settings asg8 ON (asg8.author_id = aa.author_id AND asg8.setting_name = 'givenName' AND asg8.locale = 'zh_CN')
LEFT JOIN author_settings asf8 ON (asf8.author_id = aa.author_id AND asf8.setting_name = 'familyName' AND asf8.locale = 'zh_CN')
LEFT JOIN author_settings asa8 ON (asa8.author_id = aa.author_id AND asa8.setting_name = 'affiliation' AND asa8.locale = 'zh_CN')
WHERE j.enabled = 1 AND
AND (LOWER(asf0.setting_value) LIKE LOWER( $8) OR LOWER(asf1.setting_value) LIKE LOWER( $9) OR LOWER(asf2.setting_value) LIKE LOWER( $10) OR LOWER(asf3.setting_value) LIKE LOWER( $11) OR LOWER(asf4.setting_value) LIKE LOWER( $12) OR LOWER(asf5.setting_value) LIKE LOWER( $13) OR LOWER(asf6.setting_value) LIKE LOWER( $14) OR LOWER(asf7.setting_value) LIKE LOWER( $15) OR LOWER(asf8.setting_value) LIKE LOWER( $16))
GROUP BY names
) as t1 ON (t1.author_id = a.author_id)
ORDER BY author_family, author_given LIMIT 25 OFFSET 0
In the final lines “WHERE j.enabled = 1 AND AND”
Regards,
Tarcisio Pereira
Hi
Any news?
We have 194 journals hosted in this OJS installation.
If people can’t search, this is a big problem for us.
Regards,
Tarcisio Pereira
Hi @asmecher
Can you please help me?
The search is not working yet.
I don’t know what more can I do.
Regards,
Tarcisio Pereira
I suspect you’re encountering this issue: SQL typo in "browse by authors" feature · Issue #6111 · pkp/pkp-lib · GitHub
Regards,
Alec Smecher
Public Knowledge Project Team
1 Like
Hi @asmecher
Thank you.
I no longer have this error in the logs.
But the problem of empty results remains.
I already tried to reindex with: php tools/rebuildSearchIndex.php
Hi @asmecher
I apologize for the repeated request, but we are still having problems with it.
I don’t know what more can I do.
Regards,
Tarcisio Pereira
Hi,
Can someone please help me?
This has become a big issue, our site has 195 journals hosted.
Regards,
Tarcisio Pereira
Hi @asmecher
I changed the line 81 in file ‘classes/search/ArticleSearchDAO.inc.php’ from:
JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)
to:
JOIN issues i ON (CAST(i.issue_id AS TEXT) = ps.setting_value)
The search is working again, but I don’t know if this change is ok
Regards,
Tarcisio Pereira
Thanks for tracking this down! I believe it’s already filed here: [OJS] CAST AS CHAR i postgres · Issue #6320 · pkp/pkp-lib · GitHub
I’ll need to double-check whether that syntax works for MySQL – please watch that issue for updates.
Thanks,
Alec Smecher
Public Knowledge Project Team
1 Like
Hi @Tarcisio_Pereira/all,
I’ve posted some pull requests that should resolve this over at pkp/pkp-lib#6320. I’ve tested it locally with my sample data set but confirmation on real-world data would be very welcome!
Thanks,
Alec Smecher
Public Knowledge Project Team