MySQL Bugs: #19266: Alter table fails where it didn't before.

Bug #19266	Alter table fails where it didn't before.
Submitted:	21 Apr 2006 16:26	Modified:	5 May 2006 5:55
Reporter:	Nathan Rowden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.19/4.1BK/5.0BK/5.1BK	OS:	Linux (Linux & OS X)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
In version 5.0.15 alter table works as expected, later versions of MySQL fail when the alter table command is issued.

How to repeat:
CREATE TABLE `parent` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `child` (
  `parentid` int(11) NOT NULL,
  PRIMARY KEY  (`parentid`),
  CONSTRAINT `silly_constraint` FOREIGN KEY (`parentid`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into parent values('5'),('10'),('15'),('20');

insert into child values(10),(15);

set foreign_key_checks=0;

alter table parent change column id id varchar(12);

alter table child change column parentid parentid varchar(12);

set foreign_key_checks=1;

Suggested fix:
Have disabling foreign key checks disable foreign key checks...?

The only workaround for this is to reload the database, or go through and manually remove all foreign key references, then perform the alter tables, then recreate all the foreign key references.

Thank you for the bug report.
This is probably a side effect of the bug:
http://bugs.mysql.com/bug.php?id=13778
Bug #13778 If FOREIGN_KEY_CHECKS=0, one can create inconsistent FOREIGN KEYs

Version: '5.1.10-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
060422 19:39:33  InnoDB: Error: in ALTER TABLE `db55/parent`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
060422 19:39:33  InnoDB: Error: table `db55/parent` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060422 19:39:33  InnoDB: Error: in ALTER TABLE `db55/child`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
060422 19:39:33  InnoDB: Error: table `db55/child` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html

If that isn't more allowed then should be documented in the alter table section.

Consider this order of executing the commands as well:

-------------
set foreign_key_checks=0;

CREATE TABLE `child` (
  `parentid` int(11) NOT NULL,
  PRIMARY KEY  (`parentid`),
  CONSTRAINT `silly_constraint` FOREIGN KEY (`parentid`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into child values(10),(15);

alter table child change column parentid parentid varchar(12);

CREATE TABLE `parent` (
  `id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into parent values('5'),('10'),('15'),('20');

alter table parent change column id id varchar(12);

set foreign_key_checks=1;

------------

Works fine in 5.0.15, but will not allow creation of parent table in 5.0.19.

I think this is intentional. If just the user would perform just the first ALTER, he would end up with a nonsensical reference of an INT to a CHAR column. That could for ON UPDATE CASCADE constraints corrupt one of the tables.

I am marking this as 'Not a bug'.

I understand that if you made a mistake you could end up with nonsensical references, but if this is truly intentional then how is a person supposed to change a column type in an InnoDB table that other tables are referencing?

It is my contention that having a switch to disable foreign key checks that doesn't really turn off foreign key checks is nonsensical.

According to the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

"...Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations."

Setting foreign key checks to 0 is no longer useful for ignoring foreign key constraints during ALTER TABLE operations.

Verified just as described on 5.0.21-BK:

mysql> CREATE TABLE `parent` (
    ->   `id` int(11) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `child` (
    ->   `parentid` int(11) NOT NULL,
    ->   PRIMARY KEY  (`parentid`),
    ->   CONSTRAINT `silly_constraint` FOREIGN KEY (`parentid`) REFERENCES `parent`
    -> (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into parent values('5'),('10'),('15'),('20');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into child values(10),(15);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table parent change column id id varchar(12);
ERROR 1025 (HY000): Error on rename of './test/#sql-19bb_5' to './test/parent' (errno: 150)

If it is intended behaviour, it should be explained at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. So, it is either a bug (I think so, what that "set foreign_key_checks=0" is for, if it is intended behaviour. All foreign keys should be checked while performing "set foreign_key_checks=1"!) or a documentation request.

This also prevents loading from mysqldump files.

Hi!

We cannot allow nonsensical constraints INT -> CHAR since they can lead to crashes and corruption.

I am setting this to 'Documenting'. The documentor should add to the end of:

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
"
This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations. 
"

a sentence:

However, even if FOREIGN_KEY_CHECKS=0, InnoDB does not allow creation of a foreign key constraint where a column references a non-matching column type.

Regards,

Heikki

Just to make sure I'm on the same page as everyone else:

1) Changing a character set (that could lead to corrupt data) is ok, and changing a column type isn't.

2) Filling a table with data that isn't contained in a referenced table is ok, and changing a column type isn't.

3) In order to change the schema of a tables that have foreign key constraints you must follow one of two paths:

A) Path one
i) Dump the database using mysqldump.
ii) Change the schema by hand.
iii) Drop the original database.
iv) Run the mysqldump file to recreate the database with the changed schema.

B) Path two
i) Use an ALTER TABLE DROP FOREIGN KEY command on each of the tables referencing the table need to change.
ii) Run the ALTER TABLE CHANGE COLUMN command on the now unreferenced table.
iii) Run the ALTER TABLE CHANGE COLUMN command on each of the tables referencing the table that was changed.
iv) Run ALTER TABLE ADD CONSTRAINT commands on each of the other tables to recreate the foreign keys that were dropped.

[sarcasm]
My wife would like to thank in advance the MySQL/InnoDB team(s) for the robotic husband that she will be able to afford now thanks to all the overtime that I will be forced to work. My only regret is that my newborn child will come to think of the robot version of me as her daddy rather than myself. But I suppose it's a small price to pay to ensure that no idiots out there forget to change one or two of their momentarily nonsensical foreign key constraints while in the process of altering their database's schema.

I'm sure that Path one will work particularly well for enterprise shops with large multi-gigabyte databases. Path two on the other hand is clearly a more error free technique than just disabling foreign key checking and altering each table. Especially in highly interrelated databases with meaningful foreign key names such as ibfk_1, ibfk_2, ibfk_3, etc...
[/sarcasm]

Nathan,

I have spent various hours figuring out crashes that resulted from a foreign key constraint referencing a column of a non-matching type. By blocking such ALTERs I am relieving my own job burden!

Regards,

Heikki

Documented in the 4.1, 5.0 and 5.1 InnoDB section.

Heikki,

I didn't realize I was arguing with "the creator of the InnoDB transactional storage engine in MySQL".  I suppose if anyone has the right to say "it's not a bug, it's a feature" it's you.  Can you please tell me what source files to look at if I want to attempt and restore the previous behaviour for my installation?

Regards,

Nate

Nathan,

in /innobase/dict/dict0dict.c, it is the call cmp_types_are_equal() that checks the types match. If you remove it, then it might work.

You are right that it is risky that the current implementation DOES allow changing the charset when FOREIGN_KEY_CHECKS=0. That can lead to mild table corruption in ON UPDATE CASCADE if the user forgets to change both the parent and the child table. The perfect solution would be to check that the user remembers to change both, and print a big error message if he does not.

Regards,

Heikki

static
dict_index_t*
dict_foreign_find_index(
/*====================*/
                                /* out: matching index, NULL if not found */
        dict_table_t*   table,  /* in: table */
        const char**    columns,/* in: array of column names */
        ulint           n_cols, /* in: number of columns */
        dict_index_t*   types_idx, /* in: NULL or an index to whose types the
                                   column types must match */
        ibool           check_charsets) /* in: whether to check charsets.
                                        only has an effect if types_idx !=
                                        NULL. */
{
#ifndef UNIV_HOTBACKUP
        dict_index_t*   index;
        const char*     col_name;
        ulint           i;

        index = dict_table_get_first_index(table);

        while (index != NULL) {
                if (dict_index_get_n_fields(index) >= n_cols) {

                        for (i = 0; i < n_cols; i++) {
                                col_name = dict_index_get_nth_field(index, i)
                                                        ->col->name;
                                if (dict_index_get_nth_field(index, i)
                                                ->prefix_len != 0) {
                                        /* We do not accept column prefix
                                        indexes here */

                                        break;
                                }

                                if (0 != innobase_strcasecmp(columns[i],
                                                                col_name)) {
                                        break;
                                }

                                if (types_idx && !cmp_types_are_equal(
                                     dict_index_get_nth_type(index, i),
                                     dict_index_get_nth_type(types_idx, i),
                                     check_charsets)) {

                                        break;
                                }