MySQL Bugs: #47163: Replication attribute promotion does not work

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:	None
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

Description:
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.:
 CREATE TABLE `t2` (
  `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:
 ALTER TABLE t2 MODIFY `id` INT;
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.

Verified using mysql-5.1-telco-7.0 (MySQL Cluster 7.0 bzr).

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.

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:

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

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

INT ==> BIGINT
TINYINT ==> INT

Note that some SQL types have the same base type, for example BINARY and CHAR.

See also BUG#46584

Changing title since the primary key has nothing to do with this.

This has been implemented as WL#5151.

Documented as part of documenting WL#5151.