Ojs-3.3.0-8 rebuildSearchIndex error

Software Support
1

We recently upgraded from version 3.1.2.1 to version 3.3.0.8. Even before that, our operating system was from debian 9 to debian 10. Currently debian 10, php7.3, mysql Ver 15.1 Distrib 10.3.31-MariaDB.
We ran into an error with the sql update TRUNCATE the following tables:
submission_search_object_keywords; submission_search_objects; submission_search_keyword_list;
The reason is to re-create the database with the settings (CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci). Unfortunately, the load was very slow due to the large search database.
Re-indexing runs to the next error:

[08-Mar-2022 01:41:50 Europe/Budapest] PHP Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: ‘\xF0\x9D\x91\x89\xF0\x9D…’ for column ojsdata.submission_search_keyword_list.keyword_text at row 1 in /pathojs//lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /pathojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /pathojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(489): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /path/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘INSERT INTO sub…’, Array)
#3 /pathojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘INSERT INTO sub…’, Array, Object(Closure)) in /pathojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

 SHOW FULL COLUMNS FROM submission_search_keyword_list;
+--------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field        | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+--------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| keyword_id   | bigint(20)  | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| keyword_text | varchar(60) | utf8_general_ci | NO   | UNI | NULL    |                | select,insert,update,references |         |
+--------------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
2

Sorry but now all of a sudden I can’t find the ojs forum link that had this solution. (CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)

3

Currently the tables where not utf8_general_ci or NULL:

cat ojs_db.txt | grep -v NULL | grep -v utf8_general_ci
MariaDB [ojsdata]> select TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.columns where table_schema = 'ojsdata' order by table_name,ordinal_position;
+------------------------------------------+------------------------------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME     |
+------------------------------------------+------------------------------------+--------------------+
| email_templates_settings                 | locale | utf8mb4_unicode_ci |
| email_templates_settings                 | setting_name | utf8mb4_unicode_ci |
| email_templates_settings                 | setting_value | utf8mb4_unicode_ci |
| publications                             | url_path| utf8mb4_unicode_ci |
| publication_galleys                      | locale| utf8mb4_unicode_ci |
| publication_galleys                      | label  | utf8mb4_unicode_ci |
| publication_galleys                      | remote_url | utf8mb4_unicode_ci |
| publication_galleys                      | url_path | utf8mb4_unicode_ci |
| publication_galley_settings              | locale  | utf8mb4_unicode_ci |
| publication_galley_settings              | setting_name | utf8mb4_unicode_ci |
| publication_galley_settings              | setting_value   | utf8mb4_unicode_ci |
| publication_settings                     | locale  utf8mb4_unicode_ci |
| publication_settings                     | setting_name| utf8mb4_unicode_ci |
| publication_settings                     | setting_value | utf8mb4_unicode_ci |

Because the rest of the tables are: utf8_general_ci or NULL

4

This was an error updating:
[data: dbscripts / xml / upgrade / 3.2.0_update.xml]
ERROR: Upgrade failed: DB: SQLSTATE [22007]: Invalid datetime format: 1366 Incorrect string value: ‘\ xC5 \ x91i ho …’ for column ojsdata.email_templates_settings.setting_value at row 16 (SQL:
INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name)
SELECT email_id, locale, subject, ‘subject’
FROM email_templates_data
)

And that’s where I got the solution:

5

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