Bug Report: Collation issues with unmanaged tablet

[quinox]

Overview of the Issue

Preemptive: I have no idea if this should be considered a configuration isssue on my side, a documentation issue or a software issue. I found a workaround so I'm not stuck.

I was following the User Guide: Unmanaged Tablet to hook up my Docker Vitess to a legacy MySQL DB. During the startup of the tablet I received the following error:

periodic schema reload failed in health stream: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
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 c                                                                                                  
  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

According to the MySQL documentation information_schema.columns is either utf8_general_ci (5.7) or utf8mb3_general_ci (8.0). When I inspected _vt.schemacopy my columns were utf8_unicode_ci, I assume because of my server defaults.

I managed to get my tablet online by dropping _vt.schemacopy and recreating it myself using utf8_general_ci as collation. Now my tablet is happily online.

Checking the output of vttablet --help I did not see a way of specifying the collation to use.

Reproduction Steps

1. Run a MySQL 5.7 with the following settings:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE "%version%"; 
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.39                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2   |
| version                 | 5.7.39-0ubuntu0.18.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

2. Follow the User Guide: Unmanaged Tablet to connect vttablet to the MySQL server.
3. See the tablet not coming online with ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' errors on screen.

Binary Version

vitess@63031fbd2596 /v/local> vttablet --version
ERROR: logging before flag.Parse: E0812 11:14:19.496541    7053 syslogger.go:149] can't connect to syslog
Version: 15.0.0-SNAPSHOT (Git revision ab0ddd072599f6517406f16dff97e98e72f50cb5 branch 'main') built on Wed Aug  3 09:14:18 UTC 2022 by vitess@ac495f2676f5 using go1.18.4 linux/amd64

Operating System and Environment details

* Your Docker environment
* My own external DB is MySQL 5.7

Log Fragments

No response

[arthurschreiber]

This is the same issue described in #15858 (which was already fixed).