Skip to Content
Precheck FAQ

Pre-check Error Messages and Solutions

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

log_bin: xxx, needs to be changed to ON

binlog_format: xxx, needs to be changed to ROW

binlog_row_image: xxx, needs to be changed to FULL

Solution:

When dealing with incremental synchronization, including scenarios such as Incremental Task, Full + Incremental Task, Bidirectional Synchronization, and Full Task followed by Incremental Task, it is required that the source database enables the binlog feature, with:

  • binlog_format set to ROW
  • binlog_row_image set to FULL

Note: For full migration only, this issue can be ignored.

1.1.1 Source Database Binlog Not Enabled

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

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

If you are using a cloud 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, so it does not need to be set.

1.1.2 Binlog Enabled but binlog_format or binlog_row_image Incorrect

Pay special attention that if you set binlog_format using MySQL command, when MySQL has connections writing data to the database, the written binlog_format remains unchanged and will only take effect after disconnecting the connection.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'ROW';
-- Similarly, MySQL 5.5 does not need to set binlog_row_image
SET GLOBAL binlog_row_image = 'FULL';
FLUSH LOGS;
UNLOCK TABLES;

After changes, you can disconnect existing connections using the following command:

-- 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 using kill, if you can confirm which connections have write operations, you can only kill these connections
> kill 497
-- After killing the original session, flush logs to ensure the new binlog format is ROW
> FLUSH LOGS;

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

Execute on the slave node:

stop slave;

Execute on the master:

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

Execute on the slave node:

start slave;

Note:
The parameter set by SET GLOBAL binlog_format = 'ROW';
Check with show global variables like 'binlog_format'; again, the value is still the original one, and the connection needs to be disconnected and reconnected to show 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.

The source database tables to be migrated include MyISAM engine tables, while the target database has GTID enabled, which may lead to migration failure.

Solution:

If the tables to be migrated from the source database include MyISAM engine tables and the target database has GTID enabled, it may cause MySQL 1785 error with the following 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 to convert MyISAM engine tables to InnoDB engine tables or disable GTID mode on the target database.

Check method:

# Check if 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);

# Check if GTID is enabled on the target database
show global variables like 'gtid_mode';

Settings:

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

# Solution 2: Modify the target database
# Disable GTID mode on 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

The source's max_allowed_packet value xxx is larger than the target's max_allowed_packet value yyy

Solution:

When the source’s max_allowed_packet value is larger than the target’s, it may lead to data write failures on the target database; it is suggested to adjust the max_allowed_packet value of the target database to keep it consistent with the source.

Execute on the source database:

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

Then use the acquired max_allowed_packet value on the target database, like this:

set global max_allowed_packet = 4194304;

1.4

Error Message:

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

Table: xxx requires a primary or unique key

Solution: If migration involves incremental synchronization, including scenarios such as Incremental Task, Full + Incremental Task, Bidirectional Synchronization, and Full Task followed by Incremental Task, each table needs to have a primary or unique key to avoid duplicate data in incremental synchronization. This issue can be ignored for full migrations only.

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

sql_mode may cause errors, check target db's sql_mode NO_ZERO_DATE/NO_ZERO_IN_DATE.

Solution: If the sql_mode of the source and target databases are different, it may prevent some data from migrating to the target database.

# Check sql_mode on source database
show variables like "sql_mode";

# Change sql_mode on target database to match the source
SET GLOBAL sql_mode='xxxx';

1.6

Error Message:

log_slave_updates should be ON

log_slave_updates needs to be set to ON

Solution: If you use a slave as the source for migration, it is required that the slave has log_slave_updates enabled, otherwise, there are no binlog logs on the slave, preventing migration.

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

Before incremental synchronization, stop event: 'db1':event1

Solution:

Before starting incremental synchronization, it is required to stop the event

# Stop all events
SET GLOBAL event_scheduler = OFF;

# If migrating by database, stopping the event for the particular database is sufficient
# Find and stop the corresponding event
USE db1;
SHOW EVENTS;
ALTER EVENT event1 DISABLE;

1.8

Error Message:

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

The parameters XXX have different values in the source and target, it is recommended to modify the parameters to ensure consistency

Solution:

The source and target databases have different innodb related parameters, which may cause data migration errors, it is recommended to modify the target database parameters to match the source

# Change the mismatched 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;

1.9

Error Message:

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

The parameters lower_case_table_names have different values in the source and target, it is recommended to modify the parameters to ensure consistency

Solution:

The lower_case_table_names parameters differ between the source and target, which may cause data migration errors; it is recommended to modify the target database parameters to be consistent with the source.

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

[mysqld]
...
lower_case_table_names = 0
...

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

2 TiDB

2.1

Error Message:

tikv_gc_life_time is xxx, and should be great than 1h

tikv_gc_life_time: xxx, needs to be greater than 1h

Solution:

If the migration task type is Full Task or Full + Incremental Task, and the amount of data to be migrated is large, it is required that the value of tikv_gc_life_time is greater than the dump time. Typically, 2T of data takes around 45 minutes, so we recommend setting this value to more than 1h.

Execute the following 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'.

TiDB does not support the charset used by this table xxx, please change the 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 charset of the table or a particular column in the source is not included in the sets mentioned above, it is not migratable.

Check method:

show create table table1;

Settings:

# Change the charset of table table1 to utf8
alter table task character set utf8;

# Change the charset of 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

The source and target database versions do not match, source: 3.6.23 , target: 5.0.14

Solution:

MongoDB currently does not support cross-major version migrations. When migrating from version 3.x to 5.x, a temporary 4.x version database is needed. First, migrate from 3.x to 4.x, and then from 4.x to 5.x.

4 Redis

4.1

Error Message:

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

The source and target database versions do not match, source: 4.0 , target: 7.0

Solution:

Redis cross-major version migration may have compatibility issues. It is recommended to use an intermediate version for migration. For example, when migrating from 3.x/4.x to 7.x, create a 5.x/6.x intermediate version, 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

Source database version 7.0 does not support rump mode

Solution:

Redis version 7.0 does not support rump mode; it is recommended that the source database enable psync privileges before migration.

FAQHow to migrate from Alibaba Cloud RDS MySQL to Surfercloud UDB