Database collation - mismatch on upgrade 2.1 - 2.4

[sipsmi]

I seem to have gotten in a mess with my database. The system seems to work after an upgrade except for the advanced logbook (so far testing) which throws an error on an MSSQL join where the "=" cannot work owing to collation. Is there a simple fix for this or is there a database update script?

Error Number: 1267

Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

[sipsmi]

I changed the tables in the query to the same collation ( utf8mb4_0900_ai_ci ) and still get the error:

A Database Error Occurred
Error Number: 1267
Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
SELECT `TABLE_HRD_CONTACTS_V01`.*, `station_profile`.*, `dxcc_entities`.*, coalesce(dxcc_entities_2.name, "- NONE -") as station_country, `dxcc_entities_2`.`end` as `station_end`, `eQSL_images`.`image_file` as `eqsl_image_file`, `lotw_users`.`callsign` as `lotwuser`, `lotw_users`.`lastupload`
FROM `TABLE_HRD_CONTACTS_V01`
LEFT JOIN `dxcc_entities` ON `TABLE_HRD_CONTACTS_V01`.`col_dxcc` = `dxcc_entities`.`adif`
LEFT JOIN `station_profile` ON `station_profile`.`station_id` = `TABLE_HRD_CONTACTS_V01`.`station_id`
LEFT OUTER JOIN `dxcc_entities` as `dxcc_entities_2` ON `station_profile`.`station_dxcc` = `dxcc_entities_2`.`adif`
LEFT OUTER JOIN `eQSL_images` ON `TABLE_HRD_CONTACTS_V01`.`COL_PRIMARY_KEY` = `eQSL_images`.`qso_id`
LEFT OUTER JOIN `lotw_users` ON `TABLE_HRD_CONTACTS_V01`.`COL_CALL` = `lotw_users`.`callsign`
WHERE `COL_PRIMARY_KEY` = '810'</p><p>Filename: models/Logbook_model.php</p><p>Line Number: 1307

[sipsmi]

OK - managed to drill down and see the issue. Whilst the database and tables now are utf8mb4_0900_ai_ci for some, probably legacy, reason lotw_users.callsign only was utf8mb4_general_ci Changed this and now all OK.

[phl0]

This is probably an issue that @AndreasK79 should look into.

[AndreasK79]

Well, the migration script didn't contain any collate, so not sure why this is happening.

I guess we could always add another migration script like 084:

foreach ($tables as $table) {
    $this->db->query('ALTER TABLE ' . $table . ' CONVERT TO CHARACTER SET ' . $this->db->char_set . ' COLLATE ' . $this->db->dbcollat);
}

Thoughts @magicbug?

[sipsmi]

That would not have fixed it, in the end I had to alter the column lotw_users.callsign which was different to the table default.

[AndreasK79]

@sipsmi I see, well, glad you fixed it!