Bug Report: SidecarDB init does not apply table level changes

[mattlord]

Overview of the Issue

The vitess sidecar database (_vt by default) is initialized during tablet init in v16+ after: #11520

At the end of the process, the sidecar schema should match what is defined in the <table_name>.sql files here: https://github.com/vitessio/vitess/tree/main/go/vt/sidecardb/schema

As part of investigating a case where this fix: #15859 did NOT resolve the error noted here: #11003 we could see that the error was still occurring because while we DID apply the column level changes here: https://github.com/vitessio/vitess/pull/15859/files#diff-53dc8d559ba63c007358a539683251457de57916552143d1ff5a1e673978615e we did NOT apply the table level change:

 - ) ENGINE = InnoDB
 + ) ENGINE = InnoDB, CHARACTER SET = utf8mb3

Reproduction Steps

git checkout main
make build

cd examples/local
alias vtctldclient='command vtctldclient --server=localhost:15999'

./101_initial_cluster.sh

mysql commerce/0 -e "show create table _vt.schema_version\G"

vtctldclient ApplySchema --sql "alter table _vt.schema_version CHARSET=utf8mb3" commerce

mysql commerce/0 -e "show create table _vt.schema_version\G"

cat ../../go/vt/sidecardb/schema/vreplication/schema_version.sql

sed -i "" 's/ENGINE = InnoDB/ENGINE = InnoDB, CHARSET utf8mb4/' ../../go/vt/sidecardb/schema/vreplication/schema_version.sql

git diff | cat

for i in 100 101 102; do
        CELL=zone1 KEYSPACE=commerce TABLET_UID=$i ../common/scripts/vttablet-down.sh
done

for i in 100 101 102; do
        CELL=zone1 KEYSPACE=commerce TABLET_UID=$i ../common/scripts/vttablet-up.sh
done

mysql commerce/0 -e "show create table _vt.schema_version\G"

You can see that the table's character set is not updated:

...

❯ mysql commerce/0 -e "show create table _vt.schema_version\G"
*************************** 1. row ***************************
       Table: schema_version
Create Table: CREATE TABLE `schema_version` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pos` varbinary(10000) NOT NULL,
  `time_updated` bigint NOT NULL,
  `ddl` blob,
  `schemax` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

diff --git a/go/vt/sidecardb/schema/vreplication/schema_version.sql b/go/vt/sidecardb/schema/vreplication/schema_version.sql
index 2b7cbc08de..ac28f036ee 100644
--- a/go/vt/sidecardb/schema/vreplication/schema_version.sql
+++ b/go/vt/sidecardb/schema/vreplication/schema_version.sql
@@ -22,4 +22,4 @@ CREATE TABLE IF NOT EXISTS schema_version
     ddl          BLOB DEFAULT NULL,
     schemax      LONGBLOB         NOT NULL,
     PRIMARY KEY (id)
-) ENGINE = InnoDB
+) ENGINE = InnoDB, CHARSET utf8mb4

... tablet restarts ...

❯ mysql commerce/0 -e "show create table _vt.schema_version\G"
*************************** 1. row ***************************
       Table: schema_version
Create Table: CREATE TABLE `schema_version` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pos` varbinary(10000) NOT NULL,
  `time_updated` bigint NOT NULL,
  `ddl` blob,
  `schemax` longblob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

Binary Version

❯ vtgate --version
vtgate version Version: 21.0.0-SNAPSHOT (Git revision 2438a79a10e22addd3d7d9b3a911bce44cfae3ae branch 'vdiff_start_ctrl') built on Tue Aug 27 12:42:21 EDT 2024 by matt@pslord.local using go1.23.0 darwin/arm64

Operating System and Environment details

N/A

Log Fragments

❯ grep -A18 "Applying DDL for table schema_version" "${VTDATAROOT}"/tmp/vttablet.*INFO*
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057:I0827 19:43:00.133208   24057 sidecardb.go:415] Applying DDL for table schema_version:
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-CREATE TABLE IF NOT EXISTS `_vt`.`schema_version` (
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`id` int NOT NULL AUTO_INCREMENT,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`pos` varbinary(10000) NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`time_updated` bigint NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`ddl` blob,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`schemax` longblob NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	PRIMARY KEY (`id`)
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-) ENGINE InnoDB
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-I0827 19:43:00.134740   24057 sidecardb.go:462] Applied DDL CREATE TABLE IF NOT EXISTS `_vt`.`schema_version` (
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`id` int NOT NULL AUTO_INCREMENT,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`pos` varbinary(10000) NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`time_updated` bigint NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`ddl` blob,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	`schemax` longblob NOT NULL,
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-	PRIMARY KEY (`id`)
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-) ENGINE InnoDB for table _vt.schema_version (vreplication) during sidecar database initialization
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-I0827 19:43:00.134832   24057 sidecardb.go:415] Applying DDL for table vreplication:
/opt/vtdataroot/tmp/vttablet.pslord.matt.log.INFO.20240827-194258.24057-CREATE TABLE IF NOT EXISTS `_vt`.`vreplication` (