MySQL 5.7 Upgrade Issue: Reserved Words

MySQL 5.7 reaches End of Life status this October. If you still need to start your migration, time is getting short. The first step for many is looking into the new reserved words in MySQL 8.0. As MySQL gets new functionality or the project matures, there are new additions to the list of reserved words you can not use as column names.

Reserved words added to 8.0

There is a list of the new reserved words later in this document that you need to review.

Odds are you are not using a column named Master_tls_ciphersuites, but what about Rank, System, Skip, or Lead? Those are a lot more common and may be in your table definitions, so your upgrade process will be harder. It is recommended that you use the util.checkForServerUpgrade() in the MySQL Shell to check for these Reserved Words.

So what happens if I use a reserved word?

If you try to use a reserved word, the server will not explicitly admonish you that you are trying to use a reserved word. Instead, the server informs you that you have a syntax error.

This can be confusing as the Data Description Langauge (DDL) looks correct and would literally work if you replaced the word that causes the problem with almost any other word. In the following example, the keyword ‘window’ is causing the problem. The lack of clarity in the error message can cause a lot of confusion and send you off to the documentation trying to find a non-obvious error.

Is there a workaround?

Yes, there is a workaround. In the example below, the word ‘window’ is encapsulated by backticks. The ‘`’ backtick is easy to confuse with the single quote and may be indistinguishable from the single quote in some character sets. For those reasons and the ease of forgetting to add these ticks, I do not advise you to use this workaround continuously. For a quick ‘I got to do this for the 8.0 upgrade, and I will revise the code later’ fix, it will suffice.

Do reserved words ever get unreserved?

Words that are no longer reserved in MySQL 8.0 include Analyse (AnalyZe is still reserved), Des_key_file, master_server_id, Redo-file, Remote, and Sql_cache. You can feel free to use them to your heart’s desire.

Questions?

Percona is an open source database software, support, and services company that helps make databases and applications run better. If you have questions, need a helping hand, or want to outsource your MySQL 5.7 to 8.0 upgrade, please contact us at Percona.com.

Need extra time to upgrade? While we recommend that everyone upgrade to MySQL 8.0, we recognize that some organizations may need additional time. Therefore, we’re offering extended support for MySQL 5.7 on a subscription basis. If that sounds like something your organization needs, more details will be forthcoming on this blog.

The new 8.0 reserved words

Active, Array, Attribute, Authentication, Buckets, Bulk, Challenge_response, Clone, Cume_dist, Definition, Dense_rank, Description, Empty, Enforced, Engine_attribute, Exclude, Factor, Failed_login_attempts, Finish, First_value, Following, Generate, Geomcollection, Get_source_public_key, Grouping, Groups, Gtid_only, History, Histogram, Inactive, Initial, Initiate, Intersect, Json_table, Keyring, Lag, Last_value, Laterl, Lead, Locked, Master_compression_algorhythm, Master_public_key_path, Master_tls_cyphersuites, Master_zstd_compresion_level, Member, Nested, Nowait, Nth_value, Ntile, Nulls, Of, Off, Oj, Old, Optional, Ordinality, Organization, Others, Over, Password_lock_time, Path, Percent_rank, Persit_only, Preceding, Privilege_check_user, Process, Random, Rank, Recursive, Reference, Registration, Remote, Replica, Replicas, Require_row_format, Resource, Restart, Respect, Retain, Returning, Reuse, Row_number, Secondary, Secondary_engine, Secondary_engine_attribute, Secondary_load, Secondary_unload, Skip, Source_auto_position, Source_bind, Source_auto_postion, Source_compress_algorhythm, Source_connect_retry, Source_delay, Source_heartbeat_period, Source_host, Source_log_file, Source_log_pos, Source_password, Source_port, Source_public_key_path, Source_retry_count, Source_ssl, Source_ssl_ca, Source_ssl_capath, Source_sll_cert, Source_ssl_cypher, Source_ssl_crl, Source_ssl_crlpath, Source_ssl_key, Source_ssl_verify_server_cert, Source_tls_ciphersuites, Source_tls_version, Source_user, Source_zstd_compression_level, Srid, Stream, System, Thread_priority, Ties, Tls, Unbounded, Unregister, Url, Vcpu, Window, and Zone.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

Subscribe

Connect with

Login

Label

0 Comments