Primary keys are the set of columns that uniquely identify a single row in a table, and no two rows match the same key. Using primary keys helps create faster queries and permits you to perform targeted updates. Savvy database developers know that using primary keys is a common best practice, and many features require using primary keys on all tables. Simply stated, creating tables without primary keys is not good practice, especially for tables with large amounts of data.

However, there may be rare times when you may not be able to use primary keys either because existing data simply data sources do not support it or because no subset of columns can be used to identify a row uniquely. While you could use an auto-increment column that acts like a surrogate primary key, there are times when you may not want to include such information because the data may never be updated, indexes on other columns fulfill query optimization or performance goals, or the dataset is so vast that adding another column will add unnecessary complexity.

In these cases, and as a last resort, you can use a new feature named generated invisible primary keys (GIPK) that, as the name suggests, does not add a visible column that you will see in your query results. Thus, when you enable GIPKs, you do not need to change your application or scripts that interact with your data to account for another column. Nice!

You can enable GIPKs in your MySQL configuration on-prem or for MySQL HeatWave in the Oracle Cloud Infrastructure (OCI). We will focus on the OCI for this blog and see how to automatically generate primary keys to help us use the high-availability feature for MySQL HeatWave DB Systems.

Why Would I Use GIPKs?

If you plan to create a high-availability MDS DB System and use data containing tables without primary keys, you must add primary keys to any existing tables. However, unless you enable the GIPK option, any table you attempt to create after enabling high availability will fail if they do not have a primary key. This is where the GIPK option helps you the most. It allows you to continue to create tables without primary keys creating them for you.

The following demonstrates how you can check the status of this variable and enable it (set it to ON) for an on-prem MySQL server. The mechanism to enable the variable for MySQL HeatWave DB Systems differs, and we will see how to do that in the next section.

Enabling GIPKs on an on-prem MySQL Server

For MySQL server releases 8.0.30 and later, GIPKs can be automatically generated for InnoDB tables created without a primary key if the sql_generate_invisible_primary_key server system variable is set to ON (default = OFF).

 

With this variable turned off, and you do not have high availability enabled, you can create tables without primary keys, as demonstrated below.

 

If you enable the option (set its value to ON) and create a table without a primary key, the server will add one for you, as demonstrated below.

 

Here, we see that a primary key was generated automatically and is marked invisible. As demonstrated below, any SELECT queries executed on the table will not include the invisible primary key.

 

Interestingly, if you want to see the invisible columns in the result set, you can explicitly request it in the column set option, as demonstrated below.

 

Now, let’s see how to enable GIPKs for MySQL HeatWave.

For more information about generating invisible primary keys for on-prem MySQL and how to use them with on-prem MySQL replication, see Generated Invisible Primary Keys.

Enabling GIPKs on a MySQL HeatWave DB System

To enable GIPKs, you must alter the MySQL configuration object for the DB System. This is because the administrator user account you created when you created the DB System does not have permission to execute the SET command as we did in the previous section. Fortunately, you can set variables like the sql_generate_invisible_primary_key variable using a MySQL configuration object.

Since the MySQL configuration is an object, you could create any number of configurations for use with MySQL HeatWave DB Systems. However, the MySQL configuration object is closely associated with the MySQL configuration object that is part of the shape object. Thus, a MySQL object must be based on a shape object.

Tip: See the Configuration of a DB System documentation for more details about MySQL configurations.

Furthermore, while you could create a new MySQL configuration and then apply it to one or more MySQL HeatWave DB Systems, the most straightforward process is to view the DB System to select and copy its MySQL configuration, which eliminates the potential to choose the incorrect shape and other options mistakenly.

This section demonstrates how to change the MySQL configuration for a DB System for MySQL HeatWave. The process requires creating a copy of the existing configuration, changing the desired variable, and then applying the new configuration to a DB System.

Note: Changing the MySQL configuration for a DB System requires restarting the DB System. Be sure that your applications are paused before executing the change.

Before you begin…

It is always a best practice to take a manual backup of any DB System before making any changes. Changes to variables and configurations are excellent triggers for taking a backup. Do that before you proceed any further to return the DB System to a known state should something go wrong.

Copy an Existing Configuration

The first step is to view the details page for the DB System you want to enable the GIPK feature (set the variable to ON). Locate the DB system configuration section on the details page and click the link next to Configuration, as shown in Figure 1.

Figure 1. DB system configuration section (DB System details page)

When you click on the link, you will see a new overlay popup with a link labeled View configuration details. Click that link to open the MySQL configuration details page.

Figure 2. View configuration details

On the MySQL configuration details page, notice the configuration is associated with a specific shape. In this example, the shape is MySQL.VM.Standard.E4.4.64GB.Standalone. Scroll down to the bottom to view the variables and their settings associated with the shape, as demonstrated in Figure 3. Notice the GIPK variable is not listed. The variable is not included in the base configuration, but we can add it in the following steps.

Figure 3. Example MySQL Configuration Variables

At the top of the page, you will see an option to change the configuration. In this case, we want to create a copy of the configuration to add and enable the GIPK variable. To do so, click the Copy configuration button shown in Figure 4.

Figure 4. Copy Configuration (Configuration details page)

On the Copy configuration page, change the name so that you can identify it as having the GIPK variable enabled, as shown in Figure 5. In this example, we appended the name, but you can use whatever name you wish.

Figure 5. Change the name of the configuration (Copy the configuration page)

Next, scroll down to the bottom of the page and notice that you can change any existing variable you wish. Click the + Another variable button to add a new variable, as shown in Figure 6.

