Revision Upgrade

The simplest form of upgrade is when upgrading when only the revision number is changed. This is commonly referred to the Z in the X.Y.Z version number or simply “the version of the major.minor release.” For example, version 5.7.20 is revision 20 or version 20 of 5.7.

Upgrading at this version level is generally safe and, although not guaranteed to work flawlessly, is low risk. However, you still should take the precaution of reading the release notes before executing the upgrade. This is especially true if you are working with nongeneral availability (GA) releases. If the release is not a GA release, you must pay attention to the release notes and upgrade section in the online MySQL reference manual. Although it is rare, sometimes there are special considerations you must plan for and overcome to achieve the upgrade. Fortunately, Oracle does an excellent job of communicating any necessary steps and procedures—you just need to read the documentation!

Minor Upgrade

The next form of upgrade is upgrading when the minor number is changed. This is commonly referred to the Y in the X.Y.Z version number—for example, upgrading from 5.6 to 5.7.

Upgrades are generally acceptable and documented for single digit increment of the minor version. For example, the upgrade from 5.6 to 5.7 is supported, but an upgrade from 5.0 to 5.7 is not directly supported. This is because there are too many differences between the versions to make an upgrade viable (but not impossible).

Nevertheless, you can upgrade minor version changes with manageable risk if you plan accordingly. More about managing the risk in later sections.

Major Upgrade

The next form of upgrade is when upgrading when the major number is changed. This category—aside from the incompatible versions—is the one with the most risk and potentially the most likely to require more work.

Upgrades of versions at the major version are rare and only occur when Oracle has released a new, major set of changes (hence the name) to the server. MySQL 8 server contains many improvements over MySQL 5—most have been tremendous increases in performance, advanced features, and stability. However, there have been a few changes that have rendered some features in older versions incompatible.

For example, once MySQL 8.0 is released as GA, upgrading from MySQL 5.7 to MySQL 8.0 is supported but you may have to migrate certain features to complete the upgrade.

It is fortunate that Oracle has documented all the problem areas in detail with suggestions on how to migrate to the new features. We’ve even seen this extend beyond major versions—the MySQL Document Store is a very good example.

Incompatible Upgrades

Without a doubt, the incompatibilities are dependent on certain features, hardware, or internal storage mechanisms. In most cases, the online documentation outlines what you can do to ensure success. Sometimes this requires following a specific upgrade path such as first upgrading to one version before upgrading to your target version.

Now that we have a good idea of what types of upgrades are possible, let’s look at some best practices for performing the upgrade.

Check Prerequisites

The first thing you should do when upgrading MySQL is to check the documentation for any prerequisites. Sometimes this is simply safely backing up your data, but also can include things such as which utilities and tools you need to use to migrate certain features (or data). Be sure you have all the prerequisites met before you being the upgrade.

The upgrade documentation also will include incompatibility issues. Most often, this occurs when upgrading major versions but sometimes this happens for minor versions. It is fortunate that these are outlined in the online MySQL reference manual. Checking the prerequisites also can help you by providing details you can use to plan the upgrade.

Once you’ve read through the documentation, one of the things you will want to do as a prerequisite is to use the mysqlcheck utility to check your MySQL installation for compatibilities. For example, one of the prerequisites for upgrading to MySQL 8 is that, per the section entitled, “MySQL Upgrade Strategies” in the online MySQL reference manual, “there must be no tables that use obsolete data types, obsolete functions, orphan .frm files, InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context.” We can use the mysqlcheck utility to identify any of these conditions as shown in Listing 10-1.

For best results, you should use the mysqlcheck utility from the version you are upgrading. This will ensure the utility is the most up to date and should identify more upgrade issues.

Plan the Upgrade

Once you have all the prerequisites mapped out and have identified any features that require special handling to solve incompatibilities, it is time to plan for upgrading your server. This may be an obvious thing to do if you have thousands of servers, but less obvious to those with only a few (or even one) server to upgrade.

You should resist the temptation to simply run the upgrade without planning what you are going to do. Recall, we want to ensure the upgrade goes smoothly by reducing (or eliminating) risk. This is much more critical for production environments, but any potential loss of availability, performance, or data can result in loss of productivity.

You can get most of what you need to plan from the documentation, but the documentation won’t be specific to your installation, servers, platform, and so forth. Therefore, you must fill in those blanks and adapt the procedures suggested in the documentation to your own installation. However, you can learn quite a lot by reading the section, “What’s New in MySQL 8.0,” paying attention to any subsections labeled “Ramifications for Upgrades” in the online MySQL reference manual. There you will find tips that may help you avoid some complicated decisions, or better to avoid complex repairs.

This also includes making sure you have the right personnel on hand to do the upgrade or to be ready to jump in case something goes wrong.¹ For example, don’t forget your developers, web administrators, and other critical roles.

The form of the plan is up to you; however, I suggest that you write what you plan to do and share it with others. This way, everyone in the chain of ownership of the upgrade will know what is to be done. You will be surprised how much a little communication can do to reduce risk of things going wrong.

Consider Parallel Deployment

