Schema comparison

Background information

OceanBase Developer Center (ODC) provides the schema comparison feature that allows you to compare the schemas of database objects in the same type of data sources of the same project, and generate a change script.

This topic describes how to submit a schema comparison ticket in ODC to compare the schemas of two database objects.

The example in this topic describes how to compare the schemas of the employee tables in two OceanBase MySQL compatible mode data sources of the odc_4.2.0 project.

Considerations

• The following links are supported for schema comparison:

  • Links between MySQL-compatible tenants of OceanBase Database
  • Links between Oracle-compatible tenants of OceanBase Database
  • Links between MySQL databases

• Only table objects can be compared in terms of columns, primary keys, constraints, indexes, partitions, and attributes.

  • In terms of column attributes, only the data types, character sets, collations, comments, and default values can be compared.

  • Only partitions but not subpartitions can be compared.

  • In terms of table attributes, only the character sets, collations, and comments can be compared.

• CHECK constraints cannot be compared for MySQL-compatible tenants of OceanBase Database.

• For a MySQL-compatible tenant of OceanBase Database of a version earlier than V1.4.79, you cannot compare partition attributes if you do not have the query permissions on the sys tenant.

• If the size of the generated SQL change script exceeds 1 MB, the content of the script will not be directly displayed in SQL Preview. A message is displayed, prompting that the script file exceeds the size limit for display and that you can download the change script to view the full content.

Execution process

1. Initiate a schema comparison ticket.

2. Approve the task process.

3. Execute the schema comparison task and display the comparison results.

4. Create a database change task for schema synchronization.

Create a schema comparison task

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

2. On the Tickets tab, choose Schema Comparison > Create New.

3. In the Create Schema Comparison panel, configure the following parameters and click Create.

   Parameter	Description
   Source Database	Select the source database.
   Target Database	Select the target database for comparison.
   Comparison Scope	You can select Partial Tables or All Tables. Partial Tables: In the Select Source Table pane of the Objects for Comparison section, select the objects for comparison. All Tables: You can select this option to compare all table objects in the source database with those in the target database.
   Task Settings	Select Manual Execution, Execute Immediately, or Execute On Schedule in the Execution Mode section.
   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 schema comparison task list.

View the schema comparison task

1. In the left-side navigation pane, choose Tickets > Schema Comparison. In the task list, click the name of the target task to view the task details. You can also click the ··· icon in the Actions column to perform more operations.

   • Execute: If the task is manually executed, you can click this option to execute the task.
   • Terminate: If the task is periodically executed, you can click this option to terminate the task.
   • Disable/Enable: If the task is periodically executed, you can click this option to disable the task. The task will be disabled and will not be executed until you enable it.
   • Edit: If the task is disabled, you can click this option to edit the task.
   • Delete: If the task is completed or terminated, you can click this option to delete the task.
   • View: Click this option to view the task details.
   • Clone: Click this option to copy the task execution content and generate a new task with the same configuration.
   • Share: Click this option to copy the task access URL.
   • Initiate Schema Synchronization: Click this option to quickly create a database change task and synchronize or create all tables involved in the task.

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

   Note

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

3. In the task details panel, click the Workflow tab to view the task status, approval status, execution status, and execution result.

4. In the task details panel, click the 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 Find, 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. You can click the Find, Download, and Copy buttons to search for, 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

• Database change management

• Manage partitions

• Synchronize shadow tables

• Online schema changes