Bug Report: sidecar sync alter table with ALGORITHM = COPY

[francisco-otterai]

Overview of the Issue

When vttablet sidecar runs (tablet start or tablet going to serving mode), alter tables to match desired schema are done with ALGORITHM=COPY which on the mysql database behaves as:

1. Create new table
2. Copy rows
3. Update schema on new table
4. Swap tables
5. Drop old table

In particular for unmanaged tablets during migrations, an external mysql instance with a big innodb buffer pool this create semaphore locks and can bring DB to a halt for several minutes.
Since the _vt tables are fairly small, there should be no need to do the alter without it on mysql 8.0 and above.
Adding the ALGORITHM = COPY is done here:

vitess/go/vt/sidecardb/sidecardb.go

Lines 391 to 400 in 599f646

	// findTableSchemaDiff gets the diff which needs to be applied
	// to the current table schema in order to reach the desired one.
	// The result will be an empty string if they match.
	// This will be a CREATE statement if the table does not exist
	// or an ALTER if the table exists but has a different schema.
	func (si *schemaInit) findTableSchemaDiff(tableName, current, desired string) (string, error) {
	hints := &schemadiff.DiffHints{
	TableCharsetCollateStrategy: schemadiff.TableCharsetCollateIgnoreEmpty,
	AlterTableAlgorithmStrategy: schemadiff.AlterTableAlgorithmStrategyCopy,
	}

vitess/go/vt/sidecardb/sidecardb.go

Line 399 in 599f646

AlterTableAlgorithmStrategy: schemadiff.AlterTableAlgorithmStrategyCopy,

#12464

Reproduction Steps

1. Generate a change on one table
   ALTER TABLE vreplication_log CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci;
2. Restart vttablet
3. Check vttablet.INFO
   I0206 11:08:32.098253 164677 sidecardb.go:415] Applying DDL for table vreplication_log: I0206 11:08:32.187492 164677 sidecardb.go:462] Applied DDL ALTER TABLE vreplication_logMODIFY COLUMNmessage text NOT NULL, ALGORITHM = COPY for table _vt.vreplication_log (vreplication) during sidecar database initialization

Binary Version

vttablet version Version: 22.0.1

Operating System and Environment details

PRETTY_NAME="Ubuntu 24.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="24.04"
VERSION="24.04.3 LTS (Noble Numbat)"
VERSION_CODENAME=noble
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=noble
LOGO=ubuntu-logo

Log Fragments

I0206 11:08:32.098253  164677 sidecardb.go:415] Applying DDL for table vreplication_log:
ALTER TABLE `vreplication_log` MODIFY COLUMN `message` text NOT NULL, ALGORITHM = COPY
I0206 11:08:32.187492  164677 sidecardb.go:462] Applied DDL ALTER TABLE `vreplication_log` MODIFY COLUMN `message` text NOT NULL, ALGORITHM = COPY for table _vt.vreplication_log (vreplication) during sidecar database initialization