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.

Issue

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.

Example

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:
create table gb.test
(id int primary key, name varchar2(10))
 View the data of the source database.
Insert data:

insert into test values (1,'a');

insert into test values (2,'A');

insert into test values (3,'abc');

insert into test values (4,'Abc');

insert into test values (4,'aBc');

insert into test values (5,'aBc');

insert into test values (6,'abC');

commit;
Destination database: MySQL utf8
Note The character set is case-insensitive by default.
Create the following schema:
create table
test.test (auto_id int primary key auto_increment, name varchar(10), unique
key(name)
 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.

Cause

The case sensitivity of character sets is different between the source and destination databases.

Solution

  • 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.