PrecheckFAQ

1 MySQL

1.1

Error message:

log_bin is xxx, and should be ON

binlog_format is xxx, and should be ROW

binlog_row_image is xxx, and should be FULL

Solution:

If it involves incremental synchronization, including incremental tasks, full + incremental tasks, Bidirectional Synchronization and full tasks followed by incremental tasks, the source database is required to enable the binlog function, and

binlog_format should be ROW
binlog_row_image should be FULL

Note: If only full migration is carried out, this issue can be ignored.

1.1.1 Source database has not turned on binlog

Modify the configuration file (default is my.cnf), restart MySQL.

[mysqld]
...
log-bin = /data/mysql/logs
binlog_format = ROW
binlog_row_image = FULL
...

If you are using a cloud vendor's database service, you need to modify the corresponding configuration file and restart the database with the modified configuration.

Note: MySQL 5.5 does not have the binlog_row_image variable, no need to set.

1.1.2 The source database has turned on binlog, but the values of binlog_format or binlog_row_image are incorrect

It should be noted that if the binlog_format is set through the MySQL command, when there are connections writing data to the database, the binlog_format of the written data remains the original value. It will only take effect after the connection is disconnected.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'ROW';
-- Similarly, if it is MySQL 5.5, there is no need to set binlog_row_image
SET GLOBAL binlog_row_image = 'FULL';
FLUSH LOGS;
UNLOCK TABLES;

After the change is completed, you can disconnect the existing connections through the following commands:

-- View all current connections
> show processlist;
+-----+------+-----------------+--------+---------+------+----------+------------------+
| Id  | User | Host            | db     | Command | Time | State    | Info             |
+-----+------+-----------------+--------+---------+------+----------+------------------+
| 495 | root | 10.20.5.1:56820 | <null> | Query   | 0    | starting | show processlist |
| 497 | root | 10.20.5.1:56828 | <null> | Sleep   | 3    |          | <null>           |
+-----+------+-----------------+--------+---------+------+----------+------------------+
 
-- Disconnect all sessions through kill, if you can confirm which connections have write operations, you can only kill these connections
> kill 497
 
FLUSH LOGS;

If you are using the master-slave mode, you need to execute the following commands.

Execute on the slave node:

stop slave;

Execute on the master node:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'ROW';
FLUSH LOGS;
UNLOCK TABLES;

Execute on the slave node:

start slave;

Note:

After setting the parameter with SET GLOBAL binlog_format = 'ROW';, if you execute show global variables like 'binlog_format'; again, the value returned is still the original value. You need to disconnect and reconnect to display the changed value.

1.2

Error message:

The tables that need to be migrated from the source database include MyISAM engine tables, while the target database has GTID enabled, which may result in migration task failure.

Solution:

If the tables to be migrated in the source database include MyISAM engine tables, and the target database has enabled GTID, it may cause MySQL 1785 error, with the following error message:

When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables

It is recommended that users convert MyISAM engine tables to InnoDB engine tables, or turn off the GTID mode of the target database.

Query method:

# Query whether there are MyISAM tables in the source database (db1)
 
select table_schema, table_name
	from information_schema.tables
	where engine = 'MyISAM'
		and table_type = 'BASE TABLE'
		and table_schema in (db1);
 
# Query whether GTID has been enabled in the target database
show global variables like 'gtid_mode';

Setting method:

# Solution one: Modify the source database
# Change the engine of the MyISAM table table1 to InnoDB
alter table table1 ENGINE = InnoDB;

# Solution two: Modify the target database
# Turn off the GTID mode of the target database
set global gtid_mode = "ON_PERMISSIVE";
set global gtid_mode = "OFF_PERMISSIVE";
set global gtid_mode = "OFF";

1.3

Error message:

max_allowed_packet of the source is xxx, which is larger than max_allowed_packet of the target yyy

Solution:

When the max_allowed_packet value of the source database is greater than that of the target database, it may cause the data writing to the target database to fail. It is recommended to adjust the max_allowed_packet value of the target database to keep it consistent with the source database.

Execute in the source database:

> show global variables like "max_allowed_packet";
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

Then use the obtained max_allowed_packet value to execute in the target database, for example.

set global max_allowed_packet = 4194304;

