Managing a Configuration

You can edit a custom configuration, copy a configuration, update the configuration of a DB system, or compare two configurations.

Editing a Custom Configuration

You can edit the name, description, and tags of a custom configuration. You cannot edit a default configuration.

Using the Console

Use the Console to edit a custom configuration.

  1. Open the navigation menu and select Databases. Under HeatWave MySQL, click Configurations.
  2. Locate the custom configuration you want to edit and do one of the following:
    • Click the Actions menu and select Edit to open the Edit MySQL Configuration dialog box.
    • Click the name of the configuration to open the Configuration Details page and click Edit.
  3. In the Edit Configuration dialog box, edit the configuration name, description, and tags.
  4. Click Save Changes.

Copying a Configuration

You can copy either an existing custom configuration or a default configuration, and create a new custom configuration.

The new custom configuration inherits the shape from the copied configuration. If you want to create a custom configuration that supports high availability DB systems, you must copy from an existing configuration that supports high availability. For example, if you want to create a custom configuration for the MySQL.8 shape that supports high availability, you can copy from the MySQL.8.HA configuration.

Use the Console of a command-line interface to copy a configuration.

Using the Console

Use the Console to copy a configuration.

  1. Open the navigation menu and select Databases. Under HeatWave MySQL, click Configurations.
  2. Click the Actions menu on the same line as the configuration you want to copy, and select Copy Configuration.
  3. In the Copy Configuration panel, in the Configuration Information section, provide the following details:
    • Name: Specify a user-friendly display name for the configuration. The name does not need to be unique. An Oracle Cloud Identifier (OCID) uniquely identifies the configuration.
    • Description: (Optional) Specify a description of the configuration.
    • Configure placement and hardware:
      • Select a Compartment: (Optional) If you want to create the configuration in a different compartment, select the required compartment.
      • Select a Shape: You cannot change the shape associated with the configuration.
    • Show Advanced Options:
      • Tags: (Optional) Specify a Tag Namespace, Tag Key, and Tag Value.
  4. Click Next.
  5. In the Variables Information section, provide the following details:
    • Initialization Variables: (Optional) Enable Ignore case in table and schema names to disable case sensitivity. See Initialization Variables.
    • User Variables: (Optional) Select the variable you want to add in Variable Names drop down list, and specify the Variable Value. To add another variable to your configuration, click +Another Variable .

      Every configuration has a set of default user variables. If you do not select any variable, Oracle adds the default variables to your Configuration. See Configuration Variables. See User Variables.

    • System Variables: You cannot edit the system variables. See System Variables.
  6. Click Copy.

Using the CLI

Use the command-line interface to copy a configuration.

This task requires the following:
  • The Oracle Cloud Identifier (OCID) of your compartment.
  • The OCID of an existing configuration to copy from.
  • A policy that permits you to create configurations in the compartment or tenancy.
  • If you are copying from a custom configuration, a policy that permits you to read the custom configuration.
  • A properly configured CLI installation and the requisite SSH keys. See Command Line Interface.
Copy a configuration to create a new custom configuration using the CLI:
  1. Open a command prompt and run the following command:
    oci mysql configuration create 
         --compartment-id <CompartmentOCID>
         --parent-configuration-id <ConfigurationOCID>
         --description <Description>
         --display-name <DisplayName> 
         --shape-name <ShapeName> 
         --variables <JSONObject>
    • compartment-id: Specify the OCID of the compartment used to create the new configuration.
    • parent-configuration-id: Specify the OCID of the configuration to be copied from.
    • description: (Optional) Specify a brief description of the configuration.
    • display-name: (Optional) Specify the display name of the configuration. If you do not define a display name, Oracle generates one for you in the mysqlconfigurationYYYYMMDDHHMMSS format.
    • shape-name: Specify the name of the shape. For example, MySQL.4.
      Note

      The shape must be the same as the shape of the parent configuration.
    • variables: (Optional) Specify the variables and their corresponding values as key value pairs in a JSON object, for example:
      { "autocommit": true, "connectTimeout": 20 } 
