Skip to main content

MY.L6.COLLATION_LEGACY — A utf8mb4 object still sorting by a pre-8.0 collation

  • Category: idiom
  • Level: 6
  • 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.

The difference is wrong order, not slow order​

utf8mb4_general_ci predates the Unicode Collation Algorithm implementation MySQL 8.0 brought. It compares by a shortened table that treats whole groups of characters as equal, so it does not merely sort differently from what a reader expects — for several languages it sorts wrongly, and it reports characters as equal that a user would not call equal.

utf8mb4_unicode_ci and utf8mb4_unicode_520_ci are better, and still pinned to Unicode versions from before 8.0.

utf8mb4_0900_ai_ci implements UCA 9.0.0 and has been the server's own default since MySQL 8.0. An object still on a pre-8.0 collation is almost always carrying a decision nobody made: it was created before 8.0, or by a tool that spelled out the old default.

Flagged​

Schema::create('customers', function (Blueprint $table) {
    $table->id();
    $table->string('name')->collation('utf8mb4_general_ci');
});

Preferred​

Schema::create('customers', function (Blueprint $table) {
    $table->id();
    $table->string('name')->collation('utf8mb4_0900_ai_ci');
});

Check the unique indexes before you convert​

This is the false positive worth meeting before the maintenance window rather than during it.

Changing a collation changes what counts as equal, and therefore what counts as a duplicate. Rows that have coexisted happily for years under utf8mb4_general_ci can collide against a UNIQUE index under utf8mb4_0900_ai_ci — and the ALTER then fails on data nobody touched. It is not a reason to stay on the old collation; it is a reason to look first.

ALTER TABLE shop.customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

MySQL performs that as a copy of the whole table. The lint suite classifies the statement and the downtime class lives there, on MY.L2.COPY_ALTER_CHARSET; an audit observation about a state carries none.

Three levels, one finding per object​

The database default, the table default and the individual column are all judged, because MySQL inherits down all three. The database default is the invisible one: it decides what the next table gets, and no existing table reveals it.

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

That changes no existing table — it stops the spread without touching data.

A table with forty legacy columns produces one finding naming all forty. A catalog finding is located at the object and results are deduplicated by rule id and location, so a verdict per column would arrive as one with the rest dropped silently, and a reader would fix the column named and believe the table was done.

What is not flagged​

  • _bin and _cs collations. A binary or case-sensitive collation on a hash, a token or a case-sensitive technical key is a decision, and usually the right one. Reporting it would be the rule telling somebody to break their own uniqueness.
  • Language-specific 0900 collations such as utf8mb4_de_pb_0900_ai_ci. They are the modern family; picking one is a considered act, not a leftover.
  • Anything that is not utf8mb4. A latin1_swedish_ci column has a character-set problem, and that is MY.L6.CHARSET_NOT_UTF8MB4. Reporting it here as well would make two rules shout about one object — and fixing the character set fixes the collation with it.

To accept a legacy collation deliberately, use the audit ignore list rather than a rule-specific setting, so the decision stays counted and listed under what hid it:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'MY.L6.COLLATION_LEGACY', 'objects' => ['shop.legacy_imports']],
        ],
    ],
],

Why it is a separate id from the character set​

MY.L6.CHARSET_NOT_UTF8MB4 asks what an object can store; this one asks how it orders and compares. They fail differently, they are fixed at different times, and a project may reasonably accept one and not the other.

Under a single id, an ignore-list entry for the storage question would silence the sorting one too, and the report would lose the ability to say which of the two somebody had actually decided about. Rule ids are public API from 1.0 on, so the split has to live in the id itself rather than in the prose around it.

When the collation could not be read​

A table that reports no collation is answered undetermined, with the reason text_encoding_unknown — never pass. Some storage engines carry none, and the reader keeps an absent value absent rather than substituting a plausible default. Its columns are left unjudged with it: a column collation without the table default it may be inheriting cannot be placed.

Sources​