1.4

Error message:

table xxx have no primary key or at least a unique key

Solution:

If the migration involves incremental synchronization, including Incremental Task, Full + Incremental Task, Two-way Synchronization, and scenarios where Incremental Task is performed after Full Task, a primary key or unique key needs to be set for each table, otherwise duplicate data may occur during the incremental synchronization stage. If only full migration is carried out, this issue can be ignored.

alter table xxx add primary key(xxxx);

1.5

Error message:

sql_mode may cause error. Please check sql_mode NO_ZERO_DATE/NO_ZERO_IN_DATE in target db

Solution:

If the sql_mode of the source database is inconsistent with the target database, some data may not be migrated to the target database.

# Query sql_mode in the source database
show variables like "sql_mode";

# Modify sql_mode in the target database to keep it consistent with the source database.
SET GLOBAL sql_mode='xxxx';

1.6

Error message:

log_slave_updates should be ON

Solution:

If you are using the slave database as the source for migration, it is required to enable log_slave_updates, otherwise there will be no binlog logs in the slave database, resulting in the inability to migrate.

Modify the configuration file (default is my.cnf), and restart MySQL.

[mysqld]
...
log_slave_updates = 1
...

1.7

Error message:

please stop event: 'db1':event1

Solution:

Before enabling incremental synchronization, you need to turn off the event.

# Stop all events
SET GLOBAL event_scheduler = OFF;

# If migrating by database, just stop the event of the specified database
# Find the corresponding event and stop it.
USE db1;
SHOW EVENTS;
ALTER EVENT event1 DISABLE;

1.8

Error message:

The variable xxx has different values in source and target. Please modify the variables to ensure consistency.

Solution:

The inconsistency of innodb related parameters between the source database and the target database may lead to data migration errors. It is recommended to modify the parameters of the target database to be consistent with the source database.

# Modify the inconsistent parameter values in the target database
set GLOBAL innodb_file_format_max = 'Barracuda';
set GLOBAL innodb_file_format = 'Barracuda';
set GLOBAL innodb_file_per_table = ON;
set GLOBAL innodb_strict_mode = OFF;

2 TiDB

2.1

Error message:

tikv_gc_life_time is xxx, and should be great than 1h

Solution:

If the type of migration task is Full Task or Full + Incremental Task, and the amount of data migrated is large, the value of tikv_gc_life_time needs to be greater than the dump time. Generally, for 2T of data, it takes about 45min, we recommend setting this value to more than 1h.

Execute the following statement in TiDB:

update mysql.tidb set VARIABLE_VALUE="1h" where VARIABLE_NAME="tikv_gc_life_time";

2.2

Error message:

TiDB dose not support charset in table xxx. Please change charset to any one of 'ascii/latin1/binary/utf8/utf8mb4'.

Solution:

The character sets currently supported by TiDB include ascii/latin1/binary/utf8/utf8mb4.

When migrating from MySQL to TiDB, if the character set used by the table or a field in the table to be migrated in the source database is not included in the above character sets, the migration cannot be performed.

Query method:

show create table table1;

Setting method:

# Change the character set of table table1 to utf8
alter table task character set utf8;
 
# Change the character set of field column1 in table table1 to utf8
alter table table1 change column1 column1 varchar(200) character set utf8;

3 MongoDB

3.1

Error message:

Source and target version do not match, source verion is 3.0, and target version is 5.0

Solution:

Mongo does not currently support migration across major versions. When migrating from 3.x to 5.x, you need to create a 4.x version of the transit database, first migrate from 3.x to 4.x, and then migrate from 4.x to 5.x.

4 MongoDB

4.1

Error message:

Source and target version do not match, source verion is 4.0, and target version is 7.0

Solution:

Compatibility issues may arise when migrating Redis across major versions. It is recommended to migrate through a transit database. For example, when migrating from 3.x/4.x to 7.x, you need to create a 5.x/6.x version of the transit database. First migrate from 3.x/4.x to 5.x/6.x, and then from 5.x/6.x to 7.x.

4.2

Error message:

The source database version is 7.0, and does not support rump mode

Solution:

Redis 7.0 version does not support rump mode, it is recommended to enable psync permissions on the source database before migration.

FAQ