CopySchemaShard doesn't work properly with collation and charset defaults set on table

[sverch]

Overview of the Issue

SHOW CREATE TABLE can sometimes return a CREATE TABLE statement that is different from how the table was actually created. In this case, setting the default COLLATION and CHARSET options for the table can result in SHOW CREATE TABLE returning a CREATE TABLE command that explicitly sets the COLLATE option on varchar fields.

The end result is that the vitess CopySchemaShard command will create a table on the destination shard with a different CREATE TABLE statement than it was originally created with, resulting in two slightly different tables. This will cause the CopySchemaShard command to report failure.

Reproduction Steps

In MySQL, on a tablet, create a table like this:

CREATE TABLE test (
 key_myKeyStringField varchar(750) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Show create table will display:

key_myKeyStringField varchar(750)  COLLATE utf8mb4_bin DEFAULT NULL

If you create the table using:

CREATE TABLE test (
 key_myKeyStringField varchar(750) COLLATE utf8mb4_bin DEFAULT NULL
)

or

CREATE TABLE test (
 key_myKeyStringField varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
)

Show create table will display:

CREATE TABLE test (
 key_myKeyStringField varchar(750) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
)

If you run CopySchemaShard with a source of the table created with the first CREATE TABLE statement above, it will create a table using the second CREATE TABLE statement, causing the two tables to be different and the command to fail.

It's possible that there are some cases where if the connection's charset/collation differ to the table, SHOW CREATE TABLE may report back something that is more verbose, so the results of this reproduction may vary.

[sougou]

The comment on the compare schema explicitly states that we expect that the source and targets have the same default character set settings. Trying to make it smarter is essentially an endless complication because mysql has default settings on every schema entity and they layer on top of each other.

The only reasonable fix for the problem is to add an option to CopySchemaShard to make it not fail the comparison. This means that we leave it to the user to determine that the schemas match.

We should still compare and report the differences. That will tell the users that they need to verify this mismatch.

[aquarapid]

There's a similar issue for the "CREATE DATABASE" step of CopySchemaShard, where things might differ slightly because of differences in MySQL versions. Note that we do that as a separate step, so a "force" flag would have to bypass a failure on that step too.

[aquarapid]

I am adding a patch to add a --skip-verify option to CopySchemaShard, that will do the copy as usual, but skip the post-copy verify. Since this is an opt-in option, I'm not displaying the differences in a case like that.

[sverch]

@sougou I was rereading your comment above and I actually had a question, particularly about this:

Trying to make it smarter is essentially an endless complication because mysql has default settings on every schema entity and they layer on top of each other.

If someone sets a table default, would that override any layers before that? Would it be possible to at least unambiguously handle some cases automatically that way, or do you need to know the full picture to make any reasonable assertion?

[sougou]

That may work and could be worthy workaround.

[morgo]

A workaround has been merged, so I am removing this from the v6.0 milestone.

[mattlord]

I'm closing this as it's no longer an issue. If you feel that I'm missing something here please let me know and we can re-open it. Thanks!