Bug Report: Illegal mix of collations for operation 'UNION' (errno 1271) (sqlstate HY000) in health streamer

[arthurschreiber]

Overview of the Issue

We see the following error message pop up in the logs of our primaries:

E0507 03:30:38.043062 3983967 health_streamer.go:150] periodic schema reload failed in health stream: Illegal mix of collations for operation 'UNION' (errno 1271) (sqlstate HY000) during query: SELECT DISTINCT table_name
FROM (
        SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
        FROM information_schema.columns
        WHERE table_schema = database()
        UNION ALL
        SELECT table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
        FROM _vt.schemacopy
        WHERE table_schema = database()
) _inner
GROUP BY table_name, column_name, ordinal_position, character_set_name, collation_name, data_type, column_key
HAVING COUNT(*) = 1

It doesn't seem to have any noticeable impact on any of our use cases, but I think this has something to do with the charsets/collations of the MySQL Server and the _vt.schemacopy table being different.

Reproduction Steps

N/A

Binary Version

This seems to affect vttablet up from at least v16 to v18. v19 seems to no longer have a _vt.schemacopy table and is not running this query.

Operating System and Environment details

N/A

Log Fragments

N/A

[arthurschreiber]

The schema for schemacopy does not explicitly specify the charset/collations to use for this table (see

vitess/go/vt/sidecardb/schema/schematracker/schemacopy.sql

Lines 17 to 28 in 610bdaf

	CREATE TABLE IF NOT EXISTS schemacopy
	(
	`table_schema` varchar(64) NOT NULL,
	`table_name` varchar(64) NOT NULL,
	`column_name` varchar(64) NOT NULL,
	`ordinal_position` bigint unsigned NOT NULL,
	`character_set_name` varchar(32) DEFAULT NULL,
	`collation_name` varchar(32) DEFAULT NULL,
	`data_type` varchar(64) NOT NULL,
	`column_key` varchar(3) NOT NULL,
	PRIMARY KEY (`table_schema`, `table_name`, `ordinal_position`)
	) ENGINE = InnoDB

):

CREATE TABLE IF NOT EXISTS schemacopy
(
    `table_schema`       varchar(64)     NOT NULL,
    `table_name`         varchar(64)     NOT NULL,
    `column_name`        varchar(64)     NOT NULL,
    `ordinal_position`   bigint unsigned NOT NULL,
    `character_set_name` varchar(32) DEFAULT NULL,
    `collation_name`     varchar(32) DEFAULT NULL,
    `data_type`          varchar(64)     NOT NULL,
    `column_key`         varchar(3)      NOT NULL,
    PRIMARY KEY (`table_schema`, `table_name`, `ordinal_position`)
) ENGINE = InnoDB

[arthurschreiber]

MySQL is configured with character_set_server and character_set_system set to utf8mb3. collation_server is set to utf8mb3_unicode_ci.

[arthurschreiber]

_vt.schemacopy was created with the server's configured charset/collation:

mysql> SHOW CREATE TABLE _vt.schemacopy;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| schemacopy | CREATE TABLE `schemacopy` (
  `table_schema` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `table_name` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `column_name` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `ordinal_position` bigint unsigned NOT NULL,
  `character_set_name` varchar(32) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `collation_name` varchar(32) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `data_type` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `column_key` varchar(3) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`table_schema`,`table_name`,`ordinal_position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[arthurschreiber]

Looks like information_schema.columns uses a set of various column collations (which differs from the _vt.schemacopy table):

mysql> SHOW FULL COLUMNS FROM information_schema.columns;
+--------------------------+----------------------------+--------------------+------+-----+---------+-------+------------+---------+
| Field                    | Type                       | Collation          | Null | Key | Default | Extra | Privileges | Comment |
+--------------------------+----------------------------+--------------------+------+-----+---------+-------+------------+---------+
| TABLE_CATALOG            | varchar(64)                | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| TABLE_SCHEMA             | varchar(64)                | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| TABLE_NAME               | varchar(64)                | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| COLUMN_NAME              | varchar(64)                | utf8mb3_tolower_ci | YES  |     | NULL    |       | select     |         |
| ORDINAL_POSITION         | int unsigned               | NULL               | NO   |     | NULL    |       | select     |         |
| COLUMN_DEFAULT           | text                       | utf8mb3_bin        | YES  |     | NULL    |       | select     |         |
| IS_NULLABLE              | varchar(3)                 | utf8mb3_general_ci | NO   |     |         |       | select     |         |
| DATA_TYPE                | longtext                   | utf8mb3_bin        | YES  |     | NULL    |       | select     |         |
| CHARACTER_MAXIMUM_LENGTH | bigint                     | NULL               | YES  |     | NULL    |       | select     |         |
| CHARACTER_OCTET_LENGTH   | bigint                     | NULL               | YES  |     | NULL    |       | select     |         |
| NUMERIC_PRECISION        | bigint unsigned            | NULL               | YES  |     | NULL    |       | select     |         |
| NUMERIC_SCALE            | bigint unsigned            | NULL               | YES  |     | NULL    |       | select     |         |
| DATETIME_PRECISION       | int unsigned               | NULL               | YES  |     | NULL    |       | select     |         |
| CHARACTER_SET_NAME       | varchar(64)                | utf8mb3_general_ci | YES  |     | NULL    |       | select     |         |
| COLLATION_NAME           | varchar(64)                | utf8mb3_general_ci | YES  |     | NULL    |       | select     |         |
| COLUMN_TYPE              | mediumtext                 | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| COLUMN_KEY               | enum('','PRI','UNI','MUL') | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| EXTRA                    | varchar(256)               | utf8mb3_general_ci | YES  |     | NULL    |       | select     |         |
| PRIVILEGES               | varchar(154)               | utf8mb3_general_ci | YES  |     | NULL    |       | select     |         |
| COLUMN_COMMENT           | text                       | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| GENERATION_EXPRESSION    | longtext                   | utf8mb3_bin        | NO   |     | NULL    |       | select     |         |
| SRS_ID                   | int unsigned               | NULL               | YES  |     | NULL    |       | select     |         |
+--------------------------+----------------------------+--------------------+------+-----+---------+-------+------------+---------+

[arthurschreiber]

Looks like the tables that the information_schema.columns view is based on all are defined with CHARSET=utf8mb3 and COLLATE=utf8mb3_bin on MySQL 8.0 (and later).

Some columns defined in the view have an explicit encoding defined (like COLUMN_NAME ), while others fall back to the server's encoding. 🙈

[arthurschreiber]

On MySQL 5.7, information_schema.columns is utf8_general_ci. 🙈

[arthurschreiber]

To fix this and make it work correctly on MYSQL 5.7 and 8.0, I propose the following two changes:

1. Update _vt.schemacopy to match the exact collations used by MySQL 8.0.
2. Change the UNION query that fetches the data to explicitly change the collation to match MySQL 8.0.

[arthurschreiber]

I opened a PR for this over at #15859.

I opted to not do any changes to the definition of the _vt.schemacopy table, as that seemed to cause a very large number of test failures that I don't feel makes a ton of sense to investigate (especially because this table has been removed starting with v19.x).

Instead, I modified the UNION query to specify collations explicitly for both parts of the UNION, and that seems to do the trick.

[dbussink]

Closing as the linked PR is merged.