Figure 6. Add Another Variable (Copy configuration page)

In the empty dropdown box on the left, choose sql_generate_invisible_primary_key from the list of available variables, and on the right, set it to ON, as shown in Figure 7. When the changes are made, click the Create button, which creates the new configuration.

Figure 7. Adding new variable (Copy configuration page)

The following action is to apply the modified configuration to the DB System.

Applying the Modified Configuration

You should now return to the details page of the MySQL HeatWave DB System. Here, we will edit the DB System and apply the modified configuration. Begin by clicking the Edit button at the top of the page, as shown in Figure 8.

 

Figure 8. Edit DB System (DB System details page)

On the edit DB System page, scroll down to the Configuration section and click Change configuration as shown in Figure 9.

Figure 9. Change configuration (Edit DB System details page)

On the Browse configurations for shape page, select the copied and modified configuration shown in Figure 10 and then click the Select a configuration button.

Figure 10. Choosing a new configuration (Browse configurations for shape page)

This will return you to the edit DB System details page. When ready, click the Save changes button to make the changes to the MySQL configuration. Caution! This will restart your DB System, so be sure your applications and users are in a state where a restart will not impede their usage or productivity.

Figure 11. Change the Configuration (Edit DB System details page)

Once the DB System restarts, you can verify the changes.

Verifying the Changes

To verify the changes, go back to the DB System details page, locate the DB system configuration, and verify that the new configuration name is shown in Figure 12.

Figure 12. DB system configuration section (DB System details page)

You can then click the new link (VM.Standard.E4.4.64Gb.Standalone with GIPKs) and then the link labeled View configuration details in the popup window to open the MySQL configuration details page. Scroll down to the bottom of the page to display the new variable added, as shown in Figure 13.

Figure 13. Example MySQL Configuration Variables (with new variable added and enabled)

That’s it! Future tables created without primary keys will now have invisible primary keys generated automatically, and you can enable features that require primary keys without encountering an error.

Limitations

There are a few limitations to using GIPKs with MySQL on-prem and MySQL HeatWave. The following highlights two areas you may want to consider before enabling GIPKs.

Existing Data

GIPKs are an excellent option for those wanting to enable high availability for their MySQL data, but enabling this option does not affect any tables you have already created. If you have an MDS DB System with databases that contain tables without primary keys, enabling GIPKs will not alter the existing tables. You must first alter your tables to add a primary key to any tables without primary keys before attempting to enable the high availability features.

You could dump the data using a logical backup, such as using the export and import features provided through the MySQL Shell. This involves exporting the data, deleting the table(s), enabling GIPKs, and importing the data. The GIPK option will add the invisible key for you. However, that could take considerable time if your dataset is large. For more information about exporting and importing data, see the OCI MySQL HeatWave documentation.

Fortunately, you have several options that do not require reloading the data, which, depending on the size of your data, may take some time to execute but should be faster than a full dump/delete/import routine. You could add a normal primary key, but as mentioned, this will add a visible column to any queries that do not specify a column set for the result, or you could also manually add a primary key that is invisible, which would have a similar effect as using the GIPK option before importing the data. Each of these options requires using the ALTER TABLE SQL command.

You could use a statement like the following to add a (traditional, visible) primary key to a table named employee_data.

 

The results of the altered table structure are as follows.

 

Alternatively, you could use a statement like the following to add an invisible primary key to the same table. Notice that we add the INVISIBLE option to make the column invisible in the result set.

 

The results of the altered table structure are as follows. Notice that the primary key is still shown in the list of the table structure but is noted as invisible and does not appear in a SELECT * query as shown (unless explicitly listed in the column set option).

 

Once again, depending on the size of your data, one of these options or a similar plan will be required to alter your data before enabling high availability. The good news is you must do this only once for any existing tables. Once the GIPK option is enabled, any new tables without a primary key will be updated automatically to include an invisible primary key.

Replication

There are also some limitations to enabling GIPKs regarding replication. The following are brief statements concerning the use of GIPKs in replication configurations. If either of these conditions meets your MySQL installation(s), test the use of GIPKs before enabling them in a production environment to avoid errors or data divergence.

You must ensure your tables with GIPKs on the replica match the source. In some conditions, replicas with extra right-most columns may require additional configuration to use GIPKs correctly.

Support for GIPKs among the source and replica(s) versions should match. Suppose the source includes support for invisible columns and has a GIPK, but the replica does not support invisible columns. In that case, the replica may not detect the GIPK and either stop with an error or may not apply the changes correctly.

Using GIPKs on replicas is not recommended. If a source table does not have GIPKs and there are two replicas that both have the variable replica_generate_invisible_primary_key=ON, then the two replicas will diverge from each other since they may generate different GIPK values for a row.

Conclusion

GIPKs can help you save from issues enabling newer features in MySQL HeatWave because tables you may create once GIPKs are enabled will have primary keys. Thus, the DB System will be eligible to enable features that require primary keys, such as high availability. The most significant benefit for those with applications that rely on existing tables without primary keys is that the generated primary key is not included in result sets (unless explicitly requested). This feature is an excellent example of how Oracle has the diverse needs of its customers at the forefront to help you grow your OCI MySQL resources to meet your business needs. And there is much more to come!

For more information about GIPKs, please see the online MySQL Reference Manual for help enabling the sql_generate_invisible_primary_key option on-prem MySQL or the OCI documentation for MySQL HeatWave for MySQL in the Oracle cloud.