Preparations before upgrade

Before you upgrade OceanBase Developer Center (ODC), you must confirm the metadata database and the environment.

MetaDB

When you deploy Web ODC, you create a MetaDB to store user data generated during ODC operations.

When you upgrade ODC, you use the same MetaDB as during deployment and run the corresponding version of the ODC image. Before the upgrade, confirm the tenant name, cluster name, database name, username, and password of the MetaDB.

Server environment

The following table describes the server environment for Web ODC based on the number of ODC users.

You can adjust resources based on the number of users.

(Optional) Pre-upgrade operations

The metadata data upgrade of ODC V4.4.1 is designed to be reentrant. If you want to complete the upgrade process quickly, you can perform some data migration operations in advance. These operations will not affect the running of the old version.

1. Check whether to perform data migration in advance.

   The metadata data upgrade of ODC version upgrade is automatically performed. Some changes in features may involve a large amount of data migration and correction. The main metadata upgrade of ODC V4.4.1 is the reconstruction of the ticket/operation module. Query the data volume in the current database to determine whether to perform data migration in advance.

   • The SQL plan is redesigned as a feature of the operation module. The historical data will be migrated from the ticket module to the operation module. Execute the following command. If the query result is greater than 100,000, we recommend that you perform data migration in advance. For more information, see Step 2.

     use odc_metadb;
     select count(1) from flow_instance where parent_instance_id in (select id from schedule_schedule where job_type in 'SQL_PLAN');
     

   • The partitioning plan is also redesigned. Execute the following command. If the query result is greater than 100,000, we recommend that you perform data migration in advance. For more information, see Step 3.

     use odc_metadb;
     select count(1) from flow_instance where parent_instance_id in (select distinct flow_instance_id from partitionplan);
     

   • The operation module may also contain some historical ticket data that is no longer needed. Execute the following SQL statement to obtain the data. If the query result is greater than 100,000, we recommend that you execute the following SQL statement to perform data migration. For more information, see Step 4.

     use odc_metadb;
     select count(1)  FROM schedule_task WHERE job_group IN ('SQL_PLAN', 'PARTITION_PLAN') AND job_id IS NULL
     

2. Manually migrate the historical data of the SQL plan. Repeat the execution of this SQL statement until no more data is inserted.

   #1. Query the subtasks (database change) of the SQL plan.
   #2. Obtain the subtask information of the SQL plan.
   #3. Perform a join query to batch query and insert data.
   INSERT INTO schedule_task (
       job_group,
       job_name,
       parameters_json,
       status,
       executor,
       fire_time,
       progress_percentage,
       result_json,
       create_time,
       update_time,
       job_id
   )
   SELECT
       'SQL_PLAN' AS job_group,
       CAST(plan_data.schedule_id AS CHAR) AS job_name,
       tt.parameters_json AS parameters_json,
       CASE
           WHEN tt.status IN ('FAILED', 'EXEC_TIMEOUT', 'CANCELED', 'DONE') THEN tt.status
           ELSE 'CANCELED'
       END AS status,
       tt.executor,
       COALESCE(tt.last_heartbeat_time, tt.create_time) AS fire_time,
       COALESCE(tt.progress_percentage, 0) AS progress_percentage,
       tt.result_json,
       tt.create_time,
       tt.update_time AS update_time,
       -plan_data.flow_instance_id AS job_id
   FROM (
       SELECT fi.id AS flow_instance_id,
               ss.id AS schedule_id
       FROM flow_instance fi
       JOIN schedule_schedule ss ON fi.parent_instance_id = ss.id
       WHERE ss.job_type = 'SQL_PLAN'
           AND fi.id > (SELECT COALESCE(-MIN(job_id), 0) FROM schedule_task WHERE job_group = 'SQL_PLAN')
       ORDER BY fi.id
       LIMIT ?
   ) plan_data
   JOIN flow_instance_node_task fint ON fint.flow_instance_id = plan_data.flow_instance_id
   JOIN task_task tt ON tt.id = fint.task_task_id
   WHERE fint.task_type = 'ASYNC' AND fint.is_start_endpoint = 1;
   

   If you want to roll back the manually migrated historical data of the SQL plan, execute the following SQL statement until no more data needs to be deleted.

    ```sql
    delete from schedule_task where job_group = 'SQL_PLAN' and job_id < 0 order by job_id asc limit 5000
    ```
   

