Fix false schema drift from a charset or collation mismatch (MySQL and MariaDB)

Question

Why does Atlas report a MODIFY COLUMN drift on a generated column in MySQL or MariaDB where the only difference is a character-set introducer, such as _latin1'-' on one side and _utf8mb4'-' on the other, even though I changed nothing?

Answer

This is a known class of drift in MySQL and MariaDB. The cause is a charset or collation mismatch between your dev database and your target, not the object itself: when the two servers render the same expression under different connection charsets, they stamp a different character-set introducer (_charset'...') on its string literals. The definitions are logically identical; only their stored text differs, so the drift never converges.

It shows up most visibly on generated columns, but the same cause drifts expression DEFAULTs, functional indexes, CHECK constraints, and view definitions: anything whose canonical form is rendered by the server rather than taken from your source text.

Give the dev database the same charset settings as the target, using the command attribute of the docker block:

atlas.hcl

docker "mysql" "dev" {
  image   = "mysql:8.4" # match the target version
  command = [
    "--character-set-server=latin1",
    "--collation-server=latin1_swedish_ci",
    "--skip-character-set-client-handshake",
  ]
}

env "dev" {
  src = "file://schema.hcl"
  dev = docker.mysql.dev.url
}

--skip-character-set-client-handshake is the key flag: it forces every connection, including the one Atlas opens, onto character_set_server.

note

Use command, not init. The init attribute runs SET GLOBAL after startup, but skip-character-set-client-handshake is a startup-only flag.

Read the values to reproduce from the target, on a fresh connection without SET NAMES:

SHOW VARIABLES LIKE 'character\_set\_server';
SHOW VARIABLES LIKE 'collation\_server';

Why this happens

MySQL and MariaDB do not store the source text of these objects; they re-serialize the expression and attach a character-set introducer to every literal, chosen from the session's connection charset at CREATE/ALTER time (usually set by server configuration, not the client). Atlas normalizes both the desired and current states through the dev database and compares the results as text. A utf8mb4 dev writes the literal as _utf8mb4'...' while a latin1 target writes it as _latin1'...', so the two never match and Atlas keeps reporting drift.