This topic describes how to submit a lock-free change ticket to perform online schema changes on a database.
Background information
Principles
Note
If a task involves multiple tables, the tables are processed in a serial manner. That is, the steps for one table are completed before the steps for the next table are started.
The steps for processing a single table are as follows:
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.
Synchronize data. The data in the original table is synchronized to the shadow table by using full and incremental synchronization.
Perform data consistency checks. During incremental synchronization, data consistency checks are performed at regular intervals.
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.
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.
Rename the original table and the shadow table. The shadow table becomes the new business table for business production.
Procedure
Submit an online schema change task.
Perform a precheck on the SQL statements that you enter.
Approve the task.
Execute the online schema change task.
Supported operations
| Category | Operation | Supported | Notes |
|---|---|---|---|
| Column operations | Change the column type | Yes | |
| Rearrange columns (before/after/first) | Yes | ||
| Change the character set | Yes | ||
| Rename a column | No | ||
| Add a column | Yes | ||
| Drop a column | Yes | ||
| Add a primary key column | No | ||
| Primary key | Add a primary key | Yes | The table has a non-null unique key |
| Modify a primary key | Yes | The table has a non-null unique key | |
| Drop a primary key | Yes | The table has a non-null unique key | |
| Partition | Drop a partition | Yes | |
| Truncate a partition | No | ||
| Reorganize partitions | Yes | ||
| Convert a non-partitioned table to a partitioned table | Yes |
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.
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
In the left-side navigation pane, choose Tickets > Online Schema Change > Create Online Schema Change.
On the Create Online Schema Change page, specify the following parameters.
Parameter Description Database The database to be changed. Write Prohibition during Table Name Switch - Lock User: Before the table name is switched, the system locks the specified database account and terminates the current session of the account. During the switch, all applications that involve the account cannot access the database. We recommend that you avoid performing this operation during business peak hours to minimize the impact on the business.
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.
- Lock Table: Before the table name is switched, the system locks the table to be changed and blocks other sessions from accessing the table. We recommend that you avoid performing this operation during business peak hours to minimize the impact on the business.
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. - Retry Attempts on Lock Failure: The number of automatic retries when the lock fails during the business write-off period.
- 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 Immediate Execution/Scheduled Execution/Manual Execution to set the task execution mode.
- 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. - Lock User: Before the table name is switched, the system locks the specified database account and terminates the current session of the account. During the switch, all applications that involve the account cannot access the database. We recommend that you avoid performing this operation during business peak hours to minimize the impact on the business.
Click Create to create the online schema change task.
After the task is generated, you can view the task information in the Tickets > Online Schema Change list.
View an online schema change task
View task information
In the list of online schema change tasks, click View in the Actions column.
In the task details panel that appears, click the Task Information tab to view information such as the database to which the task belongs, the task type, the risk level, and the SQL content.
Click Initiate Again to re-initiate the online schema change task.
View the task process
In the task details panel, click the Task Process tab to view information such as the task initiation status, pre-check status, approval status, execution status, 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 the source table, as well as the task execution progress.
View the task logs
In the task details panel, click the Task Logs tab to view all logs and alert logs of the task.
| Item | Description |
|---|---|
| All Logs | All logs display the INFO, ERROR, and WARN logs of the task. You can click the Search, Download, and Copy buttons to search for, download, or copy all logs. |
| Alert Logs | Alert logs display the ERROR and WARN logs of the task. When a task fails, you can view the error information in the alert logs. You can click the Search, Download, and Copy buttons to search for, download, or copy the alert logs. |