3. Manually migrate the historical data of the partitioning plan. Repeat the execution of this SQL statement until no more data is inserted.

   #1. Query the schedule_id of the historical partitioning plan and the database change orders under the partitioning plan (parent_flow_id = flow_instance_id of the partitioning plan).
   #2. Query the task_task association information and integrate it into the fields corresponding to schedule_task.
   #3. Batch insert the schedule_id as job_name through the application layer.
   INSERT INTO schedule_task (
           job_group,
           job_name,
           parameters_json,
           status,
           executor,
           fire_time,
           progress_percentage,
           result_json,
           create_time,
           update_time,
           job_id
       )
       SELECT
           'PARTITION_PLAN' AS job_group,
           CAST(plan_data.schedule_id AS CHAR) AS job_name,
           tt.parameters_json AS parameters_json,
           tt.status AS status,
           tt.executor,
           COALESCE(tt.last_heartbeat_time, tt.create_time) AS fire_time,
           COALESCE(tt.progress_percentage, 0) AS progress_percentage,
           tt.result_json,
           tt.create_time,
           tt.update_time AS update_time,
           -plan_data.flow_instance_id AS job_id
       FROM (
           SELECT fi.id AS flow_instance_id,
                   mapped.schedule_id
           FROM flow_instance fi
           JOIN (
               SELECT
                   p.flow_instance_id,
                   COALESCE(
                       MAX(CASE WHEN COALESCE(ptp.strategy, 'CREATE') = 'CREATE' THEN pt.schedule_id END),
                       MAX(pt.schedule_id)
                   ) AS schedule_id
               FROM partitionplan p
               JOIN partitionplan_table pt ON p.id = pt.partitionplan_id
               LEFT JOIN partitionplan_table_partitionkey ptp ON pt.id = ptp.partitionplan_table_id
               GROUP BY p.flow_instance_id
           ) mapped ON fi.parent_instance_id = mapped.flow_instance_id
           WHERE mapped.schedule_id IS NOT NULL
               AND fi.id > (SELECT COALESCE(-MIN(job_id), 0) FROM schedule_task WHERE job_group = 'PARTITION_PLAN')
           ORDER BY fi.id
           LIMIT ?
       ) plan_data
       JOIN flow_instance_node_task fint ON fint.flow_instance_id = plan_data.flow_instance_id
       JOIN task_task tt ON tt.id = fint.task_task_id
       WHERE tt.task_type = 'ASYNC';
   

   To roll back the manually migrated historical data of the partitioning plan, execute the following SQL until no more data is to be deleted.

    ```sql
    delete from schedule_task where job_group = 'PARTITION_PLAN' and job_id < 0 order by job_id asc limit 5000
    ```
   

4. Clean up the useless data in the job module.

   1. Create a backup table for the useless data.

      CREATE TABLE IF NOT EXISTS `schedule_task_backup` LIKE `schedule_task`;
      

   2. Backup the data by repeatedly executing the following SQL until no more data is inserted.

      REPLACE INTO schedule_task_backup (
              id, job_name, job_group, parameters_json, status, executor,
              fire_time, progress_percentage, result_json, create_time, update_time, job_id
          )
          SELECT id, job_name, job_group, parameters_json, status, executor,
              fire_time, progress_percentage, result_json, create_time, update_time, job_id
          FROM schedule_task
          WHERE job_group IN ('SQL_PLAN', 'PARTITION_PLAN')
          AND job_id IS NULL
          AND id > (SELECT COALESCE(MAX(id), 0) FROM `schedule_task_backup`)
          ORDER BY id LIMIT 10000
      

   3. Delete the data in the main table by repeatedly executing the following SQL until no more data is deleted.

      DELETE FROM schedule_task
          WHERE job_group IN ('SQL_PLAN', 'PARTITION_PLAN')
          AND job_id IS NULL and id <= (SELECT COALESCE(MAX(id), 0) FROM `schedule_task_backup`)
          ORDER BY id
          LIMIT 10000
      

   4. If you want to roll back the manually cleaned data, repeatedly execute the following SQL until no more data is inserted.

      INSERT INTO schedule_task (                 
          id, job_name, job_group, parameters_json, status, executor,                 
          fire_time, progress_percentage, result_json, create_time, update_time, job_id             
          )             
          SELECT id, job_name, job_group, parameters_json, status, executor, 
          fire_time, progress_percentage, result_json, create_time, update_time, job_id 
          FROM schedule_task_backup             
          WHERE id > (SELECT COALESCE(MAX(id), 0) FROM `schedule_task` where job_id is NULL and  id <= (select max(bak.id) from schedule_task_backup as bak))
          ORDER BY id LIMIT 10;