Diagnose incompatible collations in comparisons

Understand the likely causes

• Columns, literals, parameters and stored objects can inherit different collations. MariaDB resolves expressions using coercibility priorities; equally strong incompatible operands can fail. Changing a database default does not convert every existing column, and a MySQL-only collation name may not exist on MariaDB.

Inspect both column definitions and the session

Reduce the failing statement to its comparison and identify both source columns or expressions. Inspect column metadata and the connection character-set settings in an already authorized session. A table default is not sufficient evidence because a column can override it. If the error occurs only through an application, compare its bound-parameter connection settings with the working tool. Values returned here are environment-dependent and no server output is fabricated.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'harbor_lab'
  AND TABLE_NAME IN ('customers', 'imported_customers')
ORDER BY TABLE_NAME, ORDINAL_POSITION;
SELECT @@character_set_connection, @@collation_connection;

Inspect expressions, not only storage

CHARSET(), COLLATION() and COERCIBILITY() describe an expression. An explicit COLLATE has stronger priority than a column or ordinary literal, but choosing a stronger rule does not make it the correct business rule. Concatenations, casts and routine parameters can introduce another expression collation. The literal example inspects a deliberately chosen utf8mb4 expression; it neither rewrites stored data nor proves what an application connection sends.

SELECT CHARSET(_utf8mb4'Harbor' COLLATE utf8mb4_unicode_ci),
 COLLATION(_utf8mb4'Harbor' COLLATE utf8mb4_unicode_ci),
 COERCIBILITY(_utf8mb4'Harbor' COLLATE utf8mb4_unicode_ci);

Apply a narrow comparison policy

When both operands are utf8mb4 and the business wants the chosen case-insensitive comparison, an explicit compatible COLLATE on both operands can make a read-only diagnostic join unambiguous. If character sets differ, convert the expression to a shared capable charset before collating, after checking representability. A binary comparison has different equality rules and is not a universal repair. Query-level conversion can also affect index use, so inspect the resulting plan and result set.

SELECT c.customer_id, i.import_id
FROM customers AS c JOIN imported_customers AS i
ON c.label COLLATE utf8mb4_unicode_ci
 = i.label COLLATE utf8mb4_unicode_ci
ORDER BY c.customer_id, i.import_id;

Separate diagnosis from a schema migration

If inconsistent definitions are the root cause, propose an explicit schema and connector policy through a separate reviewed migration. Before converting stored columns, test unique-key collisions, case and accent behavior, sort order and Arabic round trips on a copy. For an explicitly named Unicode character set without COLLATE, default collation selection changed in MariaDB 11.5; the server default charset/collation changed in 11.6. MariaDB 11.8 is the first LTS containing both, so upgrading from an earlier LTS can reveal both changes together. This read-only guide deliberately contains no ALTER TABLE or global settings; removing an error message alone does not prove the new equality semantics are safe.

Keep it from recurring

• Declare charset and collation deliberately in schema migrations and connector configuration. Inventory imported definitions and test equality-sensitive unique keys when changing versions or products. Keep language display requirements separate from business identifier comparison rules.