search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.99k stars 5.82k forks source link

MySQL 8.0 Compatibility #7968

Open morgo opened 5 years ago

morgo commented 5 years ago

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

See the complete list.

Essential

Nice to Have

8.0.11 and before

8.0.13

8.0.14

8.0.15

8.0.17

8.0.18

8.0.19

8.0.20

8.0.21

8.0.22

8.0.23

8.0.28

8.0.30

8.0.31

8.0.34

8.0.35

8.0.36

8.0.37

Cleanup

Not Applicable

shenli commented 5 years ago

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

elvizlai commented 5 years ago

is there any progress or plan about JSON_TABLE ?

morgo commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

Should we add utf8mb4_0900_ai_ci to the list as well?

yahonda commented 2 years ago

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.

dveeden commented 2 years ago

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 commented 2 years ago

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

bb7133 commented 2 years ago

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!

SunRunAway commented 2 years ago

Should the full implementation of Dynamic Privileges be essential?

dveeden commented 2 years ago

Should the full implementation of Dynamic Privileges be essential?

I don't think that is essential.

jaggerwang commented 1 year ago

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

SunRunAway commented 1 year ago

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

jaggerwang commented 1 year ago

@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 commented 1 year ago

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

yahonda commented 1 year ago

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 commented 9 months ago

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

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> 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 commented 9 months ago

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

dveeden commented 9 months ago

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 commented 9 months ago

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.