Failing to upgrade from 3.1.1-4 to any version above due to null values

[jascanio]

Hi,
OJS 3.1.1-4
Multijournal
Multilanguage

We’re trying to upgrade our OJS from 3.1.1-4 to 3.3.0-6.

As this attempt resulted in failure during upgrade process (null values in locales and incorrect string values in email templates subjects) we tried to go through version 3.2.1.1 as a bridge upgrade.

But, we got the same error.

We skipped the one pertaining locales by:
update user_settings set locale = ‘es_ES’ where locale like ‘’ or locale is null;
update submissions set locale = ‘es_ES’ where locale like ‘’ or locale is null;
update author_settings set locale = ‘es_ES’ where locale like ‘’ or locale is null;

(Don’t like this, but we had no choice)

However, we have not been able to bypass the second error, pertaining email templates subjects.
We got the following error during upgrade process.

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD9\x86\xD8\xB4\xD8\xA7...' for column 'subject' at row 1 in /var/www/html/revistas/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
Stack trace:
#0 /var/www/html/revistas/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /var/www/html/revistas/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(489): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /var/www/html/revistas/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('INSERT INTO ema...', Array)
#3 /var/www/html/revistas/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('INSERT INTO ema...', Array, Object(Closure))
#4 /var/www/html/revistas/lib/pkp/lib/vendor/laravel/framework/src/Illum in /var/www/html/revistas/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

Do you have any idea as why we are getting these errors?

Any help will be deeply appreciated.

Thanks in advance.

Regards,
Juan

[jascanio]

Hi again,
Any help about this issue?
Thanks in advance.
Regards,
Juan

[tecnicouncoma]

Hi @jascanio
I am trying to upgrade from

php tools/upgrade.php check
Code version:      2.4.8.3
Database version:  2.4.8.3
Latest version:    3.3.0.8

to 3.0.2 and I have similar ERROR

php tools/upgrade.php check
Code version:      3.0.2.0
Database version:  2.4.8.3
Latest version:    3.3.0.8
Database version is older than code version
Run "tools/upgrade.php upgrade" to update

ERROR: Upgrade failed: DB: Incorrect string value: '\xD9\x86\xD8\xB4\xD8\xA7...' for column 'subject' at row 1

In this post is a posible solution but I tried and didn’t work. I double check config.inc.php, I add and remove options, I tried other versions like 3.2.1-3 and 3.3.0-8, but nothing works. There has to be something I am misunderstanding.

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.04 sec)

mysql> SHOW VARIABLES LIKE 'collat%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

mysql> 

I tried with this too, but I get the same error:

mysql> CREATE DATABASE ojs CHARACTER SET utf8 COLLATE utf8_general_ci;

It is necessary to “convert” DB content to UTF8 or something like that? How can I do that?

Thanks

[tecnicouncoma]

Well, I apply this solution and it works. Thanks.