Database change management

Background information

OceanBase Developer Center (ODC) allows you to change the data in your database. You can create a database change task to run time-consuming SQL statements to avoid an execution timeout.

This topic describes how to update the name value of a target row in the employee table to user.

Considerations

• A rollback statement can be automatically generated only for the UPDATE and DELETE statements.

• A change task is allowed to affect a maximum of 1 million rows. If the number of affected rows exceeds 1 million, a rollback statement cannot be automatically generated for this change task.

• If the table contains LOB fields, a rollback statement cannot be automatically generated.

• If the table does not have a primary or unique key when the data source is an OceanBase MySQL compatible mode or a MySQL data source, a rollback statement cannot be automatically generated.

• If the SET and WHERE clauses in an UPDATE statement are the same, a rollback statement cannot be automatically generated.

• The maximum size of a rollback script that is automatically generated is 256 MB.

Create a database change task

1. Choose Projects > All Projects. On the tab that appears, click the project name.

2. On the Tickets tab, choose Database Change > Create New.

3. On the Create Database Change Ticket page, configure the following parameters and click Create.

   Parameter	Description
   Database	Select the database to which the database change task belongs.
   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 directly upload the script 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.
   SQL Check	Click this icon to check the syntax of the SQL statement.
   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 You can select Generate Backup Rollback Plan so that backup rollback SQL statements will be automatically generated for UPDATE and DELETE statements. For OceanBase Database in MySQL compatible mode or MySQL Database, a rollback SQL statement can be generated only for an UPDATE or DELETE statement executed on a table with a primary or unique key. A backup rollback SQL statement can be generated only when a database change statement affects no more than 100,000 rows and the database change task affects no more than 1 million rows.
   Delimiter	Delimiters are supported.
   Query Result Limit	You can set the maximum number of rows allowed in the query result. Value range: 1 to 1000000.
   Task Settings	SQL Retry Attempts: If you specify a value greater than 1, when the execution of an SQL statement fails, ODC will re-execute the statement in 3 minutes. The database change tool provides two error handling methods: 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 erroneous statement where an error occurs and continues to execute other statements in the script. Select Execute Now or Scheduled Execution.
   Execution Timeout Value	Specify the value in the Execution Timeout Value field in hours. Default value: 48. Maximum value: 480.
   Description	You can enter a description of no more than 200 characters in Task Description. This field is optional.

4. On the Tickets tab, view the task information in the database change task list.

View the database change task

1. Go to Tickets > Database Change. In the database change ticket list, click the name of the target ticket to view the ticket details. You can also click the ··· icon in the Actions column to perform more operations on the target ticket.

   • Execute: When the execution mode of the ticket is set to Manual, you can click this option to execute the ticket.
   • Terminate: When the execution mode of the ticket is set to Scheduled or Periodic, you can click this option to terminate the ticket.
   • Disable/Enable: When the execution mode of the ticket is set to Scheduled or Periodic, you can click this option to disable the ticket to temporarily stop its execution. After you enable the ticket, it resumes execution.
   • Edit: When the ticket is disabled, you can click this option to edit the ticket.
   • Delete: When the ticket status is Completed or Terminated, you can click this option to delete the ticket.
   • View: View the details of the ticket.
   • Clone: Copy the execution content of the ticket and quickly generate a new ticket with the same configuration.
   • Share: Copy the access address of the ticket.
   • Rollback: If the rollback option is selected for the ticket, you can click this option to quickly generate a backup rollback ticket for the UPDATE/DELETE statements.

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

   Note

   You can configure whether to allow query results to be queried and downloaded in the system settings. For more information, see System settings.

3. Click the Workflow tab to view the ticket status, approval status, execution status, and execution results.

4. Click the Result tab to view the execution records of the ticket.

5. Click the Rollback Ticket tab to view the rollback operation records of the ticket.

6. Click the Logs tab to view all logs and alert logs of the ticket.

   Item	Description
   All Logs	All logs display the INFO, ERROR, and WARN logs of the ticket. You can click the Find, Download, and Copy buttons to find, download, or copy all logs.
   Alert Logs	Alert logs display the ERROR and WARN logs of the ticket. When the ticket fails, you can view the error information in the alert logs. You can click the Find, Download, and Copy buttons to find, download, or copy alert logs.

References

• Edit and execute SQL statements

• Perform PL compilation and debugging

• Use the command-line window

• Edit and export the result set

• Execution analysis

• Generate test data

• Manage partitions

• Synchronize shadow tables

• Online schema changes