Checking Details
1. It is recommended to set the environment variable parameter innodb_stats_on_metadata to OFF on the source database.
2. When the target database is MySQL/MariaDB/Percona/TDSQL-C for MySQL/TDSQL Boundless, it is recommended to set the parameter explicit_defaults_for_timestamp to ON on the target database. If it is set to OFF, Data Transfer Service (DTS) will attempt to change it to ON on the target at the session level only.
If the target runs MySQL 8.0, DTS can automatically change the session-level parameter to ON on the target.
If the target runs MySQL with a version earlier than 8.0 or is of another database type, DTS cannot change the parameter value, which causes the initial check task to fail. After assessing the impact, users can skip this check and then initiate the check task again.
3. If the target runs TencentDB for MySQL and uses the RocksDB engine, it is necessary to check the rocksdb_skip_unique_check_tables parameter. It is recommended to set the parameter to NULL; otherwise, the check task will fail.
If the parameter is set to .*, unique key checks are skipped during table inserts or updates. For DTS data transfer tasks, you need to set the parameter to NULL, ensuring that unique key checks are not skipped during table inserts or updates.
When a check task fails, submit a ticket to request assistance from Tencent Cloud Ops personnel for modifications. Whether to Skip
When a check task fails, users can assess the impact and decide whether to modify the parameter. If they confirm that no modification is needed, they can skip the parameter check.
innodb_stats_on_metadata: Checks can be skipped.
explicit_defaults_for_timestamp: Checks can be skipped.
Fixing Methods
Modifying the Parameter innodb_stats_on_metadata
innodb_stats_on_metadata: If this parameter is enabled, InnoDB updates the information_schema.statistics table whenever tables in the information_schema metadata database are queried, increasing access time. You can disable this parameter to accelerate access to schema databases and tables.
For MySQL versions earlier than 5.6.6, the preset value of the parameter innodb_stats_on_metadata is ON and must be changed to OFF. For MySQL 5.6.6 and later versions, the preset value is OFF, and no issue exists.
1. Log in to the source database.
2. Set innodb_stats_on_metadata to OFF.
set global innodb_stats_on_metadata = OFF;
3. Check whether the configuration has taken effect.
show global variables like '%innodb_stats_on_metadata%';
The system displays results similar to the following:
mysql> show global variables like '%innodb_stats_on_metadata%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF |
+--------------------------+-------+
1 row in set (0.00 sec)
4. Execute the check task again.
Modifying the Parameter explicit_defaults_for_timestamp
Parameter Description
explicit_defaults_for_timestamp controls how MySQL handles default values for TIMESTAMP columns. The value descriptions are as follows:
OFF: MySQL sets the default value of TIMESTAMP columns to CURRENT_TIMESTAMP. When inserting data without specifying a value for TIMESTAMP columns, it automatically uses CURRENT_TIMESTAMP.
ON: MySQL does not automatically set the default value of TIMESTAMP columns to CURRENT_TIMESTAMP.
Parameter Impact
This parameter is a feature in MySQL versions later than 5.6.6. During DTS migration or synchronization, set the parameter to ON on the target to ensure data consistency. If the parameter is set to OFF and not modified as prompted, synchronizing data with TIMESTAMP columns may cause table structure inconsistencies between the source and the target.
If you need to modify the parameter, run the following command.
Note:
To modify this parameter, you need to reset all database connections for the modification to take effect. Avoid setting it at the session level, or you may not be able to check its status.
set global explicit_defaults_for_timestamp = ON