One practice that can help the most when upgrading systems that require more than a trivial amount of work is installing the new version parallel to the existing version. This is a practice known to software engineering and is designed to ensure the existing data and applications remain unchanged while the new system is being installed and configured. The new version (installation) would be considered a development platform and often goes into production once sufficient testing of the migration is complete.

Although this isn’t an upgrade per se (it’s a new installation), having a new parallel version of MySQL running gives considerable freedom in how to attack the migration of your existing data and applications. After all, if something goes wrong, your data is still operational on the old system.

This practice also provides you another benefit: you can change platforms or other major hardware without having to risk your existing data. Therefore, if your existing servers have hardware that is to be updated at the same time, you can use a parallel installation to install MySQL on the new hardware thus isolating the risks with the new hardware.

Finally, employing a parallel installation may help with scheduling and planning your migration by ensuring the existing systems are fully capable. And, better, you can always go back to the old system if during the migration something goes wrong.

Parallel deployment often includes keeping both systems running for some period. The length of time may depend on the amount of risk you’re willing to take or it may be based on how long it takes to fully switchover all your applications.

It is unfortunate that some may not have the resources available to consider parallel deployments. As having two installations of MySQL running at the same time may place a greater burden on developers, administrators, and support personnel. Given the benefits of parallel development, it may be worth adding extra resources or accepting less productivity of some personnel for a short period.

However, even this safety net is tenuous if you don’t perform enough testing.

Test, Test, Test!

This practice, along with planning, is often overlooked or given far less importance. Sometimes this is due to external forces such as not having the right personnel available or failures in planning that results in no time for extensive testing. Regardless of the excuse, failing to adequately test your upgrade increases risk beyond what most would be willing to endure.

Testing should include that you ensure all the data has been migrated, all applications work completely, and all access (user accounts, permissions, etc.) are functional. However, don’t stop there. You should also ensure all your operational practices have been modified for the new version. More specific, your maintenance scripts, procedures, and tools all work correctly with the new version.

Furthermore, your testing should result in a go/no go decision to accept the upgrade. If things are not working or there are too many issues, you may need to decide to keep or reject the upgrade. The parallel installation practice can help in this manner because you don’t destroy the existing data or installation until you are certain everything is working. Make sure that you write those criteria into your plan and to ensure success.

Production Deployment Strategies

If you have a production and development (or test) environment, you also should consider how to move the development or test deployments to production. If you are using parallel installations, it may be simply switching application routers and similar appliances and applications. If you are using in place installations, it may be more involved. For example, you may need to plan for a period of downtime to complete the migration.

For parallel installations, planning the downtime may be more precise and involve a shorter period because you have more time to test things. However, for in place upgrades, you may need to set aside a period to complete the migration. As expected, you will want to minimize the downtime by doing as much of the migration as you can. But in the base of MySQL, this may be nothing more than forming a plan and gathering resources. The bottom line is, don’t forsake including production deployment in your plan.

Now that we’ve discussed upgrade practices, let’s take a moment to discuss some reasons we may want to consider performing the upgrade, which clearly can be a very involved process with a certain amount of risk.

View Data as Code

One of the hardest concepts about schemaless designs for those who have worked with relational databases is that the data (document) should (can) be viewed as code. Consider the JSON structure—it’s code! Thus, thinking about your data as part of your code will help you design better documents.

For example, if you know your document contains elements that are lists, and you need to iterate over the items in those lists, code to do this is typically some form of loop or iterator mechanism such as a for each or for X in Y construct. Thus, you can view the document has having data that is used in those looping constructs. Yes, this is akin to thinking in result sets (arrays of rows), but in this case, we’re much closer to the real code. In fact, due to the uniqueness of how JSON works, we can write code to reference the elements of the document rather than an abstraction layer as we see in result set processing. That is, we access the field by name rather than asking a library to give us the “nth” field. This results in code that is easier to read and is described by the data (and vice-versa).

Migrating to a Hybrid Solution

Removing joins is a good strategy for improving performance in retrieving data. It also plays to one of the standards of schemaless design; keep the data together. Both are strategies that fall under denormalizing the data. The example contact list database in the last section was normalized to include four tables: one for the contact name, and one each to contain all the addresses, phone numbers, and email addresses.

The goal wasn't to make the separate tables searchable or even accessible and presented on their own. After all, what use would anyone have to see a list of phone numbers without any connection to the owner? Rather, the normalization was meant to keep like data together and remove duplication.

For example, it is possible if you know several people who work at the same place that their work address and phone numbers will be the same. Likewise, members of the same family may have the same address and phone numbers.⁴ Normalization then results in a main table with three dependent tables in a one-to-many relationship.

However, if you consider that we will seldom need to query the phone number, email address, or address table separately and that the data in those tables is associated with a contact and that it only makes sense to view the data as a set, we can denormalize the data by embedding it in the contact data.

We can do this easily by simply adding three fields to the contacts table using JSON fields to embed the data and still maintain the structure. Recall, we can use a JSON document in code and thus all the field names from the original tables can be used. When you do it this way, migrating the code is easier because you will be referring to the same data names. The following shows a redesign of the database to use a hybrid solution.