If the command validates and runs successfully, you get a response similar to the following, which summarizes the request and creates the configuration:
{
  "data": {
    "compartment-id": "ocid1.compartment.oc1..longAlphanumericString",
    "defined-tags": {
      "Oracle-Tags": {
        "CreatedBy": "userName",
        "CreatedOn": "2024-07-11T00:51:53.856Z"
      }
    },
    "description": "This is a user defined descrption",
    "display-name": "ConfigName",
    "freeform-tags": {},
    "id": "ocid1.mysqlconfiguration.oc1.longAlphanumericString",
    "init-variables": {
      "lower-case-table-names": "CASE_SENSITIVE"
    },
    "lifecycle-state": "ACTIVE",
    "parent-configuration-id": "ocid1.mysqlconfiguration.oc1.longAlphanumericString",
    "shape-name": "MySQL.2",
    "time-created": "2024-07-11T00:51:53.866000+00:00",
    "time-updated": "2024-07-11T00:51:53.866000+00:00",
    "type": "CUSTOM",
    "variables": {
      "autocommit": true,
      "big-tables": null,
      "binlog-expire-logs-seconds": 3600,
      "binlog-row-metadata": null,
      "binlog-row-value-options": "PARTIAL_JSON",
      "binlog-transaction-compression": null,
      "completion-type": null,
      "connect-timeout": 20,
      "connection-memory-chunk-size": null,
      "connection-memory-limit": null,
      "cte-max-recursion-depth": null,
      "default-authentication-plugin": null,
      "foreign-key-checks": null,
      "generated-random-password-length": null,
      "global-connection-memory-limit": null,
      "global-connection-memory-tracking": null,
      "group-replication-consistency": "BEFORE_ON_PRIMARY_FAILOVER",
      "information-schema-stats-expiry": null,
      "innodb-buffer-pool-dump-pct": null,
      "innodb-buffer-pool-instances": 4,
      "innodb-buffer-pool-size": 9126805504,
      "innodb-ddl-buffer-size": null,
      "innodb-ddl-threads": null,
      "innodb-ft-enable-stopword": null,
      "innodb-ft-max-token-size": null,
      "innodb-ft-min-token-size": null,
      "innodb-ft-num-word-optimize": null,
      "innodb-ft-result-cache-limit": 33554432,
      "innodb-ft-server-stopword-table": null,
      "innodb-lock-wait-timeout": null,
      "innodb-log-writer-threads": null,
      "innodb-max-purge-lag": null,
      "innodb-max-purge-lag-delay": 300000,
      "innodb-stats-persistent-sample-pages": null,
      "innodb-stats-transient-sample-pages": null,
      "interactive-timeout": null,
      "local-infile": true,
      "mandatory-roles": "public",
      "max-allowed-packet": null,
      "max-binlog-cache-size": 4294967296,
      "max-connect-errors": null,
      "max-connections": 1000,
      "max-execution-time": null,
      "max-heap-table-size": null,
      "max-prepared-stmt-count": null,
      "mysql-firewall-mode": null,
      "mysql-zstd-default-compression-level": null,
      "mysqlx-connect-timeout": null,
      "mysqlx-deflate-default-compression-level": null,
      "mysqlx-deflate-max-client-compression-level": null,
      "mysqlx-document-id-unique-prefix": null,
      "mysqlx-enable-hello-notice": null,
      "mysqlx-idle-worker-thread-timeout": null,
      "mysqlx-interactive-timeout": null,
      "mysqlx-lz4-default-compression-level": null,
      "mysqlx-lz4-max-client-compression-level": null,
      "mysqlx-max-allowed-packet": null,
      "mysqlx-min-worker-threads": null,
      "mysqlx-read-timeout": null,
      "mysqlx-wait-timeout": null,
      "mysqlx-write-timeout": null,
      "mysqlx-zstd-default-compression-level": null,
      "mysqlx-zstd-max-client-compression-level": null,
      "net-read-timeout": null,
      "net-write-timeout": null,
      "parser-max-mem-size": null,
      "query-alloc-block-size": null,
      "query-prealloc-size": null,
      "regexp-time-limit": null,
      "sort-buffer-size": null,
      "sql-mode": null,
      "sql-require-primary-key": true,
      "sql-warnings": null,
      "thread-pool-dedicated-listeners": null,
      "thread-pool-max-transactions-limit": null,
      "time-zone": "UTC",
      "tmp-table-size": null,
      "transaction-isolation": null,
      "wait-timeout": null
    }
  },
  "etag": "longAlphanumericString"
}

Updating the Configuration of a DB System

You can update the configuration of a DB system to an existing configuration or a newly created configuration to change the variable settings of the DB system.

If all the changed variables are dynamic, the DB system does not require a restart.
  • A standalone DB system has no downtime.
  • A high availability DB system incurs a brief downtime and a change in the current primary placement as it performs a rolling update. If it has an active HeatWave cluster attached to it, the data in the HeatWave cluster is reloaded automatically.
If one or more non-dynamic variables are changed, the DB system needs to be restarted.
  • A standalone DB system incurs a downtime during the restart.
  • A high availability DB system incurs a brief downtime and a change in the current primary placement as it performs a rolling restart. The rolling restart shortens the downtime and causes a change in the current primary placement of the high availability DB system.
  • If the DB system has an active HeatWave cluster attached to it, the data in the HeatWave cluster is reloaded automatically.
Use the Console of a command-line interface to update the configuration of a DB system:

Using the Console

Use the Console to update the configuration of a DB system.

Do the following to update the configuration of a DB system:

  1. (Optional) Create a new configuration. See Creating a Custom Configuration.
  2. Edit the DB system to update the configuration. See Editing a DB System.
    Note

    You cannot update the configuration if there is an active channel connected to the DB system. Stop the channel to update the configuration.

Using the CLI

Use the command-line interface to update the configuration of a DB system.

This task requires the following:
  • A DB system Oracle Cloud Identifier (OCID).
  • A MySQL configuration OCID.
  • A properly configured CLI installation and the requisite SSH keys. See Command Line Interface.
  1. (Optional) Create a new configuration. See Creating a Custom Configuration.
  2. Open a command prompt and run the following command:
    oci mysql db-system update 
         --db-system-id <DBSystemOCID>
         --configuration-id <ConfigurationOCID>
    • db-system-id: Specify the OCID of the DB system you want to update.
    • configuration-id: Specify the OCID of the configuration with which you want to replace the existing configuration.

Comparing Configurations

Compare the shapes, initialization variables, and user variables of two configurations.

Using the Console

Use the Console to compare two configurations.

  1. Open the navigation menu and select Databases. Under HeatWave MySQL, click Configurations.
  2. Select the check box of the two configurations you want to compare, click the Actions button at the top of the page, and then click Compare.
  3. In the Compare configurations panel, click either of the following option buttons:
    • Show configuration differences: Displays the differences in the shapes, initialization variables, and user variables.
    • Show all configuration information: Displays all information related to shapes, initialization variables, and user variables of the two configurations.
  4. Click Close.