Error
Error Code:
1736
MariaDB Error 1736: Table Definition Mismatch
Description
This error indicates that an operation, such as creating a view, executing a complex join, or performing replication, failed because the underlying tables involved have incompatible schema definitions. It typically arises when column types, lengths, character sets, or other structural attributes do not match across tables expected to be compatible.
Error Message
Tables have different definitions
Known Causes
4 known causesIncompatible Column Definitions
Columns with the same name across different tables have differing data types, lengths, or NULL constraints, making them incompatible for the intended operation.
Character Set or Collation Mismatch
Tables involved in an operation use different character sets or collations for comparable columns, leading to definition conflicts during operations.
Schema Drift in Replication
In a replication setup, the schema of a table on the source server has diverged from its counterpart on the replica server, causing replication to fail.
Outdated View or Stored Procedure Definitions
A view or stored procedure refers to tables whose underlying schema has been altered since the view/procedure was created, resulting in a definition mismatch upon execution.
Solutions
3 solutions available1. Synchronize Table Definitions medium
Ensures all replicated replicas have identical table structures.
1
Identify the master server and the replica server experiencing the mismatch.
2
On the master server, retrieve the `CREATE TABLE` statement for the problematic table.
SHOW CREATE TABLE your_database.your_table_name;3
On the replica server, compare the output from the master with the current definition of the table.
SHOW CREATE TABLE your_database.your_table_name;4
If there's a discrepancy, modify the table definition on the replica to match the master. This might involve `ALTER TABLE` statements. For example, if a column is missing on the replica:
ALTER TABLE your_database.your_table_name ADD COLUMN new_column_name VARCHAR(255) AFTER existing_column;5
If the mismatch is due to a missing index, add it:
CREATE INDEX index_name ON your_database.your_table_name (column_name);6
After making changes on the replica, restart the replica's SQL thread.
STOP REPLICA THREAD SQL_TICKETS; START REPLICA THREAD SQL_TICKETS;7
Verify the `Seconds_Behind_Master` on the replica is decreasing and eventually reaches 0.
SHOW REPLICA STATUS\G2. Rebuild Replica Table from Master Dump medium
A more drastic approach to ensure exact replication by re-creating the table.
1
On the master server, stop writes to the problematic table to ensure data consistency during the dump.
FLUSH TABLES WITH READ LOCK;2
In a separate terminal on the master, dump the `CREATE TABLE` statement and the table data.
mysqldump -u your_user -p --no-data --skip-triggers your_database your_table_name > table_schema.sql
mysqldump -u your_user -p --no-create-info --skip-triggers your_database your_table_name > table_data.sql3
On the master, release the read lock.
UNLOCK TABLES;4
On the replica server, drop the existing table.
DROP TABLE your_database.your_table_name;5
Apply the schema to the replica.
source table_schema.sql6
Apply the data to the replica.
source table_data.sql7
Restart the replica's SQL thread.
STOP REPLICA THREAD SQL_TICKETS; START REPLICA THREAD SQL_TICKETS;8
Verify replication status.
SHOW REPLICA STATUS\G3. Check for Unapplied Events easy
Investigates if the replica is lagging and missed DDL statements.
1
On the replica server, check the replica status for any errors or large `Seconds_Behind_Master`.
SHOW REPLICA STATUS\G2
Examine `Last_SQL_Errno` and `Last_SQL_Error` for clues. If there are errors, they might indicate the cause of the mismatch.
3
If `Seconds_Behind_Master` is high, it suggests the replica is struggling to keep up. Investigate the replica's performance or potential network issues.
4
If you suspect DDL statements are not being applied correctly, consider manually applying them on the replica after ensuring they are safe and idempotent.