Online schema changes

To solve the problem of table locking during database schema changes, OceanBase Database Connect ODC V4.2.0 and later provides the online schema change feature. This feature can effectively avoid business blocking caused by table locking. This topic describes how to submit a online schema change ticket to implement lock-free database schema changes.

Background information

Overview

The principle of online schema changes is to create a temporary table, copy data from the original table to the temporary table, change the structure of the temporary table, copy all data to the original table, and then switch the new and old tables.

The execution process of online schema changes is as follows:

1. Create a temporary table.

2. Change the structure of the temporary table.

3. Copy all data.

4. Synchronize incremental data.

5. Verify data consistency.

6. Kill the session.

7. Switch the new and old tables.

Workflow of online schema change tickets

1. A user submits a online schema change ticket.

2. Precheck the SQL statements entered by the user.

3. Approve the task process.

4. Execute the online schema change task.

Supported change operations

Considerations

• Ensure sufficient disk space in the database.

• The table must have a primary key or non-null unique key and must not have a foreign key.

• If the current table is executing a online schema change, it cannot execute other DDL operations, or the task will fail.

• The length of a table name in OceanBase Database in MySQL compatible mode must not exceed 54 characters.

• When you connect to an OceanBase Community Edition database in the Object-Relational Developer (ODC) console, the data source configuration information must include the cluster name and the SYS tenant account.

• By default, the system locks the user and closes the session to prevent data writes during table name switching. In some versions (OceanBase Database V4.2.5 and later, but earlier than V4.3.0, in MySQL compatible mode), data writes can be prevented by configuring the Lock Table option. For more information, see the Lock table section in Create a online schema change task.

• If another DDL change is initiated for the table during a online schema change, the task will fail.

• During a single online schema change, adding columns and dropping columns cannot be performed at the same time. If needed, perform these changes in batches.

Create a online schema change task

The example below uses OceanBase Database Community Edition and Enterprise Edition OMS as an example. The example is to change the type of the birth column in the student table to date. The example uses odc_4.2.0 as the project name, odc_test as the database name, and student as the table name.

1. (Optional) Deploy an OceanBase Community Edition cluster. If you have completed the cluster configuration, skip this step.

   Note

   When you create an OceanBase data source in OceanBase Database Enterprise Edition OMS, you must specify the cluster name. You cannot create a data source on a standalone server. For more information about how to deploy a cluster, see Solution 2: Deploy an OceanBase cluster.

2. Install ConfigServer. ConfigServer serves as the interface between OceanBase and OBProxy for accessing OceanBase rs_list. After you configure the address of the OceanBase ConfigServer, OceanBase automatically registers rs_list with ConfigServer.

   Note

   If you deploy a cluster by using OCP (non-OCP Express), you can skip this step. OCP automatically sets the IP address of OCP as the OceanBase ConfigServer for the cluster that is created.

3. Install OceanBase Database Enterprise Edition OMS. This example uses OceanBase Database Enterprise Edition OMS. You can obtain it from Docker. You can also choose OceanBase Database Community Edition OMS as needed and complete the configuration.

4. Deploy Web ODC.

5. Configure the metadata of ODC and enable the online schema change feature.

   a. Configure the information of ODC and Enterprise Edition OMS.

   Connect to the MetaDB of ODC and configure the URL and login authentication information of Enterprise Edition OMS. The format of oms_url is http://${oms_host}:${oms_port}, and the format of oms_authornization is username:password in Base64 encoding. After the configuration is completed, restart ODC to make the settings of Enterprise Edition OMS take effect.

   • Syntax:

     # Configure oms_url
     update config_system_configuration set `value` = '${oms_url}' where `key` = 'odc.osc.oms.url';
     
     # Configure oms_authornazition
     update config_system_configuration set `value` = '${oms_authornazition}' where  `key` = 'odc.osc.oms.authorization';
     
     # Configure oms_region, for example, cn-anhui
     update config_system_configuration set value = '${oms_region}' where `key` = 'odc.osc.oms.region'
     

   • Examples:

     # Configure oms_url, and specify the URL for accessing OMS: http://localhost:8089
     update config_system_configuration set `value` = 'http://localhost:8089' where `key` = 'odc.osc.oms.url';
     
     # Configure oms_authornazition. Assume that the username is test and the password is 123456. After `test:123456` is encoded in Base64, it becomes: dGVzdDoxMjM0NTY=
     update config_system_configuration set `value` = 'dGVzdDoxMjM0NTY=' where `key` = 'odc.osc.oms.authorization';
     
     # Configure oms_region. Assume that the region specified during the installation of OMS is cn-anhui. For more information, see the OMS installation process.
     update config_system_configuration set value = 'cn-anhui' where `key` = 'odc.osc.oms.region'
     

   b. Enable the online schema change feature. The online schema change feature is available for OceanBase Database on Alibaba Cloud but not for OceanBase Database Community Edition. By default, the online schema change feature is disabled for OceanBase Database Community Edition. You need to configure ODC MetaDB and execute the corresponding SQL statement to enable the feature.

   ```shell
   update config_system_configuration set value = 'true' where `key` = 'odc.features.task.osc.enabled';
   ```
   

6. Create and synchronize a data source in ODC.

   Notice

   For OceanBase Database Community Edition, when you use ODC to create a data source, you must configure the sys tenant.
   

7. Create a project and add a database.

