| Bug #90685 | CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH equal for multi-byte TEXT cols | ||
|---|---|---|---|
| Submitted: | 30 Apr 2018 7:41 | Modified: | 30 Apr 2018 9:56 |
| Reporter: | Milen Kirilov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | tested 5.6, 5.7 and 8 | OS: | Windows |
| Assigned to: | CPU Architecture: | x86 (x64) | |
| Tags: | character_maximum_length, character_octet_length, information_schema, longtext, Mediumtext, text | ||
[30 Apr 2018 7:41]
Milen Kirilov
[30 Apr 2018 8:12]
MySQL Verification Team
Thank you for the bug report. Please provide correct test case:
Your MySQL connection id is 10
Server version: 8.0.12 Source distribution 2018-APR-29
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 8.0 > CREATE DATABASE `db1`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0,11 sec)
mysql 8.0 > USE `db1`;
Database changed
mysql 8.0 > CREATE TABLE `a` (
-> `a0` varchar(56),
-> `a1` text,
-> `a2` longtext,
PRIMARY KEY (`id`)
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4;
ERROR 1072 (42000): Key column 'id' doesn't exist in table
mysql 8.0 >
Thanks.
[30 Apr 2018 8:53]
Milen Kirilov
Appologies. How do I edit the test case field? Thanks.
[30 Apr 2018 8:57]
Milen Kirilov
Edit (I'm posting the test case anew): CREATE DATABASE `db1`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `db1`; CREATE TABLE `a` ( `a0` varchar(56), `a1` text, `a2` longtext ) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4; SELECT `CS`.`MAXLEN`, `CCSA`.`CHARACTER_SET_NAME`, `C`.`COLLATION_NAME`, `C`.`CHARACTER_MAXIMUM_LENGTH`, `C`.`CHARACTER_OCTET_LENGTH`, `C`.`COLUMN_NAME`, `C`.`COLUMN_TYPE` FROM `INFORMATION_SCHEMA`.`TABLES` T INNER JOIN `INFORMATION_SCHEMA`.`COLUMNS` C ON `T`.`TABLE_SCHEMA` = `C`.`TABLE_SCHEMA` AND `T`.`TABLE_NAME` = `C`.`TABLE_NAME` LEFT JOIN `INFORMATION_SCHEMA`.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA ON `C`.`COLLATION_NAME` = `CCSA`.`COLLATION_NAME` LEFT JOIN `INFORMATION_SCHEMA`.`CHARACTER_SETS` CS ON `CCSA`.`CHARACTER_SET_NAME` = `CS`.`CHARACTER_SET_NAME` WHERE `T`.`TABLE_TYPE` IN( 'BASE TABLE', 'VIEW' ) and `T`.`TABLE_NAME` = 'a' and `T`.`TABLE_SCHEMA` = 'db1' order by `C`.`ORDINAL_POSITION`
[30 Apr 2018 9:46]
MySQL Verification Team
https://dev.mysql.com/doc/refman/8.0/en/columns-table.html Notes: ´´´´´´´´ CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multibyte character sets. mysql 8.0 > SELECT `CS`.`MAXLEN`, `CCSA`.`CHARACTER_SET_NAME`, `C`.`COLLATION_NAME`, `C`.`CHARACTER_MAXIMUM_LENGTH`, `C`.`CHARACTER_OCTET_LENGTH`, `C`.`COLUMN_NAME`, `C`.`COLUMN_TYPE` FROM `INFORMATION_SCHEMA`.`TABLES` T INNER JOIN `INFORMATION_SCHEMA`.`COLUMNS` C ON `T`.`TABLE_SCHEMA` = `C`.`TABLE_SCHEMA` AND `T`.`TABLE_NAME` = `C`.`TABLE_NAME` LEFT JOIN `INFORMATION_SCHEMA`.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA ON `C`.`COLLATION_NAME` = `CCSA`.`COLLATION_NAME` LEFT JOIN `INFORMATION_SCHEMA`.`CHARACTER_SETS` CS ON `CCSA`.`CHARACTER_SET_NAME` = `CS`.`CHARACTER_SET_NAME` WHERE `T`.`TABLE_TYPE` IN( 'BASE TABLE', 'VIEW' ) and `T`.`TABLE_NAME` = 'a' and `T`.`TABLE_SCHEMA` = 'db1' order by `C`.`ORDINAL_POSITION`\G *************************** 1. row *************************** MAXLEN: 4 CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci CHARACTER_MAXIMUM_LENGTH: 56 CHARACTER_OCTET_LENGTH: 224 COLUMN_NAME: a0 COLUMN_TYPE: varchar(56) *************************** 2. row *************************** MAXLEN: 4 CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci CHARACTER_MAXIMUM_LENGTH: 65535 CHARACTER_OCTET_LENGTH: 65535 COLUMN_NAME: a1 COLUMN_TYPE: text *************************** 3. row *************************** MAXLEN: 4 CHARACTER_SET_NAME: utf8mb4 COLLATION_NAME: utf8mb4_0900_ai_ci CHARACTER_MAXIMUM_LENGTH: 4294967295 CHARACTER_OCTET_LENGTH: 4294967295 COLUMN_NAME: a2 COLUMN_TYPE: longtext 3 rows in set (0,00 sec) mysql 8.0 > SHOW VARIABLES LIKE "%VERSION%"; +-------------------------+---------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------+ | innodb_version | 8.0.12 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 8.0.12 | | version_comment | Source distribution 2018-APR-29 | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | +-------------------------+---------------------------------+ 9 rows in set (0,00 sec)
[30 Apr 2018 9:51]
MySQL Verification Team
-> order by
-> `C`.`ORDINAL_POSITION`\G
*************************** 1. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 56
CHARACTER_OCTET_LENGTH: 224
COLUMN_NAME: a0
COLUMN_TYPE: varchar(56)
*************************** 2. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 65535
CHARACTER_OCTET_LENGTH: 65535
COLUMN_NAME: a1
COLUMN_TYPE: text
*************************** 3. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 4294967295
CHARACTER_OCTET_LENGTH: 4294967295
COLUMN_NAME: a2
COLUMN_TYPE: longtext
3 rows in set (0,03 sec)
mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+---------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------+
| innodb_version | 5.6.41 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.41 |
| version_comment | Source distribution 2018-APR-29 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------+
7 rows in set (0,00 sec)
[30 Apr 2018 9:56]
MySQL Verification Team
Thank you for the feedback.
-> order by
-> `C`.`ORDINAL_POSITION`\G
*************************** 1. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 56
CHARACTER_OCTET_LENGTH: 224
COLUMN_NAME: a0
COLUMN_TYPE: varchar(56)
*************************** 2. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 65535
CHARACTER_OCTET_LENGTH: 65535
COLUMN_NAME: a1
COLUMN_TYPE: text
*************************** 3. row ***************************
MAXLEN: 4
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
CHARACTER_MAXIMUM_LENGTH: 4294967295
CHARACTER_OCTET_LENGTH: 4294967295
COLUMN_NAME: a2
COLUMN_TYPE: longtext
3 rows in set (0,30 sec)
mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
