Bug #47163 Replication attribute promotion does not work
Submitted: 7 Sep 2009 7:39 Modified: 16 Feb 2010 9:57
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
Category:MySQL Cluster: Replication Severity:S3 (Non-critical)
Version:mysql-5.1-telco-7.0 OS:Any
Assigned to: Mats Kindahl CPU Architecture:Any
Tags: mysql-5.1.35, primary key, promotion, replication

[7 Sep 2009 7:39] Geert Vanderkelen
Attribute Promition does not work when replication changing the primary key field on Slave from TINYINT to INT while it is TINYINT on the Master.
The slave stops replicating with with following error:

Last_Errno: 1535
Last_Error: Table definition on master and slave does not match: Column 0 type mismatch - received type 1, test.t2 has type 3

Initial it was with NDB tables, but when altering the engine on the Slave to MyISAM, same error occured.
It also does not matter whether the PK field is AUTO_INCREMENT or not.

How to repeat:
1) Setup MySQL Replication.
2) Create table on Master (and replicated to Slave), e.g.:
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `txt` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `txt` (`txt`)
 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
3) On Slave do:
4) Insert new data on Master, observe error on Slave.

Suggested fix:
Documentation doesn't mention this restriction: so either a server (replication) bug, or documention has to be updated.
[7 Sep 2009 7:40] Geert Vanderkelen
Verified using mysql-5.1-telco-7.0 (MySQL Cluster 7.0 bzr).
[10 Sep 2009 5:03] Alexander Yu
I just tried this with the latest cluster 7.0.7 source release (5.1.35-ndb-7.0.7-cge-log) on OSX 10.6. 
Primary key attribute promotion worked for MyISAM and InnoDB tables but not NDB where I got the same error. 
Used two 1-datanode clusters as master/slave.
[17 Sep 2009 12:04] Mats Kindahl
Assuming that cluster has not changed this code, 5.1 does not implement promotion between "base" types of columns. This means that these promotions work:

CHAR(X)  ==> CHAR(X+N)

but these promotions do not work because the base type is different:


Note that some SQL types have the same base type, for example BINARY and CHAR.
[23 Oct 2009 7:14] Lars Thalmann
See also BUG#46584
[23 Oct 2009 8:32] Mats Kindahl
Changing title since the primary key has nothing to do with this.
[16 Feb 2010 9:57] Mats Kindahl
This has been implemented as WL#5151.
[1 Apr 2010 14:17] Jon Stephens
Documented as part of documenting WL#5151.