Every time I run php tools/rebuildSearchIndex.php
After several hours I get this error and the script stops.
Any idea where the problem might be?
collation-server = utf8mb4_unicode_ci
init-connect = ‘SET NAMES utf8mb4’
character-set-server = utf8mb4
h: warning: setlocale: LC_ALL: cannot change locale (en_US): No such file or directory
sh: warning: setlocale: LC_ALL: cannot change locale (en_US): No such file or directory
sh: warning: setlocale: LC_ALL: cannot change locale (en_US): No such file or directory
sh: warning: setlocale: LC_ALL: cannot change locale (en_US): No such file or directory
tr: write error: Broken pipe
tr: write error
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation ‘=’ in /www/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /www/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(370): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\Query\Expression), Array)
#3 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(Object(Illuminate\Database\Query\Expression), Array, Object(Closure))
#4 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database in /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
OJS 3.3.0.8
During the reindexing process php tools/rebuildSearchIndex.php
and after a few hour I get this exception
182 articles indexed
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] Connection refused in /www/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:38
Stack trace:
#0 /www/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(38): PDO->__construct(‘mysql:host=mdb1…’, ‘MYUSERNAME’, ‘MYPASSWORD…’, Array)
#1 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(67): Doctrine\DBAL\Driver\PDOConnection->__construct(‘mysql:host=mdb1…’, ‘MYUSERNAME’, ‘MYPASSWORD…’, Array)
#2 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(100): Illuminate\Database\Connectors\Connector->createPdoConnection(‘mysql:host=mdb1…’, ‘MYUSERNAME’, ‘MYPASSWORD…’, Array)
#3 /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php(50): Illuminate\Database\Connectors\Connector->tryAgainIfCausedByLostConnection(Object(Doctrine\DBAL\Driver\PDO\Exception), 'mysql:h in /www/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
Hi @Dimitris_Sioulas,
I’ve merged your other post into this thread - probably best to keep this to one post. I will see if some of our team members can assist when they are able to/available.
Best regards,
Roger
PKP Team
It looks like you might have a mix of different collations in your database. Check for all tables and thrie columns which collation they have. Some seem to be utf8_general_ci while others are utf8mb4_unicode_ci.
The indexing job - among other actions - does a lot of left joins on different tables (we only saw that today, that’s why I am answering). If the tables and/or columns are not in the same collation, operations like “is equal to” will not work. Hence the error, I suppose.
Tables I see in our error message with the joins are:
- submission_files
- submission_artwork_files
- submission_supplementary_files
- submissions
- publications
You might want to check these out in particular.
See Illegal mix of collations error in MySql - Stack Overflow
(Gabriele)