Multi-database change management

This document describes how to use a multi-database change ticket in OceanBase Developer Center (ODC) to apply consistent schema or data changes to multiple databases across different environments within the same project.

Background information

In practice, applications are often deployed across multiple environments and regions. Typical multi-environment configurations include the following:

• A single business system has a lifecycle that includes development, testing, and production stages. This requires distinct environments, such as development, testing, staging, and production environments.

• A production environment for the same business may have multiple deployment nodes in different regions for geographic distribution or disaster recovery. For example, data centers in Hangzhou, Shanghai, and Beijing might each host all or part of the production system.

To maintain consistent database object structures across all environments, you must ensure that the initial setup is identical and all subsequent schema changes are applied uniformly. Without a standardized process, errors can easily be introduced, which can create significant risks.

The multi-database change management feature in ODC reduces the risks associated with changing multiple environments. It helps you easily maintain consistent database structures across all environments.

ODC lets you run SQL changes on multiple databases from the same project across different environments. You can run these changes in a sequential or parallel orchestration.

• Sequential execution: Databases are changed one by one based on a set priority. The execution status is reported after each database change. This is similar to a typical development workflow: running changes in the testing environment, then the staging environment, and finally the production environment.

• Parallel execution: You can run changes in batches. You can also include sequential steps within the parallel execution.

This document provides an example of how to create a multi-database change task in ODC to add the `employee` table to the `test` and `user_ro` databases.

Important notes

• The databases to be changed must be in the same project.

• You can change from 2 to 100 databases in a single task.

• Each database in a multi-database change task must be unique.

• You cannot stop a task while it is running. In automatic mode, the task cannot be stopped. In manual mode, you can stop the task between execution batches, but not while a batch is being executed.

Create a multi-database change task

1. In Projects or Tickets, click Multi-database Change.

   1. Create a multi-database change task from the Projects page.

   2. Create a multi-database change task from the Tickets page.

2. On the Create Multi-database Change Ticket page, enter the following information.

   Item	Description
   Project	Select the project that you want to modify.
   Database	Select the databases to change and set the execution order. Databases in different nodes are changed sequentially. Databases in the same node are changed at the same time. You can save the database configuration as a template for quick setup later.
   SQL Content	Select SQL Entry or Upload Attachment: SQL Entry: This is the default method. Enter the SQL script directly in the editor. Note The SQL Entry editor supports a maximum of 500,000 characters. If your script is larger, upload it as an attachment. Click the IN Value Conversion button to convert a list of copied values into an in('A','B') format. The column value separator is a line feed. The row value separator is a space or a tab. Upload Attachment: After you select this option, click the file area to open your file explorer and select files, or drag files directly into the area to upload them. Note You can upload up to 500 files. The total file size cannot exceed 256 MB. By default, attachments are sorted by upload time. You can drag them to change the order. The task executes the files in the specified order. Only SQL files with the .sql extension are supported.
   SQL Check	Click this button to check the SQL syntax.
   Rollback Plan	Specify a Rollback Plan. You can enter the rollback SQL script or upload it as an attachment. For more information, see the SQL Content section. Note Check Generate backup and rollback plan to automatically generate rollback SQL for UPDATE and DELETE statements. In OceanBase MySQL mode and MySQL mode, this feature only generates rollback SQL for UPDATE or DELETE operations on tables that have a primary key or a unique key. The automatic generation of a rollback plan supports a single change SQL statement that affects up to 100,000 rows. The total number of affected rows for a multi-database change cannot exceed 1,000,000.
   SQL Execution Settings	Delimiter: Set the statement delimiter. You can use a comma ,, a semicolon ;, or a colon :. Query result limit: Set a limit for the number of query results, from 1 to 1,000,000. Execution timeout: In the Execution timeout text box, enter a value in hours to specify the statement execution timeout. The default timeout is 48 hours, and the maximum is 480 hours. SQL execution handling: Choose to either stop execution or ignore errors and continue.
   Task Settings	Choose between automatic and manual execution.
   Description	In the Description text box, you can enter a description of up to 200 characters. This field is optional.

3. Click Create to create the multi-database change task.

4. After the task is created, you can view it in the list under Tickets > Multi-database Change.

View a multi-database change task

Task Information

1. In the multi-database change list under Tickets, click View in the Actions column.

2. In the task details panel that appears, click the Task Information tab to view details such as the databases, task type, risk level, and SQL content.

   Note

   Click Download Backup and Rollback Plan to download the rollback plan file that ODC automatically generates for UPDATE or DELETE statements.

Task Flow

In the task details panel, click the Task Flow tab to view the task initiation status, approval status, execution status, and execution result.

Execution Result

1. In the task details panel, click the Execution Result tab to view the records of successful and failed executions.

2. In the Actions column, click View to view the task information, execution result, rollback ticket, and task logs for a specific database.

References

• SQL editing and execution

• Result editing and export

• Risk levels, risk identification rules, and approval flows

• Database change management

• Lock-free schema change

• Shadow table synchronization

• Structure comparison