Comments (9)
arthurschreiber
commented on June 10, 2024
2
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.
from vitess.
arthurschreiber
commented on June 10, 2024
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
from vitess.
arthurschreiber
commented on June 10, 2024
MySQL is configured with character_set_server and character_set_system set to utf8mb3. collation_server is set to utf8mb3_unicode_ci.
from vitess.
arthurschreiber
commented on June 10, 2024
_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 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
from vitess.
arthurschreiber
commented on June 10, 2024
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 | |
+--------------------------+----------------------------+--------------------+------+-----+---------+-------+------------+---------+
from vitess.
arthurschreiber
commented on June 10, 2024
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. 🙈
from vitess.
arthurschreiber
commented on June 10, 2024
On MySQL 5.7, information_schema.columns is utf8_general_ci. 🙈
from vitess.
arthurschreiber
commented on June 10, 2024
To fix this and make it work correctly on MYSQL 5.7 and 8.0, I propose the following two changes:
- Update
_vt.schemacopyto match the exact collations used by MySQL 8.0. - Change the
UNIONquery that fetches the data to explicitly change the collation to match MySQL 8.0.
from vitess.
dbussink
commented on June 10, 2024
Closing as the linked PR is merged.
from vitess.
Related Issues (20)
- Bug Report: etcd, vtcltd "already running" message on startup HOT 1
- Feature Request: `schemadiff` to calculate "Row size too large" and index size HOT 2
- Joins with derived table and aliases produces bad plan
- Bug Report: Dual left join query returns more results than it should
- Bug Report: cancelling MoveTables errors with 'cannot remove tables since one or more do not exist in the denylist' HOT 11
- Bug Report: Insert on duplicate key update failing with missing bind var
- Feature Request: Add support for getting older key values from the topo server
- RFC: Drain MySQL connection in VTGate upon termination of VTGate
- Bug Report: VPlayer does not detect stalls HOT 3
- Feature Request: Add support for multi table update for non literal column update using other dependent table
- FeatureRequest: `LookupVindex create` should use an existing VIndex and Table when possible
- `LookupVindex create` does not properly cleanup / undo state changes made when it fails to create the workflow
- Feature Request: `LookupVindex` has no `internalize` command (equivalent to `ReverseTraffic`)
- Bug Report: VTOrc is not setting the correct timeout for RPC calls which can get stuck
- Feature Request: Move most VARBINARY columns to VARCHAR in examples
- Bug Report: `schemadiff` shows bogus diff on textual column where collation is undefined
- Release of `v20.0.0`
- Release of `v20.0.0-RC1` HOT 2
- `go:linkname` (de)stabilization HOT 1
- Feature Request: support private go packages
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from vitess.