Impact of MySQL lower_case_table_names on DTLE Data Synchronization

Background: A customer asked whether DTLE can correctly synchronize data when the source MySQL and target MySQL have inconsistent lower_case_table_names settings. This article tests the impact of that configuration on DTLE synchronization.

Environment preparation: DTLE 4.23.04.2 was deployed and two MySQL 5.7 instances were created with different lower_case_table_names values (0 and 1). The instances were launched with the following command:

# lower_case_table_names=0
$ dbdeployer deploy single 5.7 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid -c lower_case_table_names=0
# lower_case_table_names=1
$ dbdeployer deploy single 5.7 --port 3306 --sandbox-directory sandbox --port-as-server-id --remote-access % --bind-address 0.0.0.0 -c skip-name-resolve -c binlog_format=ROW -c binlog_row_image=FULL -c log_slave_updates=ON --gtid -c lower_case_table_names=1

First scenario (source @@lctn=0, target @@lctn=1): According to DTLE documentation, the source executes statements with the original case, while the target receives binlog entries in the original case but MySQL automatically converts identifiers to lower case. Typical SQL operations (CREATE DATABASE, CREATE TABLE, INSERT, ALTER, RENAME, DROP) were executed and the resulting data on the target showed lower‑case database and table names, confirming the expected behavior.

Extreme cases were also examined where tables with the same name but different case existed on the source. DTLE merged the four variants ( ACTION_DB.A , ACTION_DB.a , action_db.A , action_db.a ) into a single target table, demonstrating a potential data‑collision issue. The article recommends using the TableSchemaRename job attribute and the Table.TableRename configuration to avoid such collisions.

Second scenario (source @@lctn=1, target @@lctn=0): DTLE converts user‑specified replication objects to lower case, does not copy existing upper‑case objects, adds newly created lower‑case objects to the replication set, and the target receives binlog entries already in lower case. Test results (CREATE DATABASE, CREATE TABLE, INSERT, ALTER, RENAME, DROP) showed that both source and target data were consistently lower case, matching the documentation.

Other limitations: DTLE reads the lower_case_table_names setting from both source and target only during job initialization. Changing this parameter while a DTLE job is running is not detected, so the setting must remain unchanged for the job's lifetime.

Summary:

It is advisable to keep the lower_case_table_names configuration identical on source and target.

When source is 0 and target is 1, be aware that same‑named tables differing only by case will be merged on the target.

The lower_case_table_names setting must not be altered while a DTLE job is active.