PolarDBMySQL版的5.6、5.7版本和MySQL5.6、5.7版本升级至PolarDBMySQL版的8.0版本的优势、预检查以及兼容性说明等内容-云原生数据库 PolarDB(PolarDB

PolarDB MySQL 版 8.0 版本概述

版本发布日期
- 8.0.1 版本发布于 2019 年 12 月 3 日。
- 8.0.2 版本发布于 2020 年 7 月 22 日。
版本优势

8.0 版本增强了架构和内核能力，为用户处理业务提供了更灵活的技术解决方案，性能提升明显。详情请参见功能特性。

兼容性预检查

PolarDB MySQL 版的 5.6、5.7 版本和 MySQL 5.6、5.7 版本向的 8.0 版本升级过程中，经常会遇到性能、语法兼容以及周边组件是否支持的问题。查询的性能问题一般是由于优化器升级导致执行计划有变化，此类问题需要对性能低下的语句进行针对性的性能优化，单独的性能问题基本不会引发业务报错以及代码改写的问题，此类问题不在本文讨论范围之内。

本文主要讨论兼容性问题，此类问题需要在数据库升级过程中，更新相应的代码或更改环境配置，引发此类问题的主要原因为版本升级后部分语法的变化以及特性的更新和移除。如果确保上述内容不存在问题，可以跳过下面章节进行升级，详情请参见大版本一键升级。

预检查功能会提供一个简要清单，帮助您在升级前更好地了解升级过程中可能需要注意的问题。如果下述内容存在对应的问题，请参考后续内容进行操作和检查。

确保没有使用废弃的数据类型、函数和功能，废弃清单信息请参见 Features Removed in MySQL 8.0 。
确保触发器（Triggers）没有丢失、空的 definer 或无效的内容。
确保只有 InnoDB 引擎的分区表。
确保关键字和保留关键字没有冲突，详情请参见 Keywords and Reserved Words 。
确保没有和 MySQL 5.6、5.7 版本的系统数据库、MySQL 8.0 版本新增的 INNODB_ 开头的词典表名冲突。
确保不依赖于 INFORMATION_SCHEMA 下的 GLOBAL 、 LOCAL 、 VARIABLES 、 STATUS 表。
确保 sql_mode 中没有使用废弃的变量设置，详情请参见参数兼容性。
确保表和存储过程单个 ENUM 或 SET 列元素的长度不超过 255 个字符或 1020 个字节。
确保表的分区不在共享 InnoDB tablespaces 表空间中。
确保查询 SQL 语句中的 GROUP BY 子句不带有 ASC 或 DESC。

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb','ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned';

SELECT DISTINCT NAME,SPACE,SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';

ALTER TABLE part ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

ALTER TABLE part REMOVE PARTITIONING;
Query OK, 0 raws affected (0.06 sec)

DB2, MAXDB, MSSDL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTI

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;
+----+------------+-------------+
| id | invoice_id | description |
+----+------------+-------------+
| 1 | 1 | New socks |
| 3 | 2 | Shoes |
| 5 | 3 | Tie |
+----+------------+-------------+
3 rows in set (0.00 sec)

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains

mysql> set explicit_defaults_for_timestamp = OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t1(c1 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
 `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t1(c1 int, c2 timestamp null);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
 `c1` int(11) DEFAULT NULL,
 `c2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1(c1 timestamp, c2 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
 `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t1(c1 timestamp, c2 timestamp);
ERROR 1067 (42000): Invalid default value for 'c2'
mysql> create table t1(c1 timestamp, c2 timestamp);

旧名称	新名称
INNODB_SYS_COLUMNS	INNODB_COLUMNS
INNODB_SYS_DATAFILES	INNODB_DATAFILES
INNODB_SYS_FIELDS	INNODB_FIELDS
INNODB_SYS_FOREIGN	INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS	INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES	INNODB_INDEXES
INNODB_SYS_TABLES	INNODB_TABLES
INNODB_SYS_TABLESPACES	INNODB_TABLESPACES
INNODB_SYS_TABLESTATS	INNODB_TABLESTATS
INNODB_SYS_VIRTUAL	INNODB_VIRTUAL

'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemate',
'st_spatial_reference_systems',
'table_partition_systems',
'table_partition_values',
'table_partitions',
'table_states',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage'
suppose
+--------------+--------------+
| TABLE_SCHEMA |  TABLE_NAME  |
+--------------+--------------+
|     mysql    |   catalogs   |
+--------------+--------------+
1 rows in set (0.00 sec)

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',

ALTER TABLE catalogs RENAME user_catalogs;
Query OK, 0 rows affected (0.05 sec)

DROP TABLE catalogs;
Query OK, 0 rows affected (0.06 sec)

SHOW CREATE VIEW v1；
+------+------------------------------------------------------------------------------------------------
| View | Create View
+------+------------------------------------------------------------------------------------------------
|  v1  | CREATE ALGORITHM=UNDEFINED DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW
+------+------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

ALTER VIEW v1(a12345678901234567890) AS SECLECT 1;

INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES
INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS

performance_schema.global_variables
performance_schema.session_variables
performance_schema.variables_by_thread
performance_schema.global_status
performance_schema.session_status
performance_schema.status_by_thread
performance_schema.status_by_account
performance_schema.status_by_host
performance_schema.status_by_user

SHOW VARIABLES
SHOW STATUS

1. ```
 CREATE TABLE test (id int unsigned);
```
2. ```
INSERT INTO test VALUES (-1);
```
  - ```
  Query OK, 1 row affected, 1 warning (0.01 sec)
```
- ```
ERROR 1264 (22003): Out of range value for column 'a' at row 1
```
1. ```
CREATE TABLE test2 (id int unsigned);
```
2. ```
INSERT INTO test2 VALUES (0/0);
```
  - ```
  Query OK, 1 row affected (0.01 sec)
```
- ```
ERROR 1365 (22012): Division by 0
```
1. ```
CREATE TABLE test3 (a varchar(10));
```
2. ```
INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');
```
  - ```
  Query OK, 1 row affected, 1 warning (0.00 sec)
```
- ```
ERROR 1406 (22001): Data too long for column 'a' at row 1
```

```
CREATE TABLE test3 (a datetime);
```

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at ro

check table 41_decimal for upgrade;
+-----------------+-------+----------+------------------------------------------------
| Table           | Op    | Msg_type | Msg_text                                         
+-----------------+-------+----------+------------------------------------------------
| test.41_decimal | check | error    | Table upgrade required for `test`.`41_decimal`. 
+-----------------+-------+----------+-------------------------------------------------
1 row in set (0.00 sec) 
check table 55_temporal for upgrade;
+------------------+-------+----------+------------------------------------------------
| Table            | Op    | Msg_type | Msg_text                                        
+------------------+-------+----------+------------------------------------------------
| test.55_temporal | check | error    | Table upgrade required. Please do "REPAIR TABLE 
1 row in set (0.00 sec) 
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7/client/mysqlcheck --user=root 
error    : Table upgrade required for `test`.`41_decimal`. Please dump/reload table to 
test.55_temporal
error    : Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reloa
test.child                                         OK
test.geom                                          OK
test.jemp                                          OK
test.jemp_myisam                                   OK
test.opening_lines                                 OK

REPAIR TABLE 55_temporal; 
+------------------+--------+----------+---------------------------------------------------
| Table            | Op     | Msg_type | Msg_text                                          
+------------------+--------+----------+---------------------------------------------------
| test.55_temporal | repair | Note     | TIME/TIMESTAMP/DATETIME columns of old format have
| test.55_temporal | repair | status   | OK                                                
+------------------+--------+----------+---------------------------------------------------
2 rows in set (0.01)
Dump:
$./client/mysqldump --databases test --socket=5.6/data/mysql.sock --user=root>test.sql
Restore:
.\ test.sql

GRANT REPLICATION CLIENT ON *.* TO 'odps'@'%'; You are not allowed to create a user with
create user;
grant privielges;

select id,count(*) from sbtest.sbtest1 where id < 10 group by id desc

select id,count(*) from sbtest.sbtest1 where id < 10 group by id order by id

ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` DR
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` AD
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

CREATE TABLE t_gcol_dep (fid INTEGER NOT NULL PRIMARY KEY, g POINT GENERATED ALWA
Query OK, 0 rows affected, 1 warning (0.07 sec)
show warnings;
+---------+------+---------------------------------------------------------------------
| Level | Code | Message
+---------+------+---------------------------------------------------------------------
| Warning | 1287 | 'POINTFROMTEXT' is deprecated and will be removed in a future releas
+---------+------+---------------------------------------------------------------------
1 row in set (0.00 sec)

ALTER TABLE t_gcol_dep MODIFY g POINT GENERATED ALWAYS AS (ST_POINTFROMTEXT(POINT
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

$./client/mysqlcheck --user=root --socket=5.7/data/mysql.sock --databases triggers --ch
triggers.t1
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trig
Warning : No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be act
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The tri
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigg
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The tri
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The tr
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trig
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigg
Warning : No definer attribute for trigger 'triggers'.'trg1'. The trigger will be acti
status : OK
triggers.t2 OK
check table t1;
+-------------+-------+----------+-----------------------------------------------------
| Table | Op | Msg_type | Msg_text
+-------------+-------+----------+-----------------------------------------------------
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'t1_bi'.
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_

Dump:
$./client/mysqldump --databases triggers --socket=5.6/data/mysql.sock --user=root>trigge
Restore:
.\ triggers.sql

 select @@tx_read_only

select @@transaction_read_only

SELECT *
SELECT * FROM `information_schema`. TABLES
ORDER BY table_name DESC
) AS sg
GROUP BY table_name

SELECT *
SELECT * FROM `information_schema`. TABLES
ORDER BY table_name DESC limit 10000 # 需要⾜够⼤的⾏数
) AS sg
GROUP BY table_name

DELETE FROM t1
-> WHERE id IN (SELECT id
-> FROM (SELECT t1.id
-> FROM t1 INNER JOIN t2 USING (id)
-> WHERE t2.status = 0) AS t);
ERROR 1093 (HY000): You can't specify target table 't1'
for update in FROM clause

SET optimizer_switch = 'derived_merge=off';

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

大版本升级最佳实践

PolarDB MySQL 版 8.0 版本概述

兼容性预检查

引擎和分区表兼容性

字符集和排序规则兼容性

参数兼容性

lower_case_table_names

sql_mode

explicit_defaults_for_timestamp

视图/表/关键字兼容性

InnoDB 相关视图兼容

重命名的 InnoDB 信息模式视图

视图兼容

5.6 版本系统表兼容

5.6 版本 INFORMATION_SCHEMA 系统和状态变量信息的表兼容

5.6 版本 INNODB 表兼容

5.6 版本 GET_LOCK 函数兼容

类型兼容

枚举和集合类型兼容

5.6 版本 YEAR 类型 (adsbygoogle = window.adsbygoogle || []).push({});

5.6 版本类型数据插入兼容

5.7 版本 JSON 类型中 INT 类型数据变化

5.x 旧式类型兼容

关键词与保留字

SQL 兼容性

GRANT 授权

不支持 GROUP BY ASC/DESC

外键约束定义

5.7 版本空间函数

触发器兼容

客户端兼容性

Unknown system variable 'tx_read_only'兼容性

常见问题

并行查询导致的排序问题

5.6 版本子查询问题

5.6 版本派⽣表问题

相关文档