MySQL 8 Doesn’t Support Some of the Features You May Have Used
We often discuss the new features added in new MySQL versions, but it becomes equally important to discuss the removed features that you’ll no longer be able to use. This article discusses some earlier version features that you won’t be able to use in MySQL 8 because they have been removed.
Rename the INFORMATION_SCHEMA View Names
The INFORMATION_SCHEMA provides information about the database itself; the database name, the table name, column data types, access privileges, and such. The INFORMATION_SCHEMA stores information in read-only tables, which are actually views. With MySQL 8.0.3, the views are not based on InnoDB system tables; rather they are based on data dictionary tables. The view names are shortened to remove the _SYS in the view names. For example, INNODB_SYS_TABLES is renamed to INNODB_SYS_TABLES, and INNODB_SYS_TABLESPACES is renamed to INNODB_SYS_TABLESPACES. Any scripts that use the old names must be updated to use the new view names.
User Account Management Is Slightly Different
You won’t be able to create a new user with the GRANT statement by removing the NO_AUTO_CREATE_USER from the SQL modes list. The NO_AUTO_CREATE_USER itself has been removed. Use the CREATE USER statement to create a new user. You won’t also be able to modify account properties such as authentication, resource limit properties, and SSL with the GRANT statement. You can, however, still make privilege assignments with GRANT.
You won’t be able to use the IDENTIFIED BY PASSWORD auth_string syntax with CREATE USER and GRANT. Instead, use the IDENTIFIED WITH auth_plugin AS auth_string syntax with CREATE USER and ALTER USER.
The PASSWORD() function that is previously used to return a hashed password from a string password has been removed. The PASSWORD() function cannot be removed in the SET PASSWORD statement.
Query Cache Is Removed
You may have depended on the query cache in the past to cache your SQL SELECT statements together with the results. The query cache is often useful if tables don’t change much and the server gets several identical queries. So, instead of rerunning the query, the result is fetched from the query cache. Why was the query cache removed if it was useful? Because it was shown to have scalability issues. Specifically, the query cache doesn’t scale well with high throughput workloads on multi-core machines. As alternatives, you could use Server-side Query Rewrite or ProxySQL.
The DDL Log Is Removed
In MySQL 5.7 the DDL Log, or metadata log, is used to store the metadata operations generated by data definition statements. The DDL log is a binary log in the ddl_log.log file in the MySQL data directory. The DDL log could be used to perform redo and rollback of DDL operations. In MySQL 8.0 the DDL log has been removed. DDL logs are written to the mysql.innodb_ddl_log data dictionary table, which could be used to perform redo and rollback of DDL if needed.
Sorting with GROUP BY Has Been Removed
If you have been using the GROUP BY along with the ASC and DESC modifiers to perform sorting, you would need to use the ORDER BY clause instead.
\N as a Synonym for NULL
You can no longer use \N as a synonym for NULL in SQL statements. Instead, use NULL itself. You would still be able to use \N as a synonym for NULL in the import and export of data from a file in the LOAD DATA and SELECT … INTO OUTFILE statements.
Several other minor changes have been made, including the renaming of the JSON_APPEND() function to JSON_ARRAY_APPEND().
