By Daoke
During the upgrade from PolarDB for MySQL 5.6/MySQL 5.6 to PolarDB for MySQL 8.0, common issues arise with performance, syntax compatibility, and peripheral component support. Performance problems in queries are usually caused by changes in the execution plan due to optimizer upgrades. However, such issues can be resolved through targeted performance optimization and do not require rewriting code or cause business errors. Therefore, this article will not discuss such issues.
This article focuses on actual compatibility problems that necessitate code updates or changes in the environment configuration during the database upgrade process. These problems arise due to syntax changes, feature updates, and removals in newer versions. To help users identify potential issues during the upgrade, a pre-inspection checklist is provided. If any of the following problems are encountered, users can refer to the Version Upgrade Details section for operation and inspection.
If the preceding problems do not exist, you can skip the following section and upgrade. For more information, see Major Version One-touch Upgrade of PolarDB for MySQL Engine. Note: Be sure to back up before upgrading to avoid other problems that may occur during the upgrade. In addition, the DAS team of DAMO Academy has also launched the intelligent stress testing capability to facilitate customers to perform stress testing on the flow playback of newly upgraded instances.
For information about converting MyISAM tables to InnoDB, see Converting Tables from MyISAM to InnoDB. In PolarDB for MySQL 8.0, creating a partition of MyISAM type will cause a storage engine partition table that does not support this type to fail to create statements and report an error (ER_CHECK_NOT_IMPLEMENTED).
The PolarDB for MySQLs storage engines now provide their own partition handlers, and PolarDB for MySQL servers no longer provide general-purpose engine partition support. InnoDB is the only storage engine that provides native partition handlers supported by PolarDB for MySQL 8.0. Before upgrading the server, you need to convert the partition tables of non-InnoDB storage engine to the partition tables of InnoDB storage engine, or delete the partition tables and convert them to regular tables. Otherwise, it cannot be used later. If there are similar partitions, you need to convert the engine in advance before upgrading. Check the engine syntax as follows:
<mysql> ALTER TABLE part ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)
OR
<mysql> ALTER TABLE part REMOVE PARTITIONING;
Query OK, 0 rows affected (0.06 sec)If you use a MySQLdump to import a database into a PolarDB for MySQL 8.0 server from a dump file created in MySQL 5.6 (or earlier), you must ensure that any statement that creates a partition table does not specify an unsupported storage engine. In doing so, you need to remove any references to the partition, specify the storage engine as InnoDB, or allow the engine to be set as the default value of InnoDB. The procedures in "Prepare Installation for Upgrade" describe how to identify partition tables that must be changed before upgrading to MySQL 8.0. For more information, see Storage Engine-Related Partition Restrictions.
In MySQL 8.0, the default field set is changed to utf8mb4. The character_set_server values of MySQL 8.0 and PolarDB for MySQL 8.0 are utf8 by default for compatibility. You can adjust it based on your business requirements. If you want to improve the Unicode support, you are recommended to convert the utf8mb3 character set to the utf8mb4 character set. The utf8mb3 character set is not recommended. In addition, it is recommended that use the utf8mb4 character set reference to replace utf8, because utf8 is an alias for the utf8mb3 character set. For more information, see utf8mb3 Character Set (3-byte UTF-8 unicode encoding) in the MySQL documentation.
MySQL 8.0 has added the default_collation_for_utf8mb4 parameter, which is used to sort according to the default collation when the character set is utf8mb4. The default value of the parameter is utf8mb4_0900_ai_ci. The official documentation indicates that it is only an internal parameter used by MySQL Replication. Therefore, we recommend that you do not change the default value to utf8mb4_general_ci/utf8_general_generic_ci if you do not encounter the Illegal mix of collations errors during the process of synchronizing from a lower version to a higher version.
utf8mb4_unicode_ci is used for general sorting and comparison based on official Unicode rules. The accuracy is high, but the comparison speed is slightly slower.
utf8mb4_general_ci is a collation that simplifies sets. Its purpose is to provide a simplified design to speed up. Although it does not follow the Unicode rules, the results are in line with expectations in the same situation.
Sorting rules are different from character sets. They are related to sorting and comparison, where ai refers to accent insensitivity. In other words, there is no difference between e,è,é,ê and ë when sorting, and ci means case insensitivity. That is, there is no difference between p and P during sorting.
Starting with MySQL 8.0.11, lower_case_table_names are prohibited from starting the server with settings different from those used when the server was initialized. This restriction is necessary because the collation used by various data dictionaries and table fields is based on the settings defined when the lower_case_table_names server is initialized. When the server is restarted with different settings, inconsistencies in how identifiers are sorted and compared will be introduced. In PolarDB for MySQL 8.0, the instance case sensitivity cannot be changed again after initialization. You must select whether to choose case sensitivity when you purchase a PolarDB for MySQL 8.0 instance.
To avoid startup failures of PolarDB for MySQL 8.0, remove all instances from the system variable sql_mode setting in the MySQL options file via NO_AUTO_CREATE_USER. Obsolete SQL modes must not be defined in your system variable settings. Otherwise, sql_mode will cause many different behaviors. Alignment must be confirmed during version upgrade. Cancel the following configuration items:
DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS , NO_KEY_OPTIONS, NO_TABLE_OPTIONSMost of the above configuration items are combined configurations. Pay attention to whether there are inconsistent mode options. For example, sql_mode=TRADITIONAL equals configuring the following items:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION.In the default configurations of PolarDB for MySQL 5.6/MySQL 5.6, the sql_mode=TRADITIONAL equals configuring the following items:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.Compared with the above configuration items, we can see that the NO_AUTO_CREATE_USER option is added in PolarDB for MySQL 5.6/MySQL 5.6. However, MySQL 8.0 has prohibited the implicit creation of accounts using GRANT statements. Although the NO_AUTO_CREATE_USER option is added in PolarDB for MySQL 5.6/MySQL 5.6, GRANT can also be used to create accounts when the identified by is specified.
If you find that enabling ONLY_FULL_GROUP_BY causes queries on the existing application to be rejected, you can use the following methods to resume the operation:
For example, this situation happens when the description is not part of the GROUP BY list, and no aggregation function (such as MIN or MAX) is applied.
Previous behavior:
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)PolarDB for MySQL 8.0:
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 nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byTables with information about systems and state variables in the INFORMATION_SCHEMA of MySQL 5.6/PolarDB for MySQL 5.6 are discarded in PolarDB for MySQL 8.0.
INFORMATION_SCHEMA.GLOBAL_VARIABLES
INFORMATION_SCHEMA.SESSION_VARIABLES
INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUSThe above obsolete tables are migrated to the PERFORMANCE_SCHEMA ofPolarDB for MySQL 8.0/MySQL 8.0.
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_userIf you want to use this view, it is recommended to use the SHOW command instead of using the corresponding view directly.
SHOW VARIABLES
SHOW STATUSINFORMATION_SCHEMA views based on InnoDB system tables are replaced by internal system views of data dictionary tables. The affected InnoDB INFORMATION_SCHEMA view has been renamed. When directly accessing InnoDB-related views in the system application, it is necessary to confirm whether they have been modified in the application.
| Old Name | New name |
| INNODB_SYS_COLUMNS | INNODB_COLUMNS/td> |
| 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 |
A view with the same name newly added in PolarDB for MySQL 8.0 cannot exist in MySQL 5.6/PolarDB for MySQL 5.6. Run the following statements in the instances of MySQL 5.6/PolarDB for MySQL 5.6. If there is a return, you need to confirm how to handle such tables. We recommend that you execute this check on the user-created instances during the cloud upgrade.
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',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
Suppose
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql | catalogs |
+--------------+------------+
1 row in set (0.00 sec)Therefore, such user tables should be renamed or deleted before the upgrade:
mysql>ALTER TABLE catalogs RENAME user_catalogs;
Query OK, 0 rows affected (0.05 sec)
OR
mysql> DROP TABLE catalogs;
Query OK, 0 rows affected (0.06 sec)Before MySQL 8.0, you can create views with explicit column names of up to 255 characters. In order not to exceed the maximum length of column names, MySQL 8.0 does not support views with explicit column names longer than 64 characters. At present, these views can only be identified by executing SHOW CREATE VIEW in MySQL 5.6.
mysql> SHOW CREATE VIEW v1;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a123456789012345678901234567890123456789012345678901234567890123456789` | utf8 | utf8_general_ci |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 secThe Password column mysql.user of the mysql.user system table is deleted after MySQL 5.7.6 and MySQL 8.0. All credentials are stored in the authentication_string column, including those previously stored in the Password column.
DYNAMIC replaces COMPACT and becomes the implicit default row format of InnoDB tables. A new configuration option innodb_default_row_format specifies the default InnoDB row format. The values (default) of DYNAMIC are allowed to include COMPACT, and REDUNDANT. After you upgrade to PolarDB for MySQL 8.0, any new tables that you create use the defined row format, innodb_default_row_format, unless you explicitly define the row format ( ROW_FORMAT). For the existing table ROW_FORMAT=DEFAULT that the ROW_FORMAT option is not explicitly defined or used, any reconstruction of the table will silently change the row format of the table to the defined format innodb_default_row_format. Otherwise, the existing table retains its current row format. For more information, see Define Table Row Formats.
The length of a single ENUM or SET column element of a table or stored procedure must not exceed 255 characters or 1020 bytes.
The YEAR(2) type is obsolete and needs to be replaced with YEAR(4).
Create a table with unsigned columns:
CREATE TABLE test (id int unsigned);Insert a negative value. The result in PolarDB for MySQL 5.6 is as follows:
INSERT INTO test VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)The result in PolarDB for MySQL 8.0 is as follows:
INSERT INTO test VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1Create a test table.
CREATE TABLE test2 (id int unsigned);Divide by zero. The result in PolarDB for MySQL 5.6 is as follows:
INSERT INTO test2 VALUES (0/0);
Query OK, 1 row affected (0.01 sec)The result in PolarDB for MySQL 8.0 is as follows:
INSERT INTO test2 VALUES (0/0);
ERROR 1365 (22012): Division by 0Inserting a 20-character string into a 10-character column will report an error. Create a table that contains a 10-character column:
CREATE TABLE test3 (a varchar(10));Try to insert a longer string. The result in PolarDB for MySQL 5.6 is as follows:
INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)The result in PolarDB for MySQL 8.0 is as follows:
INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');
ERROR 1406 (22001): Data too long for column 'a' at row 1Create a table that contains a datetime column:
CREATE TABLE test3 (a datetime);Insert 0000-00-00 00:00:00. The result in PolarDB for MySQL 5.6 is as follows:
INSERT INTO test3 VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)The result in PolarDB for MySQL 8.0 is as follows:
INSERT INTO test3 VALUES ('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1Data types such as legacy decimal, legacy varchar, and legacy TIME/DATETIME and TIMESTAMP are obsolete in MySQL 5.1, MySQL 5.0, and MySQL 5.6, respectively. This is because binary upgrades will not be supported in MySQL 8.0 as they are only available in MySQL 5.6. These tables can be identified by running CHECK TABLE…FOR UPGRADE or mysqlcheck with the check-upgrade option in MySQL 5.6 before the upgrade. In addition, tables that use the legacy TIME/DATETIME and TIMESTAMP can be identified by enabling session variables. For more information, see How to Easily Identify Tables With Temporal Types in Old Format!
mysql> check table 41_decimal for upgrade;
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| test.41_decimal | check | error | Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it! |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> check table 55_temporal for upgrade;
+------------------+-------+----------+------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+------------------------------------------------------------------------------------------+
| test.55_temporal | check | error | Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reload to fix it! |
+------------------+-------+----------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.6/dbg-5.6/client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.6/dbg-5.6/data/mysql.sock --databases test --check-upgrade
error : Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it!
test.55_temporal
error : Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reload to fix it!
test.child OK
test.geom OK
test.jemp OK
test.jemp_myisam OK
test.opening_lines OK
test.parent OK
test.t_blackhole OK
test.t_blob OK
test.t_blob_myisam OK
test.t_compressed OK
test.t_compressed2 OK
test.t_compressed3 OK
test.t_dynamic OK
test.t_gen_stored OK
test.t_gen_stored_myisam OK
test.t_gen_stored_myisam2 OK
test.t_index OK
test.t_json OK
test.t_myisam_compressed OK
test.t_myisam_compressed2 OK
test.t_myisam_compressed3 OK
test.t_sc~!@#$%^&*( OK
test.vt2 OKTables that use such data types cannot be upgraded and should be fixed via REPAIR TABLE and dumped or reloaded for legacy varchar or legacy decimal:
mysql> REPAIR TABLE 55_temporal;
+------------------+--------+----------+-------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------+----------+-------------------------------------------------------------------------------------+
| test.55_temporal | repair | Note | TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. |
| test.55_temporal | repair | status | OK |
+------------------+--------+----------+-------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql>
Dump:
$./client/mysqldump --databases test --socket=5.6/data/mysql.sock --user=root>test.sql
Restore:
mysql> .\ test.sql PolarDB for MySQL 8.0 can view the keywords and reserved words of the current version through the information_schema. KEYWORDS table to ensure that there are no illegal keywords and reserved words. Some previously unreserved keywords may be reserved in the PolarDB for MySQL 8.0. For more information, see Keywords and Reserved Words in the MySQL documentation. We recommend that you avoid using all custom content (table names, field names, and function names). KICKOUT is a reserved keyword of PolarDB for MySQL 8.0. Therefore, if you have used this keyword as an object name (such as a table name, a field name, or a stored procedure name) in MySQL 5.6 or open source MySQL 8.0, you must modify the object name to avoid using this keyword before you upgrade to PolarDB for MySQL 8.0. Otherwise, the error code 1064 that indicates a syntax error is returned.
Some businesses will use the self-built serial number generator functions nextval and currval. These two keywords are reserved words in RDS for MySQL 8.0. You need to change the function names or directly use the seq function provided by RDS.
CREATE sequence s START WITH 1 minvalue 1 MAXVALUE 9999999 increment BY 1 CACHE 20 cycle;
SELECT nextval(s),currval(s);In MySQL 8.0.11, several obsolete features related to account management, such as using GRANT statements to modify non-privileged features of user accounts, have been removed. Example:
GRANT REPLICATION CLIENT ON *.* TO 'odps'@'%'; You are not allowed to create a user with GRANT needs to be created by two steps
create user;
grant privielges;Starting with MySQL 8.0.13, the obsolete clause ASC or the DESC qualifier GROUP BY has been removed. The queries that relied on GROUP BY sorting might produce different results compared with those of previous MySQL versions. To generate a given sort order, you need to provide an ORDER BY clause.
select id,count(*) from sbtest.sbtest1 where id < 10 group by id descThe SQL statement needs to be rewritten as:
select id,count(*) from sbtest.sbtest1 where id < 10 group by id order by id#### Definition of Foreign Key Constraint
In MySQL 5.6, InnoDB that defines FOREIGN KEY must not have the keyword CONSTRAINT and specify that the foreign key constraint name must not exceed 64 characters. In versions earlier than MySQL 8.0, when you did not explicitly specify the foreign key constraint name, InnoDB would automatically generate the foreign key constraint name by appending '_ibfk_X' to the table name, where X is a number. If the table name is multi-byte 64 characters, such as the Cyrillic table name 'имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк 'used in the following example, the automatically generated foreign key constraint name exceeds 64 characters. You should change these tables by removing the constraint and adding a constraint with an explicit constraint name by ensuring that the foreign key constraint name does not exceed 64 characters.
mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` DROP FOREIGN KEY `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` ADD CONSTRAINT FOREIGN KEY FK1 (fld2) REFERENCES t1(fld1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0CREATE TRIGGER earlier than MySQL 5.0.17 does not support the definer attribute. Such trigger definitions with missing or empty definer attributes or invalid creation contexts (i.e. character_set_client, collation_collection, database collation attributes) persist, causing MySQL 5.6 to fail to upgrade. These triggers can be identified by running mysqlcheck or CHECK TABLE with the option to check for upgrades in MySQL 5.6.
$./client/mysqlcheck --user=root --socket=5.6/data/mysql.sock --databases triggers --check-upgrade
triggers.t1
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning : No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
status : OK
triggers.t2 OK
mysql> check table t1;
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | status | OK |
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)
mysql> select definer, trigger_name from INFORMATION_SCHEMA.TRIGGERS where definer='';
+---------+------------------------+
| definer | trigger_name |
+---------+------------------------+
| | trg_t1_before_insert |
| | t1_bi |
| | trg_t1_after_insert_1 |
| | trg_t1_after_insert |
| | trg_t1_after_insert_3 |
| | trg_t1_before_update_3 |
| | trg_t1_before_update |
| | trg_t1_after_update |
| | trg1 |
+---------+------------------------+
9 rows in set (0.02 sec)
mysql>Such triggers should be dumped or reloaded to resolve the following problems:
Dump:
$./client/mysqldump --databases triggers --socket=5.6/data/mysql.sock --user=root>triggers.sql
Restore:
mysql> .\ triggers.sqlPolarDB for MySQL 8.0 supports parallel queries. Parallel scanning causes MySQL's default serial scanning order to change randomly each time due to random data access, especially when paged SQL is involved. If you need to create a given sorting order, provide an ORDER BY clause to ensure the order.
ORDER BY in a subquery no longer works. Example:
SELECT *
FROM
(
SELECT * FROM `information_schema`. TABLES
ORDER BY table_name DESC
) AS sg
GROUP BY table_nameThe ORDER BY of the inner layer will be ignored by the optimizers of PolarDB for MySQL 5.7 and 8.0. You need to modify the statement. The simplest method is to add a limit to make the sorting take effect. For example:
SELECT *
FROM
(
SELECT * FROM `information_schema`. TABLES
ORDER BY table_name DESC limit 10000 # 需要足够大的行数
) AS sg
GROUP BY table_nameThe optimizer now treats derived tables and views in clauses in a consistent manner. It uses FROM to better avoid unnecessary materialization and allows the use of pushdown conditions that produce more efficient execution plans. However, in PolarDB for MySQL 8.0 and statements such as modifying a table, if DELETE uses the merge strategy for a derived table previously implemented by UPDATE, the ER_UPDATE_TABLE_USED error will occur:
mysql> 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 clauseMerging a derived table into an outer query block will cause an error when you select and modify table statements from the table. (Materialization does not cause problems because it actually converts the derived table to a separate table.) The workaround to avoid this error is to disable the derived_merge flag, optimizer_switch, of the system variable before executing the statement.
SET optimizer_switch = 'derived_merge=off';This derived_merge flag controls whether the optimizer attempts to merge subqueries and views in the FROM clause into the outer query block. Assume that there are no other rules preventing merging. By default, the merging is enabled when the flag is on. Set the flag to off to prevent merging and avoid the error just described. For more information, see Section 8.2.2.4: Using Merge or Implementation to Optimize Derived Tables and Viewing References.
(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);Previous versions of MySQL may allow such statements without parentheses. However, in PolarDB for MySQL 8.0, parentheses are mandatory.
The GET_LOCK() function was reimplemented in MySQL 5.7.5 using the metadata locking (MDL) subsystem, and its function has been extended:
For more information, see Section 12.15: Locking Functions.
For Java applications, MySQL Connector/J is recommended to be upgraded to version 8.0.8 and above. Version 8.0.8 and above can connect to MySQL 8.0 server, but caching_sha2_password is used. (Connector/J 8.0.9 or higher caching_sha2_password is required for connection accounts.).
If dataworks is not set to utf8 in the data source, an error may occur. It is recommended to change the database name to be consistent with RDS. Add the following connection string to PolarDB for MySQL: characterEncoding=utf8&com.mysql.jdbc.faultInjection.serverCharsetIndex=
The tx_read_only Environment Changes Have Been Deleted in PolarDB for MySQL 8.0 and Need to Be Replaced by transaction_read_only.
select @@tx_read_onlyNeed to be changed to:
select @@transaction_read_onlyThe official website provides the version 8.0 upgrade checker, which is not supported by PolarDB for MySQL. For more information about upgrading self-built databases, see Checker.
Compare the Differences Between MySQL 5.6, 5.7, and 8.0.
Best Practices for Migrating MariaDB 10.2 to PolarDB for MySQL 5.7
7 posts | 0 followers
FollowJDP - December 23, 2021
Morningking - September 26, 2023
ApsaraDB - February 4, 2024
Pum - July 10, 2023
ApsaraDB - January 22, 2021
Alibaba Clouder - February 2, 2021
7 posts | 0 followers
Follow
Best Practices
Follow our step-by-step best practices guides to build your own business case.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
PolarDB for Xscale
Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn MoreMore Posts by Morningking