DDL change rules and best practices - PolarDB - Alibaba Cloud Documentation Center

Performing Data Definition Language (DDL) operations on a source table with a Search View can affect the synchronization link. This topic covers the impact of various DDL operations and provides best practices for rebuilding the view when necessary.

Impact of DDL changes on a source table

The following table describes the impact of different DDL operations on the Search View synchronization link.

Change type	Specific operation	Synchronization status	Description
Column changes	Drop a column	Normal	Values of the dropped column are retained in existing documents. For incremental data, the corresponding field's value is `null`.
	Add a column	Normal	The new column is not automatically synchronized. To add it to the Search View, see Best practices for changing a Search View.
	Modify a column type	Depends on the type	For type-compatible changes, such as changing `INT` to `TINYINT`, incremental data synchronization continues normally. For type-incompatible changes, the Search View becomes unavailable and you must rebuild it.
	Rename a column	Disrupted	A Search View uses the column names from the time it was created. After you rename a column, the Search View cannot find the renamed source table column. You must rebuild the Search View.
	Other (reorder columns, modify default values, modify comments, extend VARCHAR length, modify character sets, modify auto-increment properties, modify NULL constraints, etc.)	Normal	These operations do not affect Search View synchronization.
Index changes	Add, drop, or modify a secondary index	Normal	Changes to a secondary index do not affect synchronization.
Index changes	Drop or modify the primary key index	Disrupted	A Search View relies on the primary key of the source table for data synchronization. If you change the primary key, you must rebuild the Search View.
Table changes	`TRUNCATE TABLE`	Disrupted	Search Views cannot detect `TRUNCATE` operations. To clear table data and synchronize the change, use `DELETE FROM` instead.
Table changes	Other (`OPTIMIZE TABLE`, modify `ROW_FORMAT`, modify `KEY_BLOCK_SIZE`, update statistics, modify table character set, modify table comments, etc.)	Normal	These operations do not affect Search View synchronization.
Partitioned table changes	Convert to a partitioned table, add a partition, merge partitions, repartition, analyze a partition, check a partition, optimize a partition, rebuild a partition, convert to a non-partitioned table, use a local index, etc.	Normal	These operations do not affect Search View synchronization.
	`DROP PARTITION`, `DROP TABLESPACE`, `TRUNCATE PARTITION`	Disrupted	Search Views cannot detect these operations. To delete data and synchronize the changes, use `DELETE FROM` instead.
	`EXCHANGE PARTITION`, `REPAIR PARTITION`, `IMPORT TABLESPACE`	Disrupted	Search Views cannot detect direct data replacement or repair in a partition. Data in other partitions is unaffected. Rebuild the Search View after performing these operations.

Best practices for changing a Search View

To modify a Search View's synchronization structure, such as by adding a new field, rebuild it by creating a new index and a new view. After the new Search View is fully synchronized and verified, you can switch your application's query traffic to the new index.

Note

Online changes to an existing Search View without rebuilding it are not currently supported.

Example: Add a new column to the shop.user table and rebuild its Search View. Suppose an existing Search View synchronizes the shop.user table (including the id, name, phone, gmt_create columns) to an index named user_v1. To add a membership_level column without disrupting online queries:

Create a new index: On the PolarSearch node, create a new index named user_v2 that includes the new membership_level field in its mapping.

PUT user_v2
{
  "mappings": {
    "properties": {
      "id":               { "type": "keyword" },
      "name":             { "type": "text", "fields": { "keyword": { "type": "keyword" } } },
      "phone":            { "type": "keyword" },
      "gmt_create":       { "type": "date" },
      "membership_level": { "type": "integer" }
    }
  }
}

Modify the source table: On the source PolarDB for MySQL instance, add the new column to the user table.

ALTER TABLE shop.user ADD COLUMN membership_level TINYINT NOT NULL DEFAULT 0 COMMENT 'Membership level';

Create a new Search View: Create a new Search View to synchronize data from the shop.user table to the user_v2 index.
```
CREATE SEARCH VIEW user_v2 AS SELECT id, name, phone, gmt_create, membership_level FROM shop.user;
```
Verify and switch over: Use SHOW SEARCH VIEW STATUS to check the new Search View's status. When synchronization latency drops to 0–1 seconds, verify the data in the new index. After confirmation, switch your application's query traffic to the new user_v2 index.
Clean up old resources: After confirming the new Search View is stable, drop the old Search View and the user_v1 index.
```
DROP SEARCH VIEW user_v1;
```