DDL change rules for PolarDB Search Views - PolarDB - Alibaba Cloud Documentation Center

DDL operations on a source table with a Search View can affect synchronization. This topic describes how each DDL type affects the sync link and how to rebuild the view when needed.

DDL impact on Search View synchronization

The following table lists how different DDL operations affect Search View synchronization.

Change type	Specific operation	Synchronization status	Description
Column changes	Drop a column	Normal	Existing documents retain the dropped column's values. In incremental data, the field value is `null`.
	Add a column	Normal	The new column is not automatically synchronized. To add it, rebuild the Search View as described in Best practices for changing a Search View.
	Modify a column type	Depends on the type	Type-compatible changes (for example, `INT` to `TINYINT`) continue synchronizing normally. Type-incompatible changes make the Search View unavailable. You must rebuild it.
	Rename a column	Disrupted	A Search View maps column names at creation time. After a rename, the view cannot locate the renamed 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 synchronization.
Index changes	Add, drop, or modify a secondary index	Normal	Secondary index changes do not affect synchronization.
Index changes	Drop or modify the primary key index	Disrupted	Search View synchronization depends on the primary key. 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 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 synchronization.
	`DROP PARTITION`, `DROP TABLESPACE`, `TRUNCATE PARTITION`	Disrupted	Search Views cannot detect these operations. To synchronize the deletions, use `DELETE FROM` instead.
	`EXCHANGE PARTITION`, `REPAIR PARTITION`, `IMPORT TABLESPACE`	Disrupted	Search Views cannot detect direct data replacement or repair in a partition. Other partitions are unaffected. Rebuild the Search View after these operations.

Rebuild a Search View

To change a Search View's synchronization structure (for example, add a field), create a new index and a new view. After the new view is fully synchronized and verified, switch your application's query traffic to the new index.

Note

Online changes to an existing Search View are not currently supported. You must rebuild the view.

Example: Add a membership_level column to the shop.user table and rebuild its Search View. Assume an existing Search View synchronizes the shop.user table (columns: id, name, phone, gmt_create) to an index named user_v1. To add the 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: Run SHOW SEARCH VIEW STATUS to check the new view's status. When synchronization latency drops to 0–1 seconds, verify the data and switch your application's query traffic to the user_v2 index.
Clean up old resources: After the new Search View is stable, drop the old view and the user_v1 index.
```
DROP SEARCH VIEW user_v1;
```