Oracle Cloud Infrastructure Documentation

Prerequisites

High availability uses MySQL Group Replication, which requires that each table has a defined primary key. If you try to create a table without a primary key in a high availabilty DB system, it fails.

If you are migrating data to MySQL HeatWave and have not defined primary keys on your tables, you must add them. Check tables for primary keys and add keys to the tables that do not have them:

  1. Checking Tables for Primary Keys Using a Command-Line Client
  2. Add primary keys using any of the following methods:
    • Using invisible column: See Manually Adding Primary Keys Using a Command-Line Client.
      Note

      Using invisible columns to add primary keys is a low-impact way to update your existing data for use with a high availablility DB system. It is transparent to your applications, the new column remains hidden from SELECT queries, enabling your applications to continue working as previously.
    • Using MySQL Shell dump utility: See create_invisible_pks in MySQL Shell Dump Utility.
    • Using MySQL Shell load utility: See createInvisiblePKs in MySQL Shell Load Utility.
      Note

      To use MySQL Shell dump and load utility, use MySQL Shell version 8.0.30 or higher.
    • Using sql_generate_invisible_primary_key variable: Set the variable to ON to add primary keys to new tables that you create. Setting the variable to ON does not add primary keys to already existing tables. See Generating Invisible Primary Keys.

Checking Tables for Primary Keys Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to check your tables for primary keys and list the ones that do not have primary keys. Primary keys are a prerequisite for the group replication used by high availability.

  1. Run the following statement against the database to generate a list of tables that do not have primary keys: 
    SELECT t.table_schema, t.table_name
FROM information_schema.tables t
  LEFT JOIN (SELECT table_schema, table_name 
             FROM information_schema.statistics
             WHERE index_name = 'PRIMARY' 
             GROUP BY table_schema, table_name, index_name
             ) pks 
  ON t.table_schema = pks.table_schema AND t.table_name = pks.table_name 
WHERE pks.table_name IS NULL
  AND t.table_type = 'BASE TABLE' 
  AND t.table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');

Manually Adding Primary Keys Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to add primary keys to invisible columns.

This task requires the following:
  • MySQL version 8.0.23 or higher. Invisible columns were introduced in 8.0.23 version.
Do the following to add a primary key to an invisible column:
  1. Run a command similar to the following against the table to which you want to add the invisible column and primary key:
    ALTER TABLE <Table1> ADD <my_row_id> BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY INVISIBLE FIRST;

    The command alters the table, <Table1>, with one column, test by adding a column, <my_row_id>, which is invisible and contains the primary key for the table.