Online schema changes

Online schema changes help minimize disruption to applications caused by table locks during schema changes. This topic describes how to submit a ticket to perform an online schema change.

Background information

Principles

The steps for processing a single table are as follows:

1. Create a shadow table. Based on the schema change requirements, a shadow table is created. The schema of the shadow table is the final schema that you expect for the table.

2. Synchronize data. The data in the original table is synchronized to the shadow table by using full and incremental synchronization.

3. Perform data consistency checks. During incremental synchronization, data consistency checks are performed at regular intervals.

4. Suspend the business. When the data in the original table and the shadow table are almost consistent, the business is suspended. OceanBase Database provides two suspension methods: locking a user and locking a table. The time for locking a user and a table is determined by the database kernel. The program does not impose any restrictions. If the user session is not killed within 2 seconds after the lock user operation succeeds, the lock user operation fails.

5. Synchronize data. After the business is suspended, incremental synchronization and data consistency checks continue. If the data is not synchronized within 25 seconds, the task fails.

6. Rename the original table and the shadow table. The shadow table becomes the new business table for business production.

Procedure

1. Submit an online schema change task.

2. Perform a precheck on the SQL statements that you enter.

3. Approve the task.

4. Execute the online schema change task.

Supported operations

Considerations

• Make sure that the disk space of the database is sufficient.

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

• When an online schema change task is being executed, other DDL operations on the current table cause the task to fail.

• In the MySQL compatible mode of OceanBase Database, the name of a table cannot exceed 54 characters.

• When ODC connects to OceanBase Database Community Edition, the cluster name and the SYS tenant account must be specified in the data source configuration.

• By default, only the lock user method is provided for the write-prohibited strategy for renaming tables. Some OceanBase Database versions also provide the lock table method.

• When using the lock user method, note the following limitations:

  • The database account used to perform the change must have permission to lock users, typically the CREATE USER privilege. The specific required privilege depends on the OceanBase Database version.

  • The database account used to perform the change cannot be in the lock user list. Otherwise, ODC will also close the sessions of this account, causing session termination to fail during the table switch.

• During a single online schema change task, you cannot add a column and drop a column at the same time. If you need to perform these operations, do so in batches.

Create an online schema change task

This example uses OceanBase Database Community Edition and OMS Enterprise Edition to change the data type of the birthday column in the student table to date. The project is named odc_4.2.0, and the database is named odc_test.

1. (Optional) Deploy an OceanBase Database Community Edition cluster. Skip this step if you have already configured the cluster.

   Note

   When you create an OceanBase data source in OMS Enterprise Edition, you must specify the cluster name. You cannot create a data source for a standalone server. For cluster deployment instructions, see Quick start with OceanBase Database Community Edition.

2. Install ConfigServer. ConfigServer provides an interface for OceanBase Database and OBProxy to access the OceanBase rs_list. After you configure the ConfigServer address, OceanBase Database automatically registers its rs_list with ConfigServer.

   Note

   Skip this step if you deploy the cluster by using OCP. OCP automatically configures its own address as the ConfigServer address for the clusters it creates.

3. OMS deployment types. This example uses OMS Enterprise Edition, which you can obtain through Docker. You can also use OMS Community Edition and configure it as needed.

4. Deploy Web ODC.