8. In the SQL window, edit an SQL statement to create a student table in the odc_test database.

   

   CREATE TABLE `odc_test`.`student` (
   `id` int(11) COMMENT 'student id' NOT NULL,
   `name` varchar(120) COMMENT 'student name' NULL,
   `birthday` datetime(0) COMMENT 'student birthday' NOT NULL,
   `province` varchar(120) COMMENT 'student province' NULL,
   `city` varchar(120) COMMENT 'student city' NULL,
   `mobile_phone` int COMMENT 'student mobile_phone' NULL,
   `email` varchar(120) COMMENT 'student email' NULL,
   `create_time` datetime(0) COMMENT 'update time' NULL,
   CONSTRAINT `cons_id` PRIMARY KEY (`id`)
   ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci ;
   

9. Lock accounts or tables.

   • For OceanBase Database of a version earlier than V4.2.5 MySQL, the lock table feature is not supported when you change the schema of a database table. You need to specify the account to be locked. Before renaming the tables (Rename), ODC will execute the alter table user lock account command on the specified account and close the session to terminate all processes on the locked account.

     

   • For OceanBase Database V4.2.5 MySQL and later versions before V4.3.0, the lock table feature is not supported when you change the schema of a database table. You need to make sure that the OBProxy version is V4.3.1 or later, and perform the following operations before creating a online schema change task to lock tables. Otherwise, the locking will fail.

     1. Modify the OBProxy parameters.

        alter proxyconfig set proxy_id=1;
        alter proxyconfig set client_session_id_version=2;
        alter proxyconfig set enable_single_leader_node_routing = false;
        

     2. Modify the tenant-level parameters.

        alter system set enable_lock_priority=true;
        

     3. Modify the range of OceanBase Database versions that support the lock table feature.

        By default, the lock table feature supports OceanBase Database versions from V4.2.5 to V4.2.9. To change the range of OceanBase Database versions that support the lock table feature, connect to ODC MetaDB and execute the following SQL statement.

        insert into config_system_configuration( key, application, profile, label, description, value) values('odc.osc.support-lock-table-ob-version-json', 'odc', 'default', 'master', 'control odc enable lock table version, default is ["4\\.2\\.[5-9].* "]', '$Expected version range');
        

        Here, '$Expected version range' must be a JSON array whose elements follow the Java wildcard rules. For example, to change the OceanBase Database version range to V4.2.5 ~ V4.2.9 and V4.3.5, the value is ["4\\\\.2\\\\.[5-9].*", "4.3.5.*"].

     4. Verify whether the locking is successful. Execute the lock table command in ODC to lock a temporary table. Then, try to insert data into the temporary table by using a new connection. If the data insertion is blocked, the locking is effective.

        create table test_table(id int not null primary key);
        session 1:                                      session 2
        lock table test_table;
                                                        insert into test_table values(1);
                                                        Blocked...
        unlock table test_table;  
                                                        Insert succeeded
        

10. Create a online schema change task.

    1. On the Ticket tab, choose Online Schema Change > Create Online Schema Change.

    2. On the Create Online Schema Change page, configure the following parameters.

       

       Parameter	Description
       Database	The database whose schema is to be changed.
       Locked User	If you specify a user account to be locked, ODC locks the specified database account and closes all sessions corresponding to the account before renaming the tables (Rename). This ensures data consistency during table renaming and minimizes impact on your business as much as possible. If you do not specify any account, ODC will not lock any account or close any session. You must ensure data consistency during table renaming. Note During the execution of a online schema change task, the user who executes the task cannot modify the tables involved in the task.
       Change Definition	CREATE TABLE: For syntaxes not supported by OceanBase Database, you must use the CREATE TABLE statement. ALTER TABLE: For OceanBase Database V4.0.0 and later, you can use the ALTER TABLE statement in the ALTER TABLE OFFLINE mode.
       SQL Content	The SQL script.
       Table Switch Settings	After data consistency is ensured, the original table is switched with the destination table. Lock Table Timeout: Tables are locked during table switching. If the switching is not completed within the specified time, the execution may fail. Number of Retries on Failure: If the switching is not completed after the specified time, automatic retries will be performed. Source Table Cleanup Strategy After Completion: You can choose Renaming tasks are not handled. to rename the source table and keep it, or Delete Now to delete the source table after a online schema change task is completed.
       Task Settings	You can set the task execution mode to Execute Immediately, Execute On Schedule, or Manual Execution. The online schema change tool provides two ways to handle task errors: Abort Task: This is the default setting. The script execution is stopped when an error occurs. Ignore Error and Continue: With this setting, the script execution skips error statements and continues. You can choose Automatic Switch or Manual Switch to enable the online schema change feature for table name switching.
       Description	You can enter up to 200 characters in the Description field as a task description. This parameter is optional.

    3. Click Create.

    4. After the task is generated, you can find the task information on the Ticket page or in the Online Schema Change list.

       

View a online schema change task

View task information

1. In the online schema change task list, click View in the operation column of the task you want to view.

2. In the task details panel that appears, click the Basic Information tab to view information such as the database to which the task belongs, task type, risk level, and SQL content.

3. Click Initiate Again to reinitiate the online schema change task.

View the task process

In the task details panel, click the Workflow tab to view the status of task initiation, precheck, approval, execution, and execution result.

View the execution records

In the task details panel, click the Execution Records tab to view the DDL statements of the new table and source table and the task execution progress.

View task logs

In the task details panel, click the Logs tab to view all logs and alert logs of the task.