Bug #9802 foreign key checks disallow alter table

• Submitted: 11 Apr 2005 1:40
• Modified: 18 Jun 2010 23:14
• Reporter: Timothy Smith
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.1, 5.0
• OS: Any (all)
• Assigned to: Marko Mäkelä
• CPU Architecture: Any

[11 Apr 2005 1:40] Timothy Smith

Description:

See the "How to repeat" section.

How to repeat:

create table validnames ( name varchar(32), primary key (name) ) type=innodb;
insert into validnames (name) values ('heikki');
create table employee ( empname varchar(32), foreign key (empname) references validnames (name)) type=innodb;
insert into employee (empname) values ('heikki');
set foreign_key_checks=0;
alter table employee convert to character set utf8;    -- This fails
alter table employee convert to character set latin1;

Suggested fix:
Allow the ALTER TABLE, at least if foreign_key_checks is off.

[11 Apr 2005 4:47] Heikki Tuuri

Tim,

hmm..., if ...=0, we could relax the check if the new column type has a charset that is not comparison-compatible with the referenced column or the referencing column.

If the user forgets to converts the other table, it will bring strange INSERT failures, but will not crash the server, I think.

We cannot remove all checks in an ALTER, because that may bring crashes if the user converts in a wrong way.

Regards,

Heikki

[11 Apr 2005 14:25] Heikki Tuuri

Reassigning this to Marko, to make sure this gets fixed in 4.1.12.

[13 Apr 2005 9:32] Marko Mäkelä

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be included in MySQL 4.1.12 and probably 5.0.5.

[5 May 2010 15:27] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 1:27] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 6:14] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:42] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:10] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:26] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:21] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:09] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:49] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)