OJS Update 3.3.0.14 to 3.4.0.0 stable: General error: 1267 Illegal mix of collations

[loadbrain]

Describe the bug Trying to update from 3.3.0-14 to 3.4.0-0 php tools/upgrade.php check works fine php tools/upgrade.php upgrade stops with this error:

2023-06-16 08:29:54 [A pre-flight check failed. The software was successfully upgraded to 3.3.9.9 but could not be upgraded further (to 3.4.0.0). Check and correct the error, then try again.] ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '=' (SQL: selectp.submission_id,p.publication_id,ps.setting_valuefrompublicationsaspinner joinpublication_settingsaspsonps.publication_id=p.publication_idleft joinissuesasion CAST(i.issue_id AS CHAR(20)) =ps.setting_valuewhereps.setting_name= issueId andi.issue_idis null)

If I submit the above sql statement in phpmyadmin and set issueId like "issueId" it works.

Database is utf8mb4_general_ci and tables are InnoDB | utf8mb3_general_ci

How can I fix this?

[jonasraoni]

@loadbrain, you'll have to ensure all your columns, tables and database are using the same collation (which should be also properly configured in the config.inc.php) to avoid issues... On our side I think it's worth to do an extra check before proceeding with the upgrade.

The queries below might be helpful to identify the problems:

SELECT s.default_collation_name AS expected, t.table_collation AS found, t.table_name
FROM information_schema.schemata s
INNER JOIN information_schema.tables t
    ON t.table_schema = s.schema_name
    AND t.table_collation <> s.default_collation_name
WHERE s.schema_name = 'NAME_OF_YOUR_DATABASE';

SELECT s.default_collation_name AS expected, c.collation_name AS found, c.table_name, c.column_name
FROM information_schema.schemata s
INNER JOIN information_schema.columns c
    ON c.table_schema = s.schema_name
    AND c.collation_name <> s.default_collation_name
WHERE s.schema_name = 'NAME_OF_YOUR_DATABASE';

[loadbrain]

Genius! That worked! Had to change all to utf8mb4_general_ci, now upgrade runs... Well, at least until the next error... ;-) ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1005 Can't create tableojs33.submission_files(errno: 121 "Duplicate key on write or update") (SQL: alter tablesubmission_filesadd constraintsubmission_files_file_id_foreignforeign key (file_id) referencesfiles(file_id) on delete cascade) I will check this...

[jonasraoni]

I see two possible things here, perhaps you're the target of an issue that we saw within older database versions (it cannot have a foreign key with the same name as its index), if yes, upgrading the database to a newer version should fix.

Or perhaps you tried to run the upgrade from where you stopped last time. It might work sometimes, but not always, it's safer to start from a backup. Unless the error happened at the "PreflightMigration", this should be safe to fix and retry.

[loadbrain]

I continued, as you said, had an older database version. But next problem.... Column not found: 1054 Unknown column 'at.assoc_type' in 'where clause' (SQL: select distinctat.type_idfromannouncement_typesasatleft joinjournalsasconat.assoc_id=c.journal_idwherec.journal_idis null orat.assoc_type<> 256) I checked this too, announcement_types.assoc_type does not exist...

[loadbrain]

What confuses me, I installed a fresh ojs 3.4.0-0 too and announcement_types.assoc_type does not exist either... ???

[jonasraoni]

That's fine, the field was removed in OJS 3.4, but it should exist on OJS <= 3.3.0 🤔

[loadbrain]

Just saw this, https://github.com/pkp/pkp-lib/issues/9062, I will try...

[jonasraoni]

This user tried to run the upgrade again using a partially upgraded database, check well, your OJS 3.3 database backup should have this field 🤔

If it doesn't, then I think you should download a sample dataset here: https://github.com/pkp/datasets/tree/main/ojs/3_3_0-14/mysql and do a schema comparison (there are some tools out there to do it), to see if your database is missing something else.

[jonasraoni]

@asmecher Should I reopen this issue? I was thinking about adding a collation check on the upgrade. And AFAICR, the collation on the config.inc.php is used only for MySQL, perhaps we can drop this and/or grab a default value directly from the database.

[asmecher]

Let's come back to this if we start to receive a lot of reports from the user community (as we did with latin1/utf8 issues or MyISAM/InnoDB). These are issues of MySQL administration and roadmap more than our software, though it's good to help when there's an issue with broad impact.

[loadbrain]

This user tried to run the upgrade again using a partially upgraded database, check well, your OJS 3.3 database backup should have this field 🤔

If it doesn't, then I think you should download a sample dataset here: https://github.com/pkp/datasets/tree/main/ojs/3_3_0-14/mysql and do a schema comparison (there are some tools out there to do it), to see if your database is missing something else.

Yes, I will do this to find out what is the Problem. Thanks a lot for all your help!