Error
Error Code:
1677
MariaDB Error 1677: Replication Type Mismatch
Description
This error indicates that MariaDB failed to convert data from one column type to another during an operation. It most commonly occurs in replication environments when the slave server cannot convert a column's data type to match the master's schema or the incoming data, leading to a conversion failure.
Error Message
Column %d of table '%s.%s' cannot be converted from type '%s' to type '%s'
Known Causes
3 known causesReplication Schema Divergence
The table schema on the slave server for a specific column differs significantly from the master server's schema, preventing successful data type conversion during replication.
Unsupported Data Type Conversion
An operation attempts to convert data between two types that MariaDB cannot implicitly or explicitly convert, often due to fundamental incompatibility between the types.
Invalid Data for Conversion
The data within the source column is not compatible with the target data type, such as attempting to convert a string containing non-numeric characters into an integer column.
Solutions
3 solutions available1. Synchronize Column Definitions Between Master and Replica medium
Ensure identical data types and attributes for the offending column on both servers.
1
Identify the exact table and column causing the mismatch. The error message provides this information (e.g., column 5 of `mydatabase.mytable`).
2
On the **master** server, retrieve the column definition for the problematic column.
SHOW CREATE TABLE `mydatabase`.`mytable`;3
On the **replica** server, compare the column definition for the same table and column. Pay close attention to the data type, length, precision, and any other attributes (e.g., UNSIGNED, ZEROFILL, character set, collation).
SHOW CREATE TABLE `mydatabase`.`mytable`;4
Modify the column definition on the **replica** server to exactly match the master. If a `ALTER TABLE` statement is needed, use the definition from the master. For example, if the master has `INT UNSIGNED NOT NULL` and the replica has `INT NOT NULL`, you'd run:
ALTER TABLE `mydatabase`.`mytable` MODIFY COLUMN `column_name` INT UNSIGNED NOT NULL;5
Restart the replica's SQL thread to apply the changes and resume replication.
STOP SLAVE SQL_THREAD;
START SLAVE SQL_THREAD;2. Manually Rebuild Replica Table Schema medium
Drop and recreate the problematic table on the replica to enforce schema consistency.
1
Identify the table and column causing the error from the error message.
2
On the **replica** server, stop the replica's SQL thread to prevent further writes to the problematic table.
STOP SLAVE SQL_THREAD;3
On the **replica** server, get the `CREATE TABLE` statement from the master.
SHOW CREATE TABLE `mydatabase`.`mytable`;4
On the **replica** server, drop the problematic table.
DROP TABLE `mydatabase`.`mytable`;5
On the **replica** server, re-create the table using the `CREATE TABLE` statement obtained from the master.
CREATE TABLE `mydatabase`.`mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`value` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6
On the **replica** server, restart the SQL thread. Replication should now process changes for this table correctly.
START SLAVE SQL_THREAD;3. Adjust Binary Log Format on Master advanced
Ensure the master's binary log format is compatible with the replica's capabilities, particularly for complex data types.
1
Check the current binary log format on the **master** server.
SHOW VARIABLES LIKE 'binlog_format';2
If the `binlog_format` is `ROW` or `MIXED`, and you are encountering issues with data type conversions, consider changing it to `STATEMENT` (though this has its own potential replication risks, like non-deterministic statements). **Note:** This is a significant change and requires careful consideration of its implications.
SET GLOBAL binlog_format = 'STATEMENT';3
Restart the **master** server for the `binlog_format` change to take full effect.
sudo systemctl restart mariadb4
After changing the `binlog_format` on the master, you will likely need to re-initialize the replica or at least reset its replication state and re-establish replication from a consistent point. A common approach is to take a fresh dump from the master and restore it on the replica.