tidb icon indicating copy to clipboard operation
tidb copied to clipboard
verified publisher icon pingcap

MySQL 8.0 Compatibility

Open morgo opened this issue 7 years ago • 9 comments

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

See the complete list.

Essential

  • [x] Common Table Expressions (regular and recursive) https://github.com/pingcap/tidb/issues/17472
  • [x] Window Functions https://github.com/pingcap/tidb/issues/4807 https://github.com/pingcap/tidb/pull/8117
  • [x] utf8mb4 as default (rename utf8 as in TiDB)
  • [x] Role Based Authentication
  • [ ] SKIP LOCKED https://github.com/pingcap/tidb/issues/18207

Nice to Have

  • [ ] Lateral derived tables (8.0.14) Details
  • [x] Invisible Indexes https://github.com/pingcap/tidb/issues/9246
  • [ ] JSON_TABLE function + add remaining JSON functions that were backported to 5.7
  • [ ] multi-valued indexes on json arrays / json_overlaps() and member of()
  • [ ] JSON schema validation
  • [ ] JSON_VALUE function (8.0.21)
  • [ ] Improved Regular expression support https://github.com/pingcap/tidb/issues/23881
  • [x] SHUTDOWN command https://github.com/pingcap/tidb/issues/5046
  • [ ] RESTART command https://github.com/pingcap/tidb/issues/26016
  • [ ] Cleanup SHOW VARIABLES output (remove query cache etc.)
  • [ ] Remove removed functions (insecure crypto etc)
  • [ ] Maintain information about password history
  • [ ] Resource Management
  • [ ] Default values for BLOB/TEXT + default value as function (8.0.13) https://github.com/pingcap/tidb/issues/10377
  • [ ] Require current password to be specified to change password (8.0.13)
  • [x] Option to require tables to have a primary key (8.0.13) https://github.com/pingcap/tidb/issues/28544
  • [ ] GIS Support https://github.com/pingcap/tidb/issues/6347
  • [x] NOWAIT
  • [x] SET_VAR optimizer hint https://github.com/pingcap/tidb/issues/18748
  • [x] MAX_EXECUTION_TIME hint https://github.com/pingcap/tidb/issues/7008
  • [ ] Descending Indexes + GROUP BY no longer implying ORDER BY https://github.com/pingcap/tidb/issues/2519
  • [ ] Functional Indexes, aka expression indexes in TiDB https://github.com/pingcap/tidb/issues/18008
  • [ ] Index Skip Scan
  • [ ] information_schema.keywords table. Example here.
  • [ ] Admin-only TCP/IP port (8.0.14) Details
  • [ ] Two passwords per account (8.0.14) Details
  • [ ] Check Constraints (from 8.0.15) https://github.com/pingcap/tidb/projects/46
  • [x] Make integer display with optional https://github.com/pingcap/tidb/pull/18775
  • [ ] New syntax for INSERT ON DUPLICATE KEY UPDATE https://dev.mysql.com/worklog/task/?id=6312
  • [ ] New Optimizer hints: join_index, no_join_index, group_index, no_group_index, order_index, no_order_index, index, no_index.
  • [ ] Lock accounts after too many failed logins
  • [ ] Table and row-value constructors. i.e. TABLE t ORDER BY c LIMIT 10 OFFSET 3; ROW(1,2,3) - 8.0.19 #19934
  • [ ] Create user/alter user/set password can generate random passwords for you.
  • [ ] utf8mb4_0900_bin
  • [ ] Non-binary utf8mb4 collations, such as utf8mb4_0900_ai_ci https://github.com/pingcap/tidb/pull/33212/files
  • [ ] support more clear combinations of UNION and INTO
  • [x] helper functions uuid_to_bin and bin_to_uuid https://github.com/pingcap/tidb/issues/20119
  • [x] #30295
  • [x] caching_sha256_password Authentication Plugin https://github.com/pingcap/tidb/issues/9411
  • [ ] User attributes
  • [ ] 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 https://github.com/pingcap/tidb/issues/30637

Cleanup

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)

morgo avatar Oct 19 '18 16:10 morgo

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

shenli avatar Oct 20 '18 07:10 shenli

is there any progress or plan about JSON_TABLE ?

elvizlai avatar Aug 05 '19 04:08 elvizlai

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.

morgo avatar Mar 30 '21 17:03 morgo

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 avatar Jul 09 '21 08:07 dveeden

Should we add utf8mb4_0900_ai_ci to the list as well?

dveeden avatar Sep 09 '21 09:09 dveeden

Would you consider to add https://github.com/pingcap/tidb/issues/32118 to this list? I think it needs supported because it blocks using MySQL Connector/J 8.

yahonda avatar May 16 '22 06:05 yahonda

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 avatar May 16 '22 09:05 dveeden

Maybe we should add https://github.com/pingcap/dm/issues/1950 to this list? or is this strictly for tidb-server?

dveeden avatar Jul 12 '22 05:07 dveeden

Would you consider to add https://github.com/pingcap/tidb/issues/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!

bb7133 avatar Aug 03 '22 02:08 bb7133

Should the full implementation of Dynamic Privileges be essential?

SunRunAway avatar Aug 22 '22 03:08 SunRunAway

Should the full implementation of Dynamic Privileges be essential?

I don't think that is essential.

dveeden avatar Aug 30 '22 14:08 dveeden

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

jaggerwang avatar Nov 07 '22 09:11 jaggerwang

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

SunRunAway avatar Nov 07 '22 09:11 SunRunAway

@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.

jaggerwang avatar Nov 07 '22 10:11 jaggerwang

Improvements in password management are tracked in https://github.com/pingcap/tidb/issues/38923

dveeden avatar Nov 29 '22 07:11 dveeden

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 avatar Mar 10 '23 05:03 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) https://github.com/pingcap/tidb/issues/10377

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

  • TiDB 7.5.0
my:[email protected]:4000=> use test;
USE
my:[email protected]: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:[email protected]: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:[email protected]: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 avatar Dec 14 '23 05:12 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 https://github.com/mysql/mysql-server/commit/c7934d119aeb

yahonda avatar Dec 14 '23 06:12 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

Yes indeed. I've fixed that.

dveeden avatar Dec 15 '23 07:12 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 https://github.com/pingcap/tidb/issues/45506 covers the second part. I've update the description to reflect this.

dveeden avatar Dec 15 '23 08:12 dveeden