Here, we eliminated the three tables by adding the relations as JSON arrays. But wait, how do we format those JSON documents? Isn’t that going to be a problem? No, not really. To migrate to a hybrid solution you would carry over the field names from the embedded data using them as keys in the JSON document.

We can also improve the data for the phone numbers by adding keys to reference the area code, exchange, and phone number. Better, if we later find that we need to add a country code value, we can do so for those contacts that require them. Remember, the beauty of JSON documents is that they are mutable and you can add or leave out fields as needed. The only catch is your code must be written to expect the omissions and new fields. Thus, there is no reason to retool your data to add a country code to new contacts.

The following shows how this is done for the create operation using the same contact shown in the last section.

This results in an interesting row returned from a SELECT query. We see the result in the following.

Here, see that we’ve used the same names as the fields in the original tables. Accessing the data is made easier as we can migrate our code from looking for columns in row objects to using the field names directly in code. For example, to display the embedded lists of addresses, phones, and emails, we can use loops. Listing 10-4 shows an example script to do this.

Note that the code reads well and we can see exactly which data we’re accessing in the loops. However, there is some duplication when converting the JSON strings to Python dictionaries. This is because we have a field in the table and a key in the JSON string with the same name. For example, there is an addresses field and the key in the JSON document is addresses. This might look a little odd, but it is precisely how you would access the JSON document in the fields. Some may want to rename the field or JSON key to make it a bit less ambiguous.

The following shows this code executing. Note the output does resemble how you would expect to see the data by reading the code. The use of tabs ( ) is helpful for printing strings to a console.

This solution is better and does solve the issue of removing joins and keeping the data together, but what if we need to store a surname, suffix, title, or if there are contacts with more than two names? Likewise, what if you found you needed to find all contacts that live or work in a certain area code? The data is there, but because the phone number is a single string, it’s harder to search for the data (but not impossible).

We can solve this problem (and similar issues) by simply altering the table to break out the data into separate fields. That will work and that is what most developers will do. However, what do you do with any existing data? Do you go back and reformat using special tools you create yourself? You don’t have any choice and if there is a lot of data, the conversion can be painful and time consuming.

Sadly, hybrid solutions with relational parts (table, fields) are still fixed and thus hybrid solutions are not immune to changes. What we need is to achieve mutability—to be able to change the structure whenever we need to do so without having to retool. If you’re thinking, “there’s got to be a better way,” you are correct—there is. Let’s see how to overcome these issues by converting the database to a pure document store.

Converting to a Document Store

Perhaps the best attribute of document store solutions is mutability. That, along with the “data as code” concept makes working with document stores so much easier than relational databases. Although we saw some improvements in a hybrid solution for the contacts database, we are not quite to the point where mutability is possible.

To be specific, we still have fixed fields in the hybrid solution. If these fields were the complete set (for all time), we might be satisfied with a hybrid solution. But if you work in an international setting, you will find that storing first and last names are far too casual and, in some cases, insufficient.

For example, Geraldo Jose Miguel Gomez. What do you do with such a name? Split the name arbitrarily placing the parts in the two fields? What if the individual uses Miguel as his first name? Now, your database will list his name as “Miguel”, “Geraldo Jose Gomez”, which is not correct. Furthermore, if you do split the name in such a manner, any queries on first name or last name for that matter are subject to incorrect results or at least additional parsing after the query to sort out these anomalies.

If we used a document store, we can add whatever fields we need. We just need to keep our code and data synchronized. That is, when we add new fields, we must also add code in our CRUD operations to compensate. For example, adding a nickname field to the document is easy, but the code that reads the data and displays it must allow for working with the nickname. Best of all, we can add the change and don’t have to rework any existing data.

This, then, is the goal: to make your data schemaless and integrated tightly with the code. Once you adopt this mindset, you will find the stigma of normalization easy to cast off. Although that doesn’t mean all schemaless solutions will outperform their relational counterparts (unlikely in fact), it does mean you can make the data work for you instead of against you. Developers especially will appreciate the freedom.

As we learned in Chapter 9 and earlier in the book, we can create a collection easily in code. Recall, this includes connecting to the server, getting a schema object instance, and creating the collection. We can then create, read, update, or delete documents in the collection. Figure 10-2 shows a snapshot of using the MySQL Shell to create the schema and collection for the contact list. We also add the row we’ve been using as an example.

Here, we see we have migrated our rigid relational database model to a mutable JSON document with embedded data that keeps all the data together for each contact.

Now, let’s look at the code to perform a read operation on the document store. Listing 10-5 shows the code to read the document from the collection and print it to the console.

Note that the code is very similar to the hybrid solution. In fact, the print sections are the same. The difference is seen early on when retrieving the data. In this case, we can retrieve the document and then store the addresses, phones, and email addresses as dictionaries, which makes the code even easier to read. Very nice!

The following shows the code executing. As you can see, the output is the same as the hybrid solution.

Now that we’ve discussed what a schemaless mindset is, let’s review some tips and tricks for working with the MySQL Document Store.