If the character sets of the source and destination databases have inconsistent case sensitivity, DTS may fail to migrate or synchronize some data records to the destination database. This topic describes how to avoid this situation.
Different databases have different requirements for case sensitivity. If the case sensitivity of the destination database is inconsistent with that of the source database, data loss may occur in the destination database after data migration or synchronization.
Oracle and MySQL are used in this example to demonstrate how inconsistent case sensitivity causes data loss in the destination database.
Oracle databases and MySQL databases have different requirements for case sensitivity.
- Oracle: Case-sensitive. You can insert values that differ only in case into a primary key or unique key field, for example, 'a' and 'A'.
- MySQL: Case sensitivity varies with collations. In most cases, "_ci" indicates that a collation is case-insensitive and "_cs" indicates that a collation is case-sensitive. If a collation is suffixed with "_ci", it is case-insensitive. You cannot insert values that differ only in case into a primary key or unique key field, for example, 'a' and 'A'.
Migration scenario: Data is migrated from the source Oracle database to the destination MySQL database. After you configure the data migration task, do not select Schema Migration as the migration type. After the migration is complete, view the data of the destination database.
|Database type||Character set||Description||Data|
|Source database: Oracle||AL32UTF||Migrate the data of the gb.test table:
||View the data of the source database.|
|Destination database: MySQL||utf8
Note The character set is case-insensitive by default.
|Create the following schema:
||After you configure the data migration task, do not select Schema Migration as the migration type. After the migration is complete, view the data of the destination database.|
The case sensitivity of character sets is different between the source and destination databases.
- Check whether your database contains data records that differ only in case. Make sure that all data records are stored in the same case, for example, all in lowercase.
- If the source database is case-sensitive, specify case-sensitive collations on the destination database.