Rust Says the Names Differ. MySQL Rejects the Duplicate.

Article · Through the layers

A signup form accepts a name. Rust compares it with another string and says they differ. MySQL refuses the insert with a duplicate-key error. There is no contradiction: the two comparisons may use different rules.

Rust string equality does not fold case or ignore accents. A MySQL text column has a collation, and a unique index follows that column's comparison rules. If the unique index decides whether a name is available, its collation decides which names count as the same.

Take résumé and resume. They contain different bytes. In a column using the explicitly chosen utf8mb4_0900_ai_ci collation, their difference in accents does not make them distinct for this comparison. MySQL's Unicode collation documentation describes the available families and their rules. The name ending is useful information, but testing the actual cases your product accepts is still necessary.

Put the disagreement in one test

The fixture creates a unique VARCHAR column using that collation. It also creates a second table with a VARBINARY primary key, for a separate byte-identity check. The second table is not presented as a replacement signup policy. It gives us a controlled comparison.

async fn names(c: &mut sqlx::MySqlConnection) -> Result<()> {
    let first = "résumé";
    let second = "resume";
    assert_ne!(first, second);
    sqlx::query("INSERT INTO b_names_folded(name) VALUES(?)")
        .bind(first)
        .execute(&mut *c)
        .await?;
    let collision = sqlx::query("INSERT INTO b_names_folded(name) VALUES(?)")
        .bind(second)
        .execute(&mut *c)
        .await
        .unwrap_err();
    assert_eq!(number(&collision), Some(1062));
    let composed = "é";
    let decomposed = "e\u{301}";
    assert_ne!(composed.as_bytes(), decomposed.as_bytes());
    for value in [composed, decomposed] {
        sqlx::query("INSERT INTO b_names_bytes(name) VALUES(?)")
            .bind(value.as_bytes())
            .execute(&mut *c)
            .await?;
    }
    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM b_names_bytes")
        .fetch_one(&mut *c)
        .await?;
    assert_eq!(count, 2);
    Ok(())
}

The full source is mysql_collation.rs. It pins MySQL 8.4.13 and SQLx 0.9.0, and configures the connection for utf8mb4. The expected duplicate is checked by its database error number, not by comparing a full English error message.

The first pair has visibly different spellings. The second pair is more awkward: the character é can be represented as one composed character or as an e followed by a combining accent. Both are valid UTF-8. Their byte sequences differ, and the binary column keeps them as two distinct keys.

The expected checks cover this accent collision and these two byte encodings. They remain to be recorded against the pinned engine. Case, accents, spaces and the scripts accepted by the product need their own cases. A name can look confusingly similar to another name even when neither Rust equality nor the selected collation treats it as equal.

Display text and identity can have different jobs

A display name often should preserve what the person entered. A login key might use a documented normalized form, or deliberately use the database collation's equality. Keeping these as separate fields can make the choice visible. It also gives the interface somewhere to explain a collision without rewriting the user's display text behind their back.

If the application computes a normalized key, use the same algorithm and version across every writer. A second service with a slightly different rule can accept a name the first service would reject. Test the stored key and the final unique constraint together. The database should still decide which concurrent request wins; a prior “is this name free?” query is only a hint to the interface.

Lowercasing every identifier would introduce another rule. Account handles, email addresses, external document IDs and filenames can each have different equality rules. Check the system that defines that identifier before changing its case or removing accents. Another service may need the original spelling.

Binary text is another choice worth checking

It is tempting to change a collation to something ending in _bin and assume the column now behaves exactly like a byte array. MySQL documents differences between binary strings and binary collations, including character handling and trailing-space behavior. The distinction depends on the selected types and collation.

That is why the fixture uses VARBINARY for its byte check and prints the selected text collation's pad attribute separately. It does not generalize from one text collation to all binary-looking names. A trailing-space case should be added explicitly if spaces can enter the identifier.

The same care applies when binding values. Text encoding, column collation and Rust comparison are different layers. A correct Unicode round trip proves that the characters survived storage; it does not prove that a unique index uses the comparison you expected. The MySQL and MariaDB contract example keeps those checks separate for that reason.

Changing the rule on an existing table

Before changing a unique column's collation, search for records that would become equal under the new rule. Two existing accounts may be distinct today and collide tomorrow. The migration needs a decision about those accounts, not only a new schema declaration.

Run the proposed rule against representative names, including the difficult cases people actually use. Keep the examples in the repository with their expected outcomes. Include a concurrent insert test so the uniqueness guarantee remains in the database even if both requests pass application validation.

Check every unique index touched by the request when mapping a duplicate error. A duplicate account handle and a duplicate request identifier may need different responses. An error number identifies a class of failure; the operation and constraint context explain what the user can do next.

Error handling should then match the rule. A duplicate-key response can say the chosen name is unavailable and let the person choose another. It should not expose an internal database message or claim that two text strings are literally identical. Explaining that the service ignores case or accents is more useful than asking a user to debug Unicode.

For the signup form, write down whether résumé and resume compete for one name, and what happens to leading or trailing spaces. Show that rule when someone hits a collision. Keep those exact examples in the migration checks, so changing a database default cannot silently change who gets a name.