Cancel a partition transfer task

You can cancel a manually initiated partition transfer task based on your business needs.

Limitations

• You cannot cancel a partition transfer task that is in the DOING state, but cancellation is allowed for tasks in other states.

• You can cancel partition transfer tasks only in primary databases or tenants.

Cancel a partition transfer task in the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Query the CDB_OB_TRANSFER_PARTITION_TASKS view for the status of the current task.

   obclient [oceanbase]> SELECT TENANT_ID, TABLE_ID, OBJECT_ID, STATUS FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+----------+-----------+---------+
   | TENANT_ID | TABLE_ID | OBJECT_ID | STATUS  |
   +-----------+----------+-----------+---------+
   |      1002 |   500006 |    500008 | WAITING |
   +-----------+----------+-----------+---------+
   1 row in set
   

   If the value of the STATUS field in the query result is not DOING, but is, for example, WAITING or INIT, the task can be canceled.

   If you want to cancel a partition transfer task in the DOING state, you can set the tenant-level parameter enable_transfer to false to disable transfer. After transfer is disabled, all partition transfer tasks in the DOING state will be rolled back to the WAITING state and can be canceled.

   For more information about the enable_transfer parameter, see enable_transfer.

3. Perform either of the following operations based on the query result:

   • Cancel the partition transfer task for a specified partition in a specified tenant

     ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID [=] table_id OBJECT_ID [=] object_id TENANT = 'tenant_name';
     

     where:

     • table_id indicates the ID of the table for which the partition transfer task is to be canceled.

     • object_id indicates the ID of the partition for which the partition transfer task is to be canceled.

     • tenant_name indicates the name of the tenant for which the partition transfer task is to be canceled.

     Here is an example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID = 500006, OBJECT_ID = 500008 TENANT = 'mysql_tenant';
     

   • Cancel all partition transfer tasks in a specified tenant

     ALTER SYSTEM CANCEL TRANSFER PARTITION ALL TENANT = 'tenant_name';
     

     Here is an example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION ALL TENANT = 'mysql_tenant';
     

Cancel a partition transfer task in a user tenant

1. Connect to the database as the tenant administrator of a MySQL or Oracle tenant.

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
   

2. Query the DBA_OB_TRANSFER_PARTITION_TASKS view for the status of the current task.

   MySQL mode

   Oracle mode

   The query statement in MySQL mode is as follows:

   obclient [oceanbase]> SELECT TABLE_ID, OBJECT_ID, STATUS FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASKS;
   

   The query statement in Oracle mode is as follows:

   obclient [SYS]> SELECT TABLE_ID, OBJECT_ID, STATUS FROM SYS.DBA_OB_TRANSFER_PARTITION_TASKS;
   

   A sample query result is as follows:

   +----------+-----------++--------+
   | TABLE_ID | OBJECT_ID | STATUS |
   +----------+-----------+--------+
   |   500046 |    500052 | INIT   |
   +----------+-----------+--------+
   1 row in set
   

   If the value of the STATUS field in the query result is not DOING, but is, for example, WAITING or INIT, the task can be canceled.

   If you want to cancel a partition transfer task in the DOING state, you can set the tenant-level parameter enable_transfer to false to disable transfer. After transfer is disabled, all partition transfer tasks in the DOING state will be rolled back to the WAITING state and can be canceled.

   For more information about the enable_transfer parameter, see enable_transfer.

3. Perform either of the following operations based on the query result:

   • Cancel the partition transfer task for a specified partition in the current tenant

     ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID [=] table_id OBJECT_ID [=] object_id;
     

     where:

     • table_id indicates the ID of the table for which the partition transfer task is to be canceled.

     • object_id indicates the ID of the partition for which the partition transfer task is to be canceled.

     Here is an example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID = 500046, OBJECT_ID = 500052;
     

   • Cancel all partition transfer tasks in the current tenant

     obclient> ALTER SYSTEM CANCEL TRANSFER PARTITION ALL;
     

References

• Transfer a partition

• Cancel a load balancing job