MySQL Bugs: #901: char => varchar promotion considered harmful

Bug #901	char => varchar promotion considered harmful
Submitted:	23 Jul 2003 11:13	Modified:	23 Jul 2003 13:53
Reporter:	Matthias Urlichs	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.0	OS:	Linux (Linux 2.4.21)
Assigned to:		CPU Architecture:	Any

Description:
A table with a varchar column promotes all other char columns to varchar too.
Unfortunately, if column A in table B has a foreign key constraint on column C in table D, but one of the columns ends up being promoted to VARCHAR, then the foreign key constraint does not fail, but a subsequent INSERT doesn't work.

How to repeat:
DROP TABLE IF EXISTS ONE;
CREATE TABLE ONE (
  ID int(11) NOT NULL default '0',
  X1 char(4) NOT NULL default '',
  BADCOL varchar(4) NOT NULL default '',
  UNIQUE KEY LAW2 (ID),
  UNIQUE KEY X1 (X1)
) TYPE=InnoDB CHARSET=latin1;

INSERT INTO ONE (ID,X1) VALUES (10,'EUR');

DROP TABLE IF EXISTS TWO;
CREATE TABLE TWO (
  ID int(11) NOT NULL default '0',
  X1 char(4) NOT NULL default '',
  KEY I_TWO_X1 (X1),
  UNIQUE KEY X1 (ID),
  FOREIGN KEY (X1) REFERENCES ONE (X1) ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1;

INSERT INTO TWO ( X1,ID ) VALUES ( 'EUR',1);

The insert fails. Remove BADCOL and it works.

Suggested fix:
Turn off the char => varchar auto-promotion.
I don't think it makes sense any more.

Hi!

The FOREIGN KEY problem has been fixed in 4.0.14.

Monty said that the CHAR => VARCHAR silent promotion will probably be disabled in 5.x. I agree that it is causes a lot of confusion.

* Fixed a bug: if in a FOREIGN KEY with an UPDATE CASCADE clause the parent column was of a different internal storage length than the child column, then a cascaded update would make the column length wrong in the child table and corrupt the child table. Because of MySQL's 'silent column specification changes' a fixed-length CHAR column can change internally to a VARCHAR and cause this error. 

* Fixed a bug: if a non-latin1 character set was used and if in a FOREIGN KEY the parent column was of a different internal storage length than the child column, then all inserts to the child table would fail in a foreign key error. 

Regards,

Heikki

> The FOREIGN KEY problem has been fixed in 4.0.14.

Ah. Thank you. You should update 4.1 then -- it's somewhat uncool that a 4.0 bugfix is missing from 4.1.

4.0 -> 4.1 merge happens at irregular intervals, but also always before the release.
So 4.1.1. is guaranteed to have all the bugfixes from latest 4.0 tree