Data Management:Repair table schema inconsistency

Use cases

• Cross-environment sync: Propagate schema changes from a test database to the production database.

• Table creation from a template: Use an existing table as a base and create new physical or logical tables with the same schema.

• Cross-database repair: Fix schema drift between tables that live in different databases.

• Logical database rollout: Apply a physical table's schema changes to every table in a logical database in a single operation.

How it works

The repair workflow has three phases:

1. Compare: DMS analyzes the schemas of the base table and destination table and generates the SQL statements needed to align them.

2. Approve: You review the generated SQL and submit it for approval.

3. Apply: After approval, DMS executes the SQL statements against the destination table.

Prerequisites

Before you begin, make sure you have:

• Query permissions on the base database. For details, see Manage permissions.

• Alter table permissions on the destination database. For details, see Manage permissions.

• (Required if the destination table does not exist) The security rule Enable execution capability (if closed, other rules are invalid) enabled for the destination database. For details, see Table synchronization.

Repair a table inconsistency

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Database Development > Schema Change > Table Consistency Repairing.

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner and choose All Features > Database Development > Schema Change > Table Consistency Repairing.

3. On the Table Sync Tickets page, configure the following parameters.

   Parameter	Description
   Base Database(Physical Database)	The source of truth for the repair. Select the database whose schema you want to replicate. To search, enter a keyword in the field. Optionally, select a schema version number — the latest schema is used by default. For details about schema versions, see Manage schema versions.
   Destination Database	The database that contains the table to repair. Select a physical or logical database. To search, enter a keyword in the Target Database field. Optionally, select a schema version — the latest schema is used by default. For details, see Manage schema versions.
   Repaired Table	Enter the name of the base table or select the table from the drop-down list on the left side. Enter the name of the destination table or select the table from the drop-down list on the right side. The destination table can be a physical table, a logical table, or an aggregate table. If the destination table already exists, DMS compares its schema with the base table and generates SQL to fix the differences. If it does not exist, DMS creates the table automatically.
   Whether to Ignore Error	Controls what happens if a SQL statement fails during execution. Not Ignore: DMS stops executing all remaining statements when an error occurs. Ignore: DMS skips the failed statement and continues executing the remaining ones.
   Business Background(Remarks)	A description of why you are performing this repair, such as the purpose or the related change request.

4. Click Submit. DMS analyzes the schemas of the base table and destination table and generates the SQL statements to resolve the differences.

5. Review the generated SQL statements, then click Submit for Approval.

6. After the approval is granted, click Submit and Synchronize to Target Database. DMS executes the SQL statements against the destination table.

What's next

• To track schema changes over time, see Manage schema versions.

• To manage query and alter table permissions for your databases, see Manage permissions.