PolarDB:Best practices

lower_case_table_names

Since MySQL 8.0.11, it is prohibited to use a value of the lower_case_table_names parameter that is different from that defined during server initialization to start the server. The collations used by various data dictionaries and table fields are based on the lower_case_table_names value that is defined during server initialization. If the server is restarted with a different value, inconsistencies are introduced in the way identifiers are sorted and compared. In PolarDB for MySQL 8.0, the case sensitivity of clusters cannot be changed after initialization. You must select whether a PolarDB for MySQL 8.0 cluster is case-sensitive when you purchase the cluster. For more information about the lower_case_table_names parameter, see lower_case_table_names.

sql_mode

To prevent startup failures for clusters of version 8.0, delete all clusters from system variable sql_mode in the MySQL option file by using the NO_AUTO_CREATE_USER option.

Do not define obsolete SQL modes in the system variable. Otherwise, sql_mode may result in many different behaviors. Confirmation and alignment are required during the version upgrade. The following options must be deleted:

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

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)

Compatibility with InnoDB-related views

The views based on InnoDB system tables in INFORMATION_SCHEMA are replaced with internal system views on data dictionary tables. The views that affect INFORMATION_SCHEMA of InnoDB are renamed. If you access the InnoDB-related views in a system application, check whether the views have been modified in the application. For more information about INFORMATION_SCHEMA, see INFORMATION_SCHEMA Tables.

Renamed InnoDB information schema views

Make sure that no views in MySQL 5.6 and 5.7 or PolarDB for MySQL 5.6 and 5.7 use the same names as the views that are added in PolarDB for MySQL 8.0. Execute the following statement in a MySQL 5.7 or PolarDB for MySQL 5.7 cluster. If any information is returned, determine how to process such tables. We recommend that you perform this check when you migrate a self-managed cluster to the cloud for upgrade.

'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)

Execute the following statement in a MySQL 5.6 or 5.7 cluster. If any information is returned, determine how to process such tables. We recommend that you perform this check when you migrate a self-managed cluster to the cloud for 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',

The user tables that are returned must be renamed or deleted before the upgrade. Execute the following statement to rename a user table:

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

Execute the following statement to delete a user table:

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;

Compatibility with system tables in version 5.6

The Password column is deleted from the mysql.user system table in versions later than MySQL 5.7.6 and 8.0. All credentials are stored in the authentication_string column, including data that was stored in the Password column in earlier versions.

Compatibility with tables that contain system and status variables in INFORMATION_SCHEMA of version 5.6

Tables that contain system and status variables in INFORMATION_SCHEMA of MySQL 5.6 and PolarDB for MySQL 5.6 are discontinued in version 8.0. The following tables are discontinued:

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

In version 8.0, the preceding discontinued tables are migrated to PERFORMANCE_SCHEMA.

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

We recommend you run the SHOW command instead of directly using the corresponding views.

SHOW VARIABLES
SHOW STATUS

Compatibility with InnoDB tables in version 5.6

The implicit default row format for InnoDB tables is changed to DYNAMIC from COMPACT. The innodb_default_row_format parameter specifies the default InnoDB row format. The COMPACT (default) and REDUNDANT values can be changed to DYNAMIC. After the upgrade to version 8.0, any new tables that you create use the defined row format unless you explicitly specify the ROW_FORMAT option. If the ROW_FORMAT option is not explicitly specified or ROW_FORMAT=DEFAULT is used, an operation that rebuilds a table changes the row format of the table to the format specified by the innodb_default_row_format parameter. For more information, see Defining the Row Format of a Table.

Compatibility with the GET_LOCK function in version 5.6

Type compatibility

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

Keywords and reserved words

In PolarDB for MySQL 8.0, you can view the keywords and reserved words that are used in the current version in the information_schema.KEYWORDS table. Make sure that no names conflict with these keywords or reserved words. Some previously unreserved keywords may be reserved in PolarDB for MySQL 8.0. For more information, see Keywords and Reserved Words. We recommend that you do not use keywords or reserved words in all custom content, such as table names, field names, and function names. In addition, KICKOUT is a reserved keyword in PolarDB for MySQL 8.0. If you have used this keyword as an object name, such as a table name, field name, or stored procedure name, in MySQL 5.6, MySQL 5.7, or native MySQL 8.0, we recommend that you change the object name before the migration to PolarDB for MySQL 8.0. Otherwise, a syntax error with error code 1064 is reported.

GRANT authorization

In MySQL 8.0.11, some discontinued features related to account management are deleted, such as the feature for using the GRANT statement to modify non-privileged features of user accounts.

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

GROUP BY clauses that contain ASC or DESC

Since MySQL 8.0.13, the ASC or DESC qualifiers that are not recommended for GROUP BY clauses have been deleted. Queries that previously relied on GROUP BY sorting may produce results that differ from those in previous MySQL versions. To produce a given sort order, you must rewrite GROUP BY clauses. Example:

Original SQL statement:

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

Rewritten SQL statement:

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

Foreign key constraint definition

In MySQL 5.6 and 5.7, the definition of InnoDB FOREIGN KEY cannot contain the CONSTRAINT keyword, and the name of a foreign key constraint cannot exceed 64 characters in length. In versions earlier than MySQL 8.0, if you do not explicitly specify a name for a foreign key constraint, InnoDB automatically generates a foreign key constraint name by appending _ibfk_X to the table name, in which X is a number. For a multi-byte 64-character table name, such as the Silil table name имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк that is used in the following example, the automatically generated name of the foreign key constraint will consist of more than 64 characters in length. To change these tables, add a constraint with an explicit constraint name that does not exceed 64 characters in length.

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

Spatial functions in version 5.7

In MySQL 5.7, we recommend that you do not use spatial functions that are available under multiple names. Example:

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)

These spatial functions have been removed in version 8.0 because of the change to spatial function names. This change helps maintain the consistency of the naming convention. The functions whose names start with ST_ perform exact operations, whereas the functions whose names start with MBR perform operations based on minimum bounding rectangles. The columns that are generated by using these functions must be changed before the upgrade. For more information about the removed spatial functions, see Features Removed in MySQL 8.0. You need to change the columns to use functions whose names start with ST_ or MBR.

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

Trigger compatibility

In versions earlier than MySQL 5.0.17, the CREATE TRIGGER statement does not support the definer attribute. This type of trigger definition with missing or empty definer attribute or invalid context, including character_set_client, collation_collection, and database collation attributes, causes an upgrade failure from MySQL 5.6 and 5.7. You can identify these triggers by running mysqlcheck with the check-upgrade option or the CHECK TABLE statement in MySQL 5.6 and 5.7.

$./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 or reload such triggers to resolve the issue.

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

Sorting issue caused by parallel queries

In version 8.0 or later, parallel queries are supported. Parallel scanning results in random change of the default order of MySQL serial scans each time because of random access to data, especially for the SQL statements for paged queries. To produce a given sort order, add an ORDER BY clause.