MySQL 8.0 Compatibility

[morgo]

This issue is to track what is required to move from MySQL 5.7 to MySQL 8.0 compatibility.

See the complete list.

Essential

• Common Table Expressions (regular and recursive) support common table expression (CTE) #17472
• Window Functions Support window functions for TiDB #4807 docs/design: add proposal for window functions #8117
• utf8mb4 as default (*: change default charset and collation from 'utf8 utf8_bin' to 'utf8mb4 utf8mb4_bin' #7965)
• Role Based Authentication
• Support utf8mb4_0900_ai_ci when new collation is enabled Support utf8mb4_0900_ai_ci when new collation is enabled #37566

Nice to Have

8.0.11 and before

• NOWAIT
• Invisible Indexes UCP: Support Invisible Indexes #9246
• SET_VAR optimizer hint Support Variable-Setting Hint SET_VAR #18748
• information_schema.keywords table. Example here. Add information_schema.keywords #48801
• SHUTDOWN command Support SHUTDOWN SQL command (and protocol cmd) #5046
• Improved Regular expression support support regular expression related functions #23881
• Maintain information about password history *: support password reuse policy #39162
• Resource Management Global Resource Quota Control  #38825
• MAX_EXECUTION_TIME hint Feature Request: Support MAX_EXECUTION_TIME #7008
• Non-binary utf8mb4 collations, such as utf8mb4_0900_ai_ci https://github.com/pingcap/tidb/pull/33212/files
• helper functions uuid_to_bin and bin_to_uuid Support bin-to-uuid and uuid-to-bin #20119
• support is_uuid function #30295
• caching_sha2_password Authentication Plugin Support for caching_sha2_password #9411
• JSON_TABLE function + add remaining JSON functions that were backported to 5.7
• SKIP LOCKED Support SKIP LOCKED Syntax #18207
• RESTART command Support for 'RESTART' statement #26016
• Cleanup SHOW VARIABLES output (remove query cache etc.)
• Remove removed functions (insecure crypto etc)
• GIS Support Support for SPATIAL functions, data types and indexes #6347
• Descending Indexes + GROUP BY no longer implying ORDER BY Support ascending or descending index  #2519
• Functional Indexes, aka expression indexes in TiDB Make Expression Index generally available #18008
• Index Skip Scan
• support more clear combinations of UNION and INTO
• SQL Mode TIME_TRUNCATE_FRACTIONAL https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractional
• Support MySQL 8.0 bitwise behavior bitwise operations with binary args behavior changes by mysql 5.7 and 8.0 both #30637
• Support Partitioning management DDL ALTER TABLE t REMOVE PARTITIONING Support ALTER TABLE t REMOVE PARTITIONING #42616
• Support Partitioning management DDL ALTER TABLE t PARTITION BY ... Support ALTER TABLE t REMOVE PARTITIONING #42616

8.0.13

• Option to require tables to have a primary key (8.0.13) Add support for sql_require_primary_key #28544
• Default values for BLOB/TEXT + default value as function (part 1: RAND()) Incompatible with MySQL 8.0 about create table with default expression #10377
• Default values for BLOB/TEXT + default value as function (part 2: UUID()) support specifying expr uuid() as column default value #33870
• Default values for BLOB/TEXT + default value as function (part 3) Default value for TEXT, BLOB or JSON columns #45506
• Require current password to be specified to change password (8.0.13)

8.0.14

• Lateral derived tables (8.0.14) Details Support Lateral derived tables #40328
• Admin-only TCP/IP port (8.0.14) Details
• Two passwords per account (8.0.14) Details

8.0.15

• Check Constraints (from 8.0.15) https://github.com/pingcap/tidb/projects/46 Support CHECK constraint #41711

8.0.17

• utf8mb4_0900_bin TiDB should support collation utf8mb4_0900_bin #46268
• multi-valued indexes on json arrays / json_overlaps() and member of() support multi-valued index  #39592
• JSON schema validation with JSON_SCHEMA_VALID() #52779

8.0.18

• Create user/alter user/set password can generate random passwords for you.

8.0.19

• Table constructors. i.e. TABLE t ORDER BY c LIMIT 10 OFFSET 3 - 8.0.19 Support VALUES and TABLE statements syntax #19934
• Make integer display with optional util: rewrite parser warings for integer display width #18775
• Lock accounts after too many failed logins Support Failed-Login Tracking and Temporary Account Locking #38938
• New syntax for INSERT ON DUPLICATE KEY UPDATE https://dev.mysql.com/worklog/task/?id=6312 Support MySQL 8.0.19 feature - aliases in the values and the set clause of insert into ... on duplicate key update #51650
• Row-value constructors. i.e. VALUES ROW(1, "foo", 3.2) -8.0.19 VALUES statement is not supported #21486
• Row-alias support with the AS keyword for INSERT...ON DUPLICATE KEY UPDATE...
• Support TIMESTAMP with explicit time zone Support TIMESTAMP with explicit time zone #51742

8.0.20

• New Optimizer hints: join_index, no_join_index, group_index, no_group_index, order_index, no_order_index, index, no_index.

8.0.21

• User attributes Support commenting in CREATE USER and ALTER USER statements #38172
• JSON_VALUE function (8.0.21)
• CREATE TABLE ... START TRANSACTION support mysql8.0 create table with start transaction feature #52477

8.0.22

• Parenthesized Query Expressions

8.0.23

• Invisible Columns - 8.0.23

8.0.28

• Deprecated TLSv1.0 and TLSv1.1 The minimum TLS version should comply with IETF RFC 8996 #36036
• Support wider range of UNIX_TIMESTAMP Support wider range of UNIX_TIMESTAMP, as MySQL 8.0.28 #30133

8.0.30

• Generated Invisible Primary Key - 8.0.30
• KDF, salt and iterations support for AES_ENCRYPT() and AES_DECRYPT().

8.0.31

• INTERSECT Support EXCEPT/INTERSECT Operators #18031
• EXCEPT Support EXCEPT/INTERSECT Operators #18031

8.0.34

• CURRENT_USER() as default value for VARCHAR and TEXT columns in CREATE TABLE statements Default value for TEXT, BLOB or JSON columns #45506

8.0.35

8.0.36

8.0.37

Cleanup

• Remove variables that have been removed.
• Remove features that have been removed (this will require several sub-tasks)
• Add deprecation warnings for deprecated variables
• Add deprecation warnings for deprecated features

Not Applicable

• New Data Dictionary
• Performance Schema enhancements
• Replication enhancements
• InnoDB enhancements
• SET PERSIST (settings automatically save cluster-wide)
• Log services (log_error_services etc)

[shenli]

@morgo Nice work! We will add it to our roadmap.
/cc @winkyao @zz-jason @jackysp

[elvizlai]

is there any progress or plan about JSON_TABLE ?

[morgo]

I moved skip-locked to the essential list. At the time this list was created, the default was optimistic locking. But since it is now pessmistic, it is highly desirable.

[dveeden]

User attributes might also be something to add

mysql 8.0.22 > CREATE USER 'foo'@'%' IDENTIFIED BY 'affalfajljfjlk' COMMENT 'Support ticket #123';
Query OK, 0 rows affected (0.01 sec)

mysql 8.0.22 > CREATE USER 'bar'@'%' IDENTIFIED BY 'affalfajljfjlk' ATTRIBUTE '{"ticket": 123, "full_name": "John Doe"}';
Query OK, 0 rows affected (0.03 sec)

mysql 8.0.22 > select User_attributes from mysql.user where user in ('foo','bar');
+--------------------------------------------------------+
| User_attributes                                        |
+--------------------------------------------------------+
| {"metadata": {"ticket": 123, "full_name": "John Doe"}} |
| {"metadata": {"comment": "Support ticket #123"}}       |
+--------------------------------------------------------+
2 rows in set (0.01 sec)

[dveeden]

Should we add utf8mb4_0900_ai_ci to the list as well?

[yahonda]

Would you consider to add #32118 to this list?
I think it needs supported because it blocks using MySQL Connector/J 8.

[dveeden]

Would you consider to add #32118 to this list? I think it needs supported because it blocks using MySQL Connector/J 8.

I don't think that really should block Connector/J 8.0. However I think fixing that soon-ish would be good anyway.

[dveeden]

Maybe we should add pingcap/dm#1950 to this list? or is this strictly for tidb-server?

[bb7133]

Would you consider to add #32118 to this list?

@yahonda @dveeden , I think this is a 'nice to have' compatibility improvement. Personally I'm really looking forward to close it, thanks, Daniel!

[SunRunAway]

Should the full implementation of Dynamic Privileges be essential?

[dveeden]

Should the full implementation of Dynamic Privileges be essential?

I don't think that is essential.

[jaggerwang]

Multi-valued indexes on json arrays and descending indexes are exactly what we need, is there any plan for these two features?

[SunRunAway]

@jaggerwang
AFAIK, Multi-valued indexes are planned in early next year /cc @bb7133
For descending indexes, could you describe more about your scenarios?

[jaggerwang]

@SunRunAway For descending indexes, suppose we have a table named file to store a filesystem's meta info.

Name	Type	Desc
id	int	ID
type	int	0 for directory, 1 for file
filename	string	File or directory name
created_at	datetime	Create time

We have the following sql to query file list order by filename ASC or DESC, directories should before files.

# Order by filename ASC
select * from file order by type asc, filename asc limit 100;

# Order by filename DESC
select * from file order by type asc, filename desc limit 100;

To accelerate query, we created an union index (type, filename), but the index is not working well when order by filename DESC, about 2 to 3 times slower.

[dveeden]

Improvements in password management are tracked in #38923

[yahonda]

I've found last_insert_id() behavior differences between MySQL 5.7 and 8.0. TiDB 6.6.0 behaves same as MySQL 5.7. #42084

[yahonda]

It looks like TiDB does not support this feature available since MySQL 8.0.13 yet. This checkbox is checked somehow.

Default values for BLOB/TEXT + default value as function (8.0.13) #10377

This example comes from the https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

• TiDB 7.5.0

my:root@127.0.0.1:4000=> use test;
USE
my:root@127.0.0.1:4000=> CREATE TABLE t2 (b BLOB DEFAULT ('abc'));
error: mysql: 1064: You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 38 near "'abc'));"

my:root@127.0.0.1:4000=> select tidb_version();
                      tidb_version()
-----------------------------------------------------------
 Release Version: v7.5.0                                  +
 Edition: Community                                       +
 Git Commit Hash: 069631e2ecfedc000ffb92c67207bea81380f020+
 Git Branch: heads/refs/tags/v7.5.0                       +
 UTC Build Time: 2023-11-24 08:41:52                      +
 GoVersion: go1.21.3                                      +
 Race Enabled: false                                      +
 Check Table Before Drop: false                           +
 Store: tikv
(1 row)

my:root@127.0.0.1:4000=>

• MySQL 8.2.0

mysql> CREATE TABLE t2 (b BLOB DEFAULT ('abc'));
Query OK, 0 rows affected (0.12 sec)

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `b` blob DEFAULT (_utf8mb4'abc')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t2 values;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> insert into t2 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------------+
| b          |
+------------+
| 0x616263   |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.2.0     |
+-----------+
1 row in set (0.01 sec)

mysql>

[yahonda]

It is not a big deal though utf8mb4_0900_bin collation has been introduced since MySQL 8.0.17. Here it is listed as "8.0.11 and before"

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html
mysql/mysql-server@c7934d119aeb

[dveeden]

It is not a big deal though utf8mb4_0900_bin collation has been introduced since MySQL 8.0.17. Here it is listed as "8.0.11 and before"

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-17.html mysql/mysql-server@c7934d119aeb

Yes indeed. I've fixed that.

[dveeden]

It looks like TiDB does not support this feature available since MySQL 8.0.13 yet. This checkbox is checked somehow.

Default values for BLOB/TEXT + default value as function (8.0.13) #10377

Yes it looks like this was marked as completed after only part of this had been implemented. I think #45506 covers the second part. I've update the description to reflect this.