Bug Report: CopySchemaShard / schema validation fails identical schemas due to MySQL's charset/collation display in SHOW CREATE TABLE

[harshita-3108]

Overview of the Issue

Overview of the Issue

CopySchemaShard (and the ValidateSchemaShard / ValidateSchemaKeyspace commands) compare schemas by string-comparing normalized SHOW CREATE TABLE output (tmutils.DiffSchema, go/vt/mysqlctl/tmutils/schema.go). However, MySQL 8.0+ : replaying a table's own SHOW CREATE TABLE output produces a table whose SHOW CREATE TABLE output differs textually, even though the two tables are semantically identical.

This makes CopySchemaShard fail its post-copy verification (and ValidateSchema* report false diffs) for any table that has:

• a table default collation that is not the charset's server default (e.g. utf8mb4 / utf8mb4_general_ci while the server default for utf8mb4 is utf8mb4_0900_ai_ci), and

• at least one string column added later via ALTER TABLE ... ADD COLUMN without an explicit CHARACTER SET/COLLATE clause.

• A column added with no clause inherits the table default; SHOW CREATE TABLE prints only COLLATE utf8mb4_general_ci for it.

• When Vitess replays that output on the target shard, the COLLATE clause is now explicit in the DDL, so MySQL marks the derived charset as explicit too, and the target prints CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci.

• Identical table, different text → CopySchemaShard was not successful because the schemas between the two tablets ... differ.

The copy itself succeeds; only the verification fails.

Suggested fix: use semantic comparison before declaring a diff.

Reproduction Steps

Reproduction Steps

Reproducible with same MySQL server alone to explain the issue (this is what CopySchemaShard does internally):
copy schema shard uses show create table to create the destination table, so we don't need actual commands to reproduce it, mysql itself can.

-- server defaults: utf8mb4 / utf8mb4_0900_ai_ci (stock 8.0+)
CREATE DATABASE repro_src;
CREATE DATABASE repro_dst;

-- 1. Source table: table default collation differs from the charset's server default
CREATE TABLE repro_src.t (
  id varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2. Add a column with no charset/collate clause
ALTER TABLE repro_src.t ADD COLUMN note varchar(20) DEFAULT NULL;

SHOW CREATE TABLE repro_src.t;
-- `note` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL      <-- COLLATE only

-- 3. Replay the SHOW CREATE TABLE output verbatim in repro_dst (= what CopySchemaShard does)

SHOW CREATE TABLE repro_dst.t;
-- `note` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL   <-- both charset and collate

The two tables are semantically identical, but the outputs differ textually

CopySchemaShard was not successful because the schemas between the two tablets ... differ:
source: ... eci varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL, ...
dest: ... eci varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

(only the `CHARACTER SET` display differs on the ALTER-added columns; all charsets/collations are actually identical).

### Binary Version

```sh
vtctldclient --version
vtctldclient version Version: 24.0.0 (Git revision  branch '') built on Thu Apr 30 12:39:11 UTC 2026 by brew@Tahoe-arm64.local using go1.26.2 darwin/arm64

Operating System and Environment details

uname -sr
Darwin 25.2.0

 uname -m
arm64

sw_vers -productVersion

26.2

Log Fragments

[mattlord]

I'm assuming that this is the result of using different major MySQL versions between the source and target. But this is something that should work. We need to leverage schemadiff here rather than a byte comparison between both sides.

[harshita-3108]

I ran on the same mysql server and just two diff schemas , seems like this is just mysql behaviour.