Data Transmission Service:Data loss from inconsistent character set case sensitivity

Symptoms

Different databases handle case sensitivity differently. If there is a mismatch in case sensitivity between your source and destination databases, you might find data missing in the destination database after the migration or synchronization completes.

Example

This example uses an Oracle source database and a MySQL destination database to illustrate how a case sensitivity mismatch can cause data loss.

Oracle and MySQL databases have different default behaviors for case sensitivity.

• Oracle: By default, Oracle is case-sensitive. This allows you to insert values that differ only in case, such as 'a' and 'A', into a column with a primary key or unique key.
• MySQL: Case sensitivity is determined by the collation. Generally, a collation ending in "_ci" is case-insensitive, and one ending in "_cs" is case-sensitive. With a "_ci" collation, you cannot insert values that differ only by case, such as 'a' and 'A', into a column with a primary key or unique key because they are treated as the same value.

Migration scenario: You migrate data from a source Oracle database to a destination MySQL database using DTS. In the task configuration, you do not select the Schema Migration option. After the migration completes, you check the data in the destination database.

create table gb.test
(id int primary key, name varchar2(10))

SQL> select * from test;

    ID NAME
------ ---------
     1 a
     2 A
     3 abc
     4 Abc
     5 aBc
     6 abC

6 rows selected.

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;

create table
test.test (auto_id int primary key auto_increment, name varchar(10), unique
key(name))

mysql> select * from test;
+---------+------+
| auto_id | name |
+---------+------+
|       4 | a    |
|       5 | abc  |
+---------+------+
2 rows in set (0.01 sec)

Cause

The source and destination databases use character sets with inconsistent case sensitivity.

Resolution

• Review your application logic to determine if you need to store data that differs only by case, such as 'abc' and 'Abc'. If this is not a requirement, enforce a consistent case for your data, such as by converting all strings to lowercase before you write them to the database.
• If your source database is case-sensitive, ensure that you configure the destination database with a case-sensitive collation. For example, in MySQL, choose a collation that ends in _cs.