Error
Error Code:
1535
MariaDB Error 1535: Master-Slave Table Mismatch
Description
This error signifies a critical replication failure where the schema (table definition) of a specific table on the master server does not match its counterpart on the slave server. It typically occurs when row-based replication events attempt to apply changes to a table with an inconsistent structure, halting replication.
Error Message
Table definition on master and slave does not match: %s
Known Causes
4 known causesManual Schema Drift
A Data Definition Language (DDL) operation (e.g., ALTER TABLE, DROP COLUMN) was executed manually on either the master or slave, but not consistently applied to both.
Failed DDL Propagation
An ALTER TABLE statement or other schema change originating from the master failed to apply correctly or completely on the slave, leading to an inconsistent table definition.
Inconsistent Initial Setup
The slave server was initialized from a backup or dump that had a different table schema than the master server at the point of setup, or the initial data load was incomplete.
Replication Mode Issues with DDL
In specific scenarios, especially with mixed replication modes or older MariaDB versions, certain DDL operations might not propagate identically or be interpreted differently, causing schema divergence.
Solutions
4 solutions available1. Re-synchronize Table Schema easy
Forces the slave to re-evaluate and potentially correct its table schema to match the master.
1
On the slave server, connect to MariaDB.
mysql -u your_user -p2
Execute the `SET GLOBAL` command to re-evaluate the table definition. Replace `your_database` and `your_table` with the actual database and table names causing the mismatch. If you're unsure, you can try this for all tables in a database, or even all tables if the error is widespread.
SET GLOBAL slave_exec_mode = 'IDEMPOTENT';
SET GLOBAL slave_exec_mode = 'STRICT';
-- For a specific table:
FLUSH TABLES your_database.your_table;
-- Or to try for all tables in a database (use with caution):
-- SHOW TABLES FROM your_database;
-- For each table returned, execute:
-- FLUSH TABLES your_database.table_name;
3
Check the slave status to see if the error has been resolved. Look for `Slave_IO_Running` and `Slave_SQL_Running` to be 'Yes', and check `Last_SQL_Errno` and `Last_SQL_Error` for any new errors.
SHOW SLAVE STATUS\G2. Stop and Restart Slave Threads easy
A simple restart of the replication threads can sometimes clear transient inconsistencies.
1
On the slave server, connect to MariaDB.
mysql -u your_user -p2
Stop the slave threads.
STOP SLAVE;3
Check the slave status to ensure threads have stopped.
SHOW SLAVE STATUS\G4
Start the slave threads again.
START SLAVE;5
Monitor the slave status for resolution.
SHOW SLAVE STATUS\G3. Recreate Table on Slave medium
Forces the slave to rebuild the table structure from scratch based on the master's definition.
1
On the slave server, connect to MariaDB.
mysql -u your_user -p2
Stop the slave threads to prevent further writes during the process.
STOP SLAVE;3
Get the `CREATE TABLE` statement from the master server for the problematic table. Replace `your_database` and `your_table`.
mysql -u master_user -p -h master_host your_database -e "SHOW CREATE TABLE your_table;"4
On the slave, drop the existing table. **WARNING: This will delete all data in the table on the slave.**
DROP TABLE your_database.your_table;5
Execute the `CREATE TABLE` statement obtained from the master on the slave. Paste the output from the previous step.
CREATE TABLE `your_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;6
Start the slave threads again.
START SLAVE;7
Monitor slave status. The slave should now catch up on any missed writes for this table.
SHOW SLAVE STATUS\G4. Resynchronize Binary Log Position advanced
Corrects potential issues where the slave's binary log position is out of sync with the master.
1
On the slave server, connect to MariaDB.
mysql -u your_user -p2
Stop the slave threads.
STOP SLAVE;3
On the master server, find the current binary log file and position.
SHOW MASTER STATUS;4
On the slave, reset the slave configuration using the master's binary log file and position. Replace `master_log_file` and `master_log_pos` with the values obtained in the previous step.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.XXXXXX', MASTER_LOG_POS=YYYYYY;5
Start the slave threads.
START SLAVE;6
Monitor slave status. This might require the slave to re-read and re-apply events from the new position.
SHOW SLAVE STATUS\G