Bug #57166 Row based replication not able to convert from 'old' to 'new' DECIMAL
Submitted: 1 Oct 2010 12:58 Modified: 8 Oct 2010 14:05
Reporter: Hartmut Holzgraefe Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:mysql-5.1 (any) OS:Any
Assigned to: Zhenxing He CPU Architecture:Any

[1 Oct 2010 12:58] Hartmut Holzgraefe
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.
[8 Oct 2010 14:07] Omer Barnir
Setting to Won't fix 
The above mentioned workaround is the documented procedure for upgrading from 4.1.
See http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-previous-series.html
[13 Oct 2010 9:28] Konstantin Osipov
See Bug#57426 Mark tables containing old DECIMAL data type as corrupt in 5.5.