Manage database changes

This topic introduces how to create and view database change tasks.

Background information

ODC allows you to make data changes to the database. You can initiate database change tasks to run time-consuming SQL statements, avoiding statement execution timeouts.

Create a database change task

Assume that you want to change the value of name in the employee table in the odc_test database from xiaoguo to xiaofeng.

1. Choose Project > All Projects, and click the project name odc_4.2.0.

2. On the Ticket tab, choose Database Change > Create Database Change Task.

3. On the Create Database Change Task page, specify the following parameters.

   Parameter	Description
   Database	The database to be changed.
   SQL Content	Select SQL Entry or Upload File. SQL Entry: This is the default way to specify SQL content. You can directly write the SQL script in the editing area. Note The maximum size of the SQL statements that can be entered in the SQL window is limited to 500,000 characters. If the SQL script to be executed has more than 500,000 characters, you can upload it in the form of a *.sql file. You can click IN Value Conversion to convert the batch copied data into the in ('A','B') format. Column values are separated with line breaks. Row values are separated with spaces or tabs. Upload File: Click the file pool to go to the file explorer and select the file to be imported. You can also directly drag the file to the file pool to upload it. Note You can upload up to 500 files. The total file size cannot exceed 256 MB. By default, files are sorted by the order in which they were uploaded. You can drag and drop them to change the order. Tasks are executed in the file order. Only SQL files suffixed with .sql are supported.
   Rollback Plan	Specify Rollback Plan. You can write an SQL script or upload a file to specify the rollback plan. For more information, see the description of SQL Content. Note Check the option Generate Backup Rollback Plan, which supports automatically generating backup rollback SQL for UPDATE/DELETE statements. In the OceanBase MySQL mode, automatic backup rollback plans are only generated for tables that have a primary key or unique key when performing UPDATE/DELETE operations. The automatic backup rollback plan supports a maximum of 100,000 rows for the total affected rows by single change SQL, and the total affected rows by database changes should not exceed 1,000,000 rows.
   Delimiter	The delimiters are supported.
   Query Result Limit	You can set the maximum number of rows allowed in the query result. Value range: 1 to 1,000,000.
   Task Settings	The database change tool supports two methods to handle task errors: Abort Task: This is the default option. When you select this option, the task is aborted if an error occurs when you run the script. Ignore Error and Continue: When you select this option, the system skips the statement where an error occurs and continues to execute other statements in the script. Select Execute immediately or Timed execution to set the task execution mode.
   Execution Timeout Value	Specify the value in the Execution Timeout Value field in hours. Default value: 48. Maximum value: 480.
   Task Description	You can enter a description of no more than 200 characters in Task Description. This field is optional.

4. Click Create. The database change task is created.

5. After the task is generated, you can view the task information in the Database Change list on the Ticket page.

View a database change task

Task information

1. In the database change list on the Ticket page, click View in the Actions column.

2. In the Task Details panel, click the Task Information tab to view the database, task type, risk level, SQL content, and rollback content.

   Note

   You can click Download Backup Rollback Plan, which supports to download the automatically generated backup rollback plan files for UPDATE/DELETE statements.

3. You can click Initiate Again to initiate the database change task again, or click Roll Back to roll back the data.

Task process

In the Task Details panel, click the Task Process tab to view the task status, approval status, execution status, and execution result of the task.

Execution results

In the Task Details panel, click the Results tab to view the execution results.

Roll back a task

In the Task Details panel, click Roll Back Task.

Task logs

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

References

• Edit and execute SQL statements

• PL compilation and debugging

• Command-line window

• Edit and export results

• Analyze SQL statements

• Generate mock data

• Manage partitions

• Synchronize shadow tables

• Change lock-free structures