Skip to main content

MY.L5.COLLATION_MIXED — A join across a collation boundary, which costs an index

  • Category: performance
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

This is an index loss, not an inconsistency​

MySQL cannot compare two strings under different collations. Faced with one, it converts the side with the lower coercibility — at runtime, per row — and a converted column can no longer be answered from its own index. The lookup that was a B-tree descent becomes a scan of the table.

Nothing in the query says so. EXPLAIN shows you the index is not used; it does not tell you the collation is why. That gap is the entire reason this is worth a rule: the cause is in the schema and the symptom is in the query plan, and the two are read by different people on different days.

Where the two sides have equal coercibility there is no lower side to convert, and the server refuses outright:

ERROR 1267 (HY000): Illegal mix of collations

which at least fails loudly.

Flagged​

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->string('customer_ref')->collation('utf8mb4_general_ci');
    $table->foreign('customer_ref')->references('ref')->on('customers');
});

Preferred​

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->string('customer_ref')->collation('utf8mb4_0900_ai_ci');
    $table->foreign('customer_ref')->references('ref')->on('customers');
});

MySQL refuses to create this — which is why it goes unnoticed​

Measured on MySQL 8.4.10. The server rejects a foreign key whose two ends collate differently, with error 3780, "Referencing column … and referenced column … are incompatible":

Pathforeign_key_checks = 1foreign_key_checks = 0
CREATE TABLE with the key inlinerefusedstill refused
ALTER TABLE … MODIFY a column already in a keyrefusedsucceeds — the mismatch persists

So nobody types this into existence. It arrives the other way: a restore script, a character-set sweep, or a migration tool that turns the checks off around a batch. Afterwards the constraint is there, the two columns disagree, every join across it converts — and nothing reports it. The server will not even let you recreate what it is already holding.

That is the whole argument for the rule. A state the engine refuses to create is the one nobody thinks to look for.

The foreign key is the case that matters​

A parent lookup that scans the child table is the same damage an unindexed foreign key does, reached from a different direction — and it is the join an application runs constantly.

Answering it needs both ends, and the far end lives on another table. The audit therefore hands each table its foreign keys with the collation on each side already read, and the rule compares them. A rule judges one object at a time; without that, the question could not be asked at all.

The second trigger is within a single table: a text column whose collation differs from the table default. It is compared against the default rather than against the other columns on purpose — "these two disagree" names no culprit, while "this one was set by hand" points at the line somebody wrote.

Why level 5 and performance, not level 6​

Filed as an idiom this would be switched off with the whole level-6 band — and a real index loss would disappear alongside a set of recommendations about taste. What this describes is not a preference: the query gets slower by a factor that grows with the table.

One finding per table, both triggers inside it​

A catalog finding is located at the object, and results are deduplicated by rule id and location. A verdict per column plus a verdict per foreign key would arrive as one, arbitrarily chosen, with the rest dropped without a word. So everything found about a table is said in a single message, with the edges and the columns named separately so you can still tell the two apart.

The same constraint decides what an unfollowable edge does — see below.

What is not flagged​

A _bin or _cs collation differing from its table is a decision, not a leftover: a hash, a token, a base64 payload or a case-sensitive technical key is stored that way on purpose, and unifying it would collapse the distinction the column exists for. Non-text columns never appear at all — they hold nothing to collate.

There is no rule-specific allow list. The audit ignore list already suppresses a (rule, object) pair, and there the finding stays counted and listed under what hid it:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'MY.L5.COLLATION_MIXED', 'objects' => ['shop.legacy_imports']],
        ],
    ],
],

When an edge leaves the audited scope​

A foreign key pointing at a table in a schema this run was not asked to read is answered undetermined, with the reason referenced_object_not_in_scope — never pass. From inside the schema that was read, an edge nobody followed and an edge that matched look identical. Widening sqlens.catalog.schemas to include the other schema turns it into a real answer.

If the table has a genuine mismatch and an unfollowable edge, the finding is the mismatch and it names the unchecked edge in the same message — a fail and an undetermined cannot both survive at one location, and dropping the unchecked one would hide it.

Sources​