5. Configure ODC metadata and enable online schema changes.

   a. Configure OMS Enterprise Edition information in ODC.

   Connect to the ODC metadata database and configure the URL and authentication information for OMS Enterprise Edition. Set oms_url to http://${oms_host}:${oms_port} and oms_authornazition to the Base64-encoded value of username:password. Restart ODC for the configuration to 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 the oms_region, for example, cn-anhui.
     update config_system_configuration set value = '${oms_region}' where `key` = 'odc.osc.oms.region'
     

   • Example:

     #Configure oms_url by entering the OMS access address: http://localhost:8089
     update config_system_configuration set `value` = 'http://localhost:8089' where `key` = 'odc.osc.oms.url';
     
     #Configure oms_authornazition. For example, if the account is test and the password is 123456, then the Base64-encoded value of `test:123456` is: dGVzdDoxMjM0NTY=
     update config_system_configuration set `value` = 'dGVzdDoxMjM0NTY=' where `key` = 'odc.osc.oms.authorization';
     
     #Configure the oms_region. For example, if the region entered during OMS installation was cn-anhui, see the OMS installation process for details.
     update config_system_configuration set value = 'cn-anhui' where `key` = 'odc.osc.oms.region'
     

   b. Enable online schema changes. This feature is already available in Alibaba Cloud OceanBase Database. For OceanBase Database Community Edition, the feature is disabled in ODC by default. To enable it, connect to the ODC metadata database and execute the following SQL statement.

   ```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 creating a data source in ODC, you need to configure the sys tenant.
   

7. Create a project and add a database to the project.

8. In the SQL window, execute the following SQL statement to create the 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 the account/table.

   • For OceanBase Database versions earlier than V4.2.5, table locking is not supported when changing the database table structure. You need to specify the account to lock. During the switch between the old and new tables (Rename), ODC executes the alter table user lock account command for the locked account and closes the session to terminate all processes under the locked account.

     

   • For OceanBase Database V4.2.5 and later but earlier than V4.3.0, schema changes use table locking instead of account locking. Make sure that OBProxy is V4.3.1 or later, and complete the following steps before running the online schema change task. Otherwise, table 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 parameter.

        alter system set enable_lock_priority=true;
        

     3. Modify the supported OceanBase MySQL version range for table locking.

        By default, table locking is supported for OceanBase Database in MySQL mode, V4.2.5 to V4.2.9. To change the supported version range, connect to the ODC metadata database 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].* "]', '$desired_version_range');
        

        '$desired_version_range' is a JSON array whose elements follow Java wildcard matching rules. For example, to modify the OceanBase MySQL version range to V4.2.5 to V4.2.9 and V4.3.5, set the value to ["4\\\\.2\\\\.[5-9].*", "4.3.5.*"].

     4. Verify that table locking works. In ODC, execute the lock table command to lock a temporary table. Then, use a separate connection to insert data into the table. If the insert operation is blocked, the lock has taken effect.

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

10. Create an online schema change task.

    1. On the Projects > All Projects page, click the target project name.

       

    2. On the Tickets tab, click Online Schema Change > Create Online Schema Change.

       

    3. On the Create Online Schema Change page, specify the following information and click Create.

       

       Parameter	Description
       Database	The database to be changed.
       Lock User	If you specify a user to lock, ODC locks the specified database account before the table name is switched and closes all sessions associated with that account to ensure data consistency during the switch while minimizing the impact on business. If no account is specified, ODC will not lock any accounts or close sessions. You must ensure data consistency during the switch yourself. Notice During the execution of an online schema change task, the user who executes the task cannot perform change operations on the tables related to the online schema change task. The database account used to execute the task must have permission to lock users, typically the CREATE USER privilege. The specific required privilege depends on the OceanBase Database version. Do not include the database account used to execute the task in the list of users to lock. Otherwise, ODC will also close the sessions of this account, causing session termination to fail during the table switch.
       Change Definition	CREATE TABLE: You must use the CREATE statement for syntax that is not supported by OceanBase Database. ALTER TABLE: In OceanBase Database V4.0.0 and later, you can use the ALTER statement in the ALTER TABLE OFFLINE mode.
       SQL Content	Enter the SQL script in the editing area.
       Switch Table Settings	After the data is consistent, the original table is switched to the target table. Lock Timeout Period: The table is locked during the switch. The task may fail if the switch is not completed before the lock times out. Retry Attempts on Lock Failure: If the lock times out before the table switch is complete, the system can retry the switch automatically. Cleanup Strategy for Source Table Upon Completion: Select Rename Only to rename the source table without deleting it. Select Delete Now to delete the source table after the online schema change is completed.
       Task Settings	Execution Mode: Select Manual Execution/Immediate Execution/Scheduled Execution. Task Error Handling: Stop Task: This is the default option. Script execution stops if an error occurs. Ignore Errors and Continue Task: If an error occurs, the statement that caused the error is skipped and script execution continues. Table Name Switch: Select Automatic Switch/Manual Switch to switch the table name.
       Description	You can enter a description of up to 200 characters in the Description text box. This parameter is optional.

    4. After the task is generated, you can view the task information in the Tickets > Online Schema Change list.

       

View an online schema change task

1. Go to Tickets > Online Schema Change. In the online schema change task list, click the name of the target task to view its details. For additional task operations, click ··· in the Actions column.

   • Execute: Manually start a task whose execution mode is manual.
   • Abort: Stop a periodic or scheduled task. To stop multiple tasks, select them and use the batch operation button above the list.
   • Disable/Enable: Temporarily stop a periodic or scheduled task by disabling it. Re-enable the task to resume execution.
   • Edit: Update the content of a disabled task.
   • Delete: Delete a completed or aborted task.
   • View: View the task details.
   • Clone: Open the task creation page with the same configuration as the selected task.
   • Share: Copy the URL of the task.

   

2. In the task details panel that appears, the Basic Info tab is displayed by default. You can view information such as the task type, database, variable configuration, and execution mode.

   

3. In the task details panel, click the Task Process tab to view information such as the task initiation status, approval status, execution status, and execution result.

   

4. In the task details panel, click the Execution Results tab to view the task execution records.

   

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

Related documentation

Deploy Web ODC