Description:
We changed the internal representation of DECIMAL in MySQL 5.0, but DECIMAL columns 'inherited' from a previous 4.x instance are not reported by mysql_upgrade nor do REPAIR or an ALTER TABLE that does not explicitly touch the DECIMAL column change its internal type ('0' for the old, '246' for the new format). Only a dump/restore or an explicit ALTER TABLE ... CHANGE/MODIFY COLUMN ... changes the internal type from old to new.
This was probably meant to prevent behavior changes of existing installations, leads to an interesting problem with row based replication though if doing a
CREATE TABLE .. SELECT old_decimal_colum FROM old_table
With RBR this is split up into a statement based CREATE TABLE event followed by RBR insert events. The problem: on the master side the table sticks to the same DECIMAL type as old_decimal_column had in old_table. If that is still of the 'old' type (0) the created table and all insert events will also have the old type (0) instead of the new (246)
On the slave side the table is created with the new (246) type though as the slave only sees the request to create a DECIMAL column without internal type information.
Next it tries to insert the first replicated row into that table and fails with
[ERROR] Slave SQL: Table definition on master and slave does not match: Column ### type mismatch - received type 0, table_name has type 246, Error_code: 1535
How to repeat:
* create a MySQL 4.x instance
* create a table with a DECIMAL column, insert some dummy rows
CREATE TABLE t_4_1(id int primary key, foo DECIMAL);
INSERT INTO t_4_1 VALUES (1,23),(2,42);
* upgrade the instance to MySQL 5.1 or just copy the 4.1 tables .frm, .MYD and .MYI into an existing 5.1 instance,
* run mysql_upgrade and perform REPAIRs as suggested by the tool
* set up a 5.1 replication slave for the 5.1 master, using ROW as binlog format
* perform the following query on the master:
CREATE TABLE t_5_1 SELECT id, foo FROM t_4_1;
* see slave replication stop with
[ERROR] Slave SQL: Table definition on master and slave does not match: Column 1 type mismatch - received type 0, t_5_1 has type 246, Error_code: 1535
Suggested fix:
We have a function to convert from old to new format in the server somewhere, e.g. for converting values when doing a
"ALTER TABLE table MODIFY COLUMN old_format_col DECIMAL;"
When encountering a format mismatch for old/new DECIMAL format (row event contains a type 0 field while the slaves column type for that field is 246) convert the received column value from old decimal type 0 to new decimal type 246 using the existing conversion function.
Description: We changed the internal representation of DECIMAL in MySQL 5.0, but DECIMAL columns 'inherited' from a previous 4.x instance are not reported by mysql_upgrade nor do REPAIR or an ALTER TABLE that does not explicitly touch the DECIMAL column change its internal type ('0' for the old, '246' for the new format). Only a dump/restore or an explicit ALTER TABLE ... CHANGE/MODIFY COLUMN ... changes the internal type from old to new. This was probably meant to prevent behavior changes of existing installations, leads to an interesting problem with row based replication though if doing a CREATE TABLE .. SELECT old_decimal_colum FROM old_table With RBR this is split up into a statement based CREATE TABLE event followed by RBR insert events. The problem: on the master side the table sticks to the same DECIMAL type as old_decimal_column had in old_table. If that is still of the 'old' type (0) the created table and all insert events will also have the old type (0) instead of the new (246) On the slave side the table is created with the new (246) type though as the slave only sees the request to create a DECIMAL column without internal type information. Next it tries to insert the first replicated row into that table and fails with [ERROR] Slave SQL: Table definition on master and slave does not match: Column ### type mismatch - received type 0, table_name has type 246, Error_code: 1535 How to repeat: * create a MySQL 4.x instance * create a table with a DECIMAL column, insert some dummy rows CREATE TABLE t_4_1(id int primary key, foo DECIMAL); INSERT INTO t_4_1 VALUES (1,23),(2,42); * upgrade the instance to MySQL 5.1 or just copy the 4.1 tables .frm, .MYD and .MYI into an existing 5.1 instance, * run mysql_upgrade and perform REPAIRs as suggested by the tool * set up a 5.1 replication slave for the 5.1 master, using ROW as binlog format * perform the following query on the master: CREATE TABLE t_5_1 SELECT id, foo FROM t_4_1; * see slave replication stop with [ERROR] Slave SQL: Table definition on master and slave does not match: Column 1 type mismatch - received type 0, t_5_1 has type 246, Error_code: 1535 Suggested fix: We have a function to convert from old to new format in the server somewhere, e.g. for converting values when doing a "ALTER TABLE table MODIFY COLUMN old_format_col DECIMAL;" When encountering a format mismatch for old/new DECIMAL format (row event contains a type 0 field while the slaves column type for that field is 246) convert the received column value from old decimal type 0 to new decimal type 246 using the existing conversion function.