Error upgrading: Invalid datetime format

[Orlando_Alonzo]

I’ve been trying to update a OJS setup for a client from 3.1.1.4 to 3.3.0.4 (for almost two months I believe), always end up with a new problem. This time I can’t finish the update process: I get the error (sorry it is in spanish):

Se ha producido un error en la base de datos: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xE2\x80\x8B\xE2\x80\x8B...' for column jainaccc_ojs3.publication_settings.setting_value at row 1 (SQL: INSERT INTO publication_settings(publication_id, locale, setting_name, setting_value) SELECT s.current_publication_id, ss.locale, ss.setting_name, ss.setting_value FROM submissions as s LEFT JOIN submission_settings as ss ON (ss.submission_id = s.submission_id) WHERE ( setting_name IN ('abstract', 'cleanTitle', 'copyrightHolder', 'copyrightYear', 'coverage', 'coverImage', 'licenseUrl', 'locale', 'prefix', 'rights', 'source', 'subtitle', 'title', 'type') OR setting_name LIKE '%pub-id%' ) )

And can’t move past that. ¿Any ideas?

[Orlando_Alonzo]

Forgot to mention, I’m running on MariaDB 10.4.18 and PHP 7.4.16.

[retostauffer]

Having the same problem with an invalid datetime format in email_templates_default_datasubject; getting the error Invalid datetime format: 1366 Incorrect string value: '\xD9\x86\xD8\xB4\xD8\xA7...' which are some Chinese letters.

Assume the issue is caused by the connection_charset = utf8 in config.inc.php (OJS 3.3) which, previously, was set to Off (for OJS 3.1.X).

Tried to drop unused email templated but does not work; seems that they are added during the upgrade?

[retostauffer]

Hy there

Might be interesting for you, Orlando_Alonzo, if you have not found a solution yet. In our case the problem was that the database character set (migrated from OJS 2.4 > ... > 3.2) had the wrong character set which caused problems migrating from 3.2 to 3.3.

What we have done

Changed the character set of the database and all tables. As we migrate the system to a new development machine, we started from scratch creating a new database with the correct character set and collation using:

CREATE DATABASE ojs CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

… and adjust the character set of the tables by manipulating an SQL dump following this guide: https://docs.moodle.org/31/en/Converting_your_MySQL_database_to_UTF8. This is for a moodle installation but explains what to do.

Altering existing tables

Alternatively you should be able to alter the database and the tables, something as (untested):

• ALTER DATABASE mydatabasename charset=utf8mb4 COLLATE utf8mb4_unicode_ci;
• ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4;

utf8mb4 is a 4-byte version, utf8 itself an alias for 3-byte character encoding. Not sure if 4-byte is explicitly needed (?).

Greez
R

[Orlando_Alonzo]

Thank you very much! I will definitely try that solution.

I’ll let you know my findings when I try.

Thanks again!

[moradnejad]

Hi Orlando,
Did the solution work? Can you give us an update?