During data sync, when users select objects, they can choose the level of repository objects and table objects, or even the column level. This article introduces column-level data sync.
Application Scope
Data sync links between MySQL, MariaDB, Percona, and TDSQL-C for MySQL.
Restrictions
1. Columns of primary keys and unique keys do not support filtering. The system will synchronize columns of primary keys and unique keys to the target by default. In special cases, if primary keys or unique keys are filtered, pre-validation will result in an error.
2. If the filtered column is a composite primary key, foreign key or partition column, or if the filtered column includes an index, then the source does not support DML operation on that column; otherwise, the task may report an error.
3. The interaction explanation between column filtering and view is as follows:
If the view is selected in the sync object, then the related column data of the view needs to be synced as well and cannot be filtered; otherwise, the task will report an error.
If the view is selected in the sync object, and subsequently, the source database associates the created view with the system database table, then column filtering cannot be used in the sync task at the same time; otherwise, the task will report an error.
4. Special explanations for the interaction between the Rename operation and the column filtering feature are as follows:
Table A underwent column filtering, the source then deleted Table A, and recreated another Table A with an identical structure. In this scenario, the created Table A will still undergo column filtering.
Table A underwent column filtering, then the source deleted Table A, and renamed Table B as Table A, with Tables A and B having identical table structures. In this scenario, the sync result for Table B will not undergo column filtering.
5. After column filtering is configured, modifying the sync configuration is not supported. Similarly, when the sync configuration is modified, filtering columns is not supported.
6. The interaction between column filtering and Where condition filtering is as follows:
If a table has undergone column filtering and the user also sets Where rules for this table, the user must ensure that the column names in the Where rules input are included in the table to be synced; otherwise, the Where rules can be set successfully, but subsequent task initiation will result in an error.
7. If the column X of Table A has been filtered, and the user executes the 'CREATE TABLE table_B like table_A' statement on the source, it is recommended to also filter the column X of Table B. In this scenario, DTS's sync of Table B will follow Table A's filtered structure. If the column X of Table B is not filtered, subsequent data updates to the column X of Table B on the source side will cause the DTS task to fail.
8. If the unique key column in the table object includes a function, then when the sync object checks that table, it cannot expand to the column dimension and cannot perform column filtering.
9. Only select scenarios that sync data instead of the table structure. This scenario requires users to create the same table structure on the target before starting the DTS task. If the task also needs column filtering settings, then the table structure created by the users on the target should be after column filtering; otherwise, sync to the target might result in errors.
Directions
1. Log in to the DTS console, choose Data Sync Page in the left sidebar to create a sync task.
2. In Set source and target databases, you can select table-level and column-level data in Source Database Object on the left; after they are checked, click
, and the objects to be synced will be displayed on the right.
