row_format
in the source database table cannot be FIXED
.lower_case_table_names
variable in the source and target databases must be the same.max_allowed_packet
parameter of the target database must be set to 4 MB or above.connect_timeout
variable of the source database must be above 10.row_format
parameter in source databaseIf the value of row_format
in the database table is FIXED
, data overflow will occur if the storage length of each row in the table exceeds the limit, and an error will be reported. Therefore, you need to modify it to another format such as DYNAMIC
to make the storage length of each row vary by content length.
If a similar error occurs, fix it as follows:
row_format
to DYNAMIC
. alter table table_name row_format = DYNAMIC
show table status like '%row_format%';
The system should display a result similar to the following:mysql> show table status like '%row_format%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| row_format | DYNAMIC |
+---------------+----------+
1 row in set (0.00 sec)
lower_case_table_names
have the same value in source and target databaseslower_case_table_names
sets the letter case sensitivity in MySQL. It has the following valid values:
Windows and macOS environments are case-insensitive, but Linux environments are case-sensitive. To ensure the compatibility between different operating systems, you need to use the same letter case sensitivity rule.
If a similar error occurs, set the parameter in the source and target databases to the same value as follows:
lower_case_table_names
in the source and target databases.show variables like '%lower_case_table_names%';
lower_case_table_names
parameter.alter global lower_case_table_names = 1
[\$Mysql_Dir]/bin/mysqladmin -u root -p shutdown
[\$Mysql_Dir]/bin/safe_mysqld &
show variables like '%lower_case_table_names%';
The system should display a result similar to the following:mysql> show variables like '%lower_case_table_names%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.00 sec)
max_allowed_packet
parameter in target databasemax_allowed_packet
is the maximum size of a packet that can be transferred. If its value is too large, more memory will be used, causing packet losses and inability to capture the SQL statements of large exception event packets. If its value is too small, program errors mat occur, causing backup failure and frequent sending/receiving of network packets, which compromises the system performance.
If a similar error occurs, fix it as follows:
max_allowed_packet
parameter. set global max_allowed_packet = 4M
show global variables like '%max_allowed_packet%';
The system should display a result similar to the following:mysql> show global variables like '%max_allowed_packet%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| max_allowed_packet | 4194304 |
+------------------------+---------+
1 row in set (0.00 sec)
connect_timeout
variable in source databaseconnect_timeout
is the database connection time. Connection requests after connect_timeout
elapses will be rejected. If this value is too small, database connections will be closed frequently, affecting the processing efficiency. Therefore, we recommend you set a value above 10.
If a similar error occurs, fix it as follows:
connect_timeout
parameter.set global connect_timeout = 10
show global variables like '%connect_timeout%';
The system should display a result similar to the following:mysql> show global variables like '%connect_timeout%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| connect_timeout | 10 |
+------------------------+-------+
1 row in set (0.00 sec)
Was this page helpful?