Illegal mix collations. Error en búsquedas y galeradas HTML. Solucionado

RTome · June 25, 2020, 11:32am

Hola a todos.

En la actualización a OJS 3.2 desde 3.0 nos hemos encontrado con el problema de que no funcionaban las búsquedas ni la carga de galeradas HTML.

La petición de galerada HTML devolvía este error:
Fatal error: Uncaught Exception: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ Query: SELECT i.* FROM issues i, submissions a LEFT JOIN publications p ON (p.publication_id = a.current_publication_id) LEFT JOIN publication_settings ps ON (ps.publication_id = p.publication_id) WHERE ps.setting_name = ? AND ps.setting_value = CAST(i.issue_id as CHAR) AND a.submission_id = ? AND a.context_id = i.journal_id in /opt/revistasonline/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php:703 Stack trace: #0 /opt/revistasonline/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), ‘SELECT\ti. \n\t\t\tF…’) #1 /opt/revistasonline/ojs-3.2.0-3/classes/issue/IssueDAO.inc.php(569): DAO->retrieve('SELECT\ti. \n\t\t\tF…’, Array) #2 /opt/revistasonline/ojs-3.2.0-3/plugins/generic/htmlArticleGalley/HtmlArticleGalleyPlugin.inc.php(180): IssueDAO->getBySubmissionId(10742) #3 /opt/revistasonline/ojs-3.2.0-3/plugins/generic/htmlArt in /opt/revistasonline/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php on line 703.

La petición de búsqueda este otro:
Fatal error: Uncaught Exception: DB Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’ Query: SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(p.date_published) AS s_pub, COUNT(*) AS count FROM submissions s JOIN publications p ON (p.publication_id = s.current_publication_id) JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’) JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value) JOIN submission_search_objects o ON (s.submission_id = o.submission_id) NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0 WHERE s.status = 3 AND i.published = 1 AND k0.keyword_text = ? AND i.journal_id = ? GROUP BY o.submission_id ORDER BY count DESC LIMIT 500 in /opt/revistasonline/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php:703 Stack trace: #0 /opt/revistasonline/ojs-3.2.0-3/l in /opt/revistasonline/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php on line 703.

Tras estudiarlo, llegamos a la conclusión de que el fallo se producía en las conversiones de datos de columnas a modo texto con “CAST” debido a que las tablas que se han generado nuevas en la actualización tienen establecido en sus columnas de texto el “collate” como formato utf8_unicode_ci que va asociado al “charset” utf8.

Lo que ocurría es que la base de datos MySQL que utilizamos tiene definido por defecto asociado al “charset” utf8 el “collate” utf8_general_ci y cuando hace la operación de “CAST” para convertir una columna en texto el valor que devuelve tiene el “collate” por defecto (utf8_general_ci ) que es distinto del de la columna de la nueva tabla (uf8_unicode_ci).

Para solucionarlo, hemos modificado el código, donde se hace CAST, indicando explícitamente que utilice “collate utf8_unicode_ci”

Estos son los archivos modificados y la modificación:

./lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-mssql.inc.php
$value = “CAST(” . $value . " AS VARCHAR(255))";
por
$value = “CAST(” . $value . " AS VARCHAR(255)) collate utf8_unicode_ci";

./lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-mssqlnative.inc.php
$value = “CAST(” . $value . " AS VARCHAR(255))";
por
$value = “CAST(” . $value . " AS VARCHAR(255)) collate utf8_unicode_ci";

./lib/pkp/classes/note/NoteDAO.inc.php
JOIN item_views v ON (v.assoc_type = ? AND v.assoc_id = CAST(n.note_id AS CHAR) AND v.user_id = ?)
por
JOIN item_views v ON (v.assoc_type = ? AND v.assoc_id = CAST(n.note_id AS CHAR) collate utf8_unicode_ci AND v.user_id = ?)

./classes/oai/ojs/OAIDAO.inc.php
JOIN issues i ON (CAST(i.issue_id AS CHAR) = psissue.setting_value)
por
JOIN issues i ON (CAST(i.issue_id AS CHAR) collate utf8_unicode_ci = psissue.setting_value)

./classes/article/AuthorDAO.inc.php
’ . ($includeEmail ? ‘aa.email,’ : ‘CAST(’’ AS CHAR),’) . ’
por
’ . ($includeEmail ? ‘aa.email,’ : ‘CAST(’’ AS CHAR) collate utf8_unicode_ci,’) . ’
y
JOIN issues i ON (ppss.setting_name = ? AND ppss.setting_value = CAST(i.issue_id AS CHAR) AND i.published = 1
por
JOIN issues i ON (ppss.setting_name = ? AND ppss.setting_value = CAST(i.issue_id AS CHAR) utf8_unicode_ci AND i.published = 1)

./classes/issue/IssueDAO.inc.php

WHERE ps.setting_name = ? AND ps.setting_value = CAST(i.issue_id as CHAR) AND
por

WHERE ps.setting_name = ? AND ps.setting_value = CAST(i.issue_id as CHAR) collate utf8_unicode_ci AND"

Con esto se solucionaron los problemas.

Si existe una solución mejor, agradeceríamos la información.
Muchas gracias.

asmecher · June 25, 2020, 4:34pm

Hi @RTome,

I’ve added an option to config.inc.php allowing you to specify your database collation, rather than hard-coding utf8_general_ci: Database error: Illegal mix of collations · Issue #6045 · pkp/pkp-lib · GitHub

We’ll start including this in OJS/OMP/OPS 3.2.1-1, but in the meantime you can apply the patch to add support for the option.

Thanks,
Alec Smecher
Public Knowledge Project Team

RTome · June 29, 2020, 6:49am

Muchas gracias @asmecher.
Pudimos encontrar una segunda solución:

En nuestra configuración de config.inc.php, actualmente teníamos los valores:

connection_charset = utf8 database_charset = utf-8 charset_normalization = utf-8

En la nueva versión desaparecían:

database_charset = utf-8 charset_normalization = utf-8

Y se mantenía:

connection_charset = utf8

Con este valor utf8 las nuevas tablas se construían con “collate utf8_unicode_ci”. Sin embargo si ponemos:

connection_charset = Off

Las deja como están y así no tenemos que modificar código. Hemos hecho de nuevo la instalación y los errores se corrigen.

Añadimos esta nueva información por si es de utilidad, aunque ya hemos tenido noticia de que se liberado la versión OJS 3.2.1-1, que seguro corrige el error.

Muchas gracias de nuevo.
Un saludo.

riquemg · July 7, 2020, 3:03pm

@asmecher, this error persists in 3.2.1-1version.

asmecher · July 7, 2020, 7:28pm

Hi all,

If you’re using OJS/OMP/OPS 3.2.1-1 and newer, and encounter the Illegal mix of collations error message, try specifying a collation in config.inc.php in the [database] section:

collation = utf8_general_ci

The default will be utf8_unicode_ci, but your server might be defaulting to utf8_general_ci instead.

Regards,
Alec Smecher
Public Knowledge Project Team

esciencepress · September 27, 2020, 7:31am

Hi I have made the suggested changes in /journals/lib/pkp/classes/services/queryBuilders/BaseQueryBuilder.inc.php

111

and revised config.inc.php as;
222

I have also tried the following string also;

[database]
collation = utf8_unicode_ci

but I am still getting the error as:

[27-Sep-2020 07:24:32 UTC] Slim Application Error:
Type: Exception
Message: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' Query: SELECT	i.*
			FROM	issues i,
				submissions a
			LEFT JOIN publications p ON (p.publication_id = a.current_publication_id)
			LEFT JOIN publication_settings ps ON (ps.publication_id = p.publication_id)
			WHERE	ps.setting_name = ? AND ps.setting_value = CAST(i.issue_id as CHAR) AND
				a.submission_id = ? AND
				a.context_id = i.journal_id
File: /home/********/public_html/journals/lib/pkp/classes/db/DAO.inc.php
Line: 703
Trace: #0 /home/********/public_html/journals/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT\ti.*\n\t\t\tF...')
#1 /home/********/public_html/journals/classes/issue/IssueDAO.inc.php(560): DAO->retrieve('SELECT\ti.*\n\t\t\tF...', Array)
#2 /home/********/public_html/journals/classes/submission/Submission.inc.php(86): IssueDAO->getBySubmissionId(60)
#3 /home/********/public_html/journals/lib/pkp/classes/services/PKPPublicationService.inc.php(572): Submission->_getContextLicenseFieldValue(NULL, 3, Object(Publication))
#4 /home/********/public_html/journals/lib/pkp/api/v1/submissions/PKPSubmissionHandler.inc.php(743): PKP\Services\PKPPublicationService->publish(Object(Publication))
#5 [internal function]: PKPSubmissionHandler->publishPublication(Object(Slim\Http\Request), Object(APIResponse), Array)
#6 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(40): call_user_func(Array, Object(Slim\Http\Request), Object(APIResponse), Array)
#7 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/Route.php(281): Slim\Handlers\Strategies\RequestResponse->__invoke(Array, Object(Slim\Http\Request), Object(APIResponse), Array)
#8 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->__invoke(Object(Slim\Http\Request), Object(APIResponse))
#9 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/Route.php(268): Slim\Route->callMiddlewareStack(Object(Slim\Http\Request), Object(APIResponse))
#10 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/App.php(503): Slim\Route->run(Object(Slim\Http\Request), Object(APIResponse))
#11 /home/********/public_html/journals/lib/pkp/classes/security/authorization/internal/ApiAuthorizationMiddleware.inc.php(77): Slim\App->__invoke(Object(Slim\Http\Request), Object(APIResponse))
#12 [internal function]: ApiAuthorizationMiddleware->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Slim\App))
#13 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(ApiAuthorizationMiddleware), Array)
#14 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Slim\App))
#15 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Slim\App))
#16 /home/********/public_html/journals/lib/pkp/classes/security/authorization/internal/ApiCsrfMiddleware.inc.php(46): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#17 [internal function]: ApiCsrfMiddleware->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#18 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(ApiCsrfMiddleware), Array)
#19 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#20 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#21 /home/********/public_html/journals/lib/pkp/classes/security/authorization/internal/ApiTokenDecodingMiddleware.inc.php(82): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#22 [internal function]: ApiTokenDecodingMiddleware->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#23 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(ApiTokenDecodingMiddleware), Array)
#24 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#25 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#26 /home/********/public_html/journals/lib/pkp/classes/handler/APIHandler.inc.php(68): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#27 [internal function]: APIHandler->{closure}(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#28 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(Closure), Array)
#29 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#30 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#31 /home/********/public_html/journals/lib/pkp/classes/handler/APIHandler.inc.php(127): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#32 [internal function]: APIHandler->{closure}(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#33 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(Closure), Array)
#34 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#35 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#36 /home/********/public_html/journals/lib/pkp/classes/handler/APIHandler.inc.php(132): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#37 [internal function]: APIHandler->{closure}(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#38 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(Closure), Array)
#39 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#40 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#41 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#42 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/App.php(392): Slim\App->callMiddlewareStack(Object(Slim\Http\Request), Object(APIResponse))
#43 /home/********/public_html/journals/lib/pkp/classes/handler/APIHandler.inc.php(124): Slim\App->process(Object(Slim\Http\Request), Object(APIResponse))
#44 [internal function]: APIHandler->{closure}(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#45 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(Closure), Array)
#46 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#47 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#48 /home/********/public_html/journals/lib/pkp/classes/handler/APIHandler.inc.php(132): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#49 [internal function]: APIHandler->{closure}(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#50 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array(Object(Closure), Array)
#51 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#52 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(APIResponse), Object(Closure))
#53 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(APIResponse))
#54 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/App.php(392): Slim\App->callMiddlewareStack(Object(Slim\Http\Request), Object(APIResponse))
#55 /home/********/public_html/journals/lib/pkp/lib/vendor/slim/slim/Slim/App.php(297): Slim\App->process(Object(Slim\Http\Request), Object(APIResponse))
#56 /home/********/public_html/journals/lib/pkp/classes/core/APIRouter.inc.php(115): Slim\App->run()
#57 /home/********/public_html/journals/lib/pkp/classes/core/Dispatcher.inc.php(143): APIRouter->route(Object(Request))
#58 /home/********/public_html/journals/lib/pkp/classes/core/PKPApplication.inc.php(279): Dispatcher->dispatch(Object(Request))
#59 /home/********/public_html/journals/index.php(68): PKPApplication->execute()
#60 {main}
View in rendered output by enabling the "displayErrorDetails" setting.

asmecher · September 29, 2020, 3:48pm

Hi @esciencepress,

What are your collation-related MySQL configuration variables? See MySQL :: MySQL 5.7 Reference Manual :: 10.4 Connection Character Sets and Collations. In particular, check character_set_server, collation_server, character_set_connection, and collation_connection.

Regards,
Alec Smecher
Public Knowledge Project Team

esciencepress · October 3, 2020, 9:44pm

Hi @asmecher,

I am unable t see the MYSQL configuration variables. Can you please guide me on how to check this?

asmecher · October 4, 2020, 5:09pm

Hi @esciencepress,

That’s more of a MySQL administration question than an OJS question; you might find some guidance on e.g. Stackoverflow.com.

Regards,
Alec Smecher
Public Knowledge Project Team

Sunny · May 23, 2022, 3:41pm

I was able to fix the problem like this:
; Database collation
collation = utf8_unicode_ci

My database has this:
collation_connection | utf8_general_ci
collation_database | utf8_unicode_ci

With utf8_general_ci galleys do not work for articles in html.
If I keep it in the future with utf8_unicode_ci i will have problems?

rcgillis · June 6, 2022, 11:00am

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