| Bug #21704 | Renaming column does not update FK definition | ||
|---|---|---|---|
| Submitted: | 17 Aug 2006 22:21 | Modified: | 19 Jun 2010 0:09 |
| Reporter: | Kolbe Kegel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.11 | OS: | Linux (Linux) |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | bfsm_2007_10_18 | ||
[18 Aug 2006 13:36]
Heikki Tuuri
In 5.0, InnoDB refuses to process the column rename. Ingo has made ALTER TABLE more intelligent in 5.1. That may explain why 5.1 does not give an error.
There may be another bug associated with this: if MySQL does not tell InnoDB that the column name in the internal InnoDB data dictionary should be updated, then the table definition inside InnoDB will be out-of-sync with MySQL.
Assigning this bug to Marko.
heikki@127:~/mysql-5.1/client$ ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25
mysql> create table t1 (col1 int primary key) engine=innodb;
create table t2 (col1 int primary key, constraint `fk1` foreign key (col1)
references t1 (col1)) engine=innodb;
show create table t2\G
alter table t2 change col1 col2 int;
show create table t2\G
alter table t1 change col1 col3 int;
show create table t2\G
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (col1 int primary key, constraint `fk1` foreign key (col1)
-> references t1 (col1)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`col1` int(11) NOT NULL,
PRIMARY KEY (`col1`),
CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table t2 change col1 col2 int;
ERROR 1025 (HY000): Error on rename of './test/#sql-652_1' to './test/t2' (errno: 150)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`col1` int(11) NOT NULL,
PRIMARY KEY (`col1`),
CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql> alter table t1 change col1 col3 int;
ERROR 1025 (HY000): Error on rename of './test/#sql-652_1' to './test/t1' (errno: 150)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`col1` int(11) NOT NULL,
PRIMARY KEY (`col1`),
CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
[18 Aug 2006 13:47]
Heikki Tuuri
Yes, column names go out-of-sync in 5.1:
heikki@127:~/mysql-5.1/client$ ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta
...
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`col3` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
But innodb_table_monitor prints:
--------------------------------------
TABLE: name test/t1, id 0 352, columns 5, indexes 1, appr.rows 0
COLUMNS: col1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
INDEX: name PRIMARY, id 0 504, fields 1/3, uniq 1, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: col1 DB_TRX_ID DB_ROLL_PTR
FOREIGN KEY CONSTRAINT test/fk1: test/t2 ( col1 )
REFERENCES test/t1 ( col1 )
We must disable the intelligent ALTER TABLE until we have the internal RENAME COLUMN operation implemented inside InnoDB.
Ingo and Jan worked on the coordination of intelligent ALTER TABLE in MySQL/InnoDB in spring 2006, but this was not completed.
[12 Sep 2006 9:07]
Marko Mäkelä
The function mysql_alter_table() seems to lack a hook for updating data in the internal data dictionary of the storage engine. It only appears to rewrite the .frm file with the changed dictionary information. A quick fix might be to add a return value to handler::update_create_info() and to let the storage engine request a full table copy. A satisfactory fix would be to call a method in the storage engine when compare_tables() would set the FIELD_IS_RENAMED flag. I reclassified this as an MySQL Server bug, as it looks like the bug cannot be fixed on the storage engine side.
[13 Sep 2006 8:46]
Ingo Strüwing
Just to clarify what has been done with "smart" alter table: Jani created the function compare_tables(). This checks if alter table can be done without recreating the table. Simple things like renaming the table or a column are seen as changes in the frm file only. It was my task to make "fast" add/drop index possible. This is based on the decision done by compare_tables(). I extended this function just to tell which indexes don't match between the two table definitions. There has never been an attempt to do any kind of "smart" alter column yet. It seems that the "field" info does not contain a hint if it is part of a foreign key. Hence the comparison of fields sees a rename only. So it decides that it is safe just to change the frm file. This is a wrong assumption obviously. Because of the missing foreign key info in the "field" class we cannot rename a column in a "smart" way. Too bad. Instead we need to declare ALTER_TABLE_DATA_CHANGED if "field->flags|= FIELD_IS_RENAMED" is detected in compare_tables(). This should be the minimal change to fix this problem. Making "field" foreign key aware is too big of a change for a bug fix IMHO. It would probably include an SQL parser change, and a frm file parser change, if not even a frm file format change. One day MySQL will support foreign keys inherently. In the course of this implementation this problem should be fixed anyway. The idea with handler::update_create_info() is interesting. But I think that changing the handler interface at this stage in 5.1 is bad. Even if only a return value would be required, all implementations of this method must be changed. This includes implementations external of MySQL. And the input parameter does not include the new field and key lists. Changing this too would transform the method to something different finally. I conclude that a preliminary fix seems to be simple. This is not exactly a storage engine issue. But since I was involved in alter table issues before, you may ask Calvin if he accepts this bug for our group.
[13 Sep 2006 9:17]
Marko Mäkelä
Indeed, too bad that the handler interface is frozen in 5.1. In 5.2, this will need to be addressed in the handler interface, even if 5.2 had native foreign keys. Otherwise, there would emerge problems with existing InnoDB tables with InnoDB-internal foreign keys. We might also want to allow users to define storage-engine internal foreign keys for performance reasons.
[13 Sep 2006 9:24]
Marko Mäkelä
Assigning this away from me, as the fix will require changes outside of InnoDB. Ingo or Tim, please reassign to a suitable developer.
[27 Sep 2007 15:28]
Ingo Strüwing
Sergei suggests to look at table->file->check_if_incompatible_data(create_info, changes) to detect foreign key changes that the server can not yet handle with fast alter table.
Martin, according to "bk annotate" and "bk changes", you added this function call to compare_tables():
...
marty 1.297.1.1 /* Check if changes are compatible with current handler without a copy */
marty 1.297.1.1 if (table->file->check_if_incompatible_data(create_info, changes))
...
ChangeSet@1.2065.8.16, 2006-01-27 17:23:14+01:00, marty@linux.site +4 -0
Added possibillity to check what fields will get added indexes (ndb does currently not support indexes on disk stored f
ields), WL#1892
...
sql/sql_table.cc@1.297.1.1, 2006-01-27 17:22:59+01:00, marty@linux.site +18 -5
Added possibillity to check what fields will get added indexes (ndb does currently not support indexes on disk stored fields), WL#1892
I was not able to find the belonging commit email.
Please add a reference to the commit email here. Thereafter you may reassign the bug to me.
Regards
Ingo
[18 Oct 2007 6:59]
Martin Skold
Setting ALTER_TABLE_DATA_CHANGED if "field->flags|= FIELD_IS_RENAMED" is detected would break fast alter table for MyISAM. Would be better to pass Alter_info to check_if_incompatible_data, but this is a change to the handler API. What can be done without changing the interface is to add a new flag to the changes argument. Currently we use: #define IS_EQUAL_NO 0 #define IS_EQUAL_YES 1 #define IS_EQUAL_PACK_LENGTH 2 by adding #define IS_EQUAL_RENAME 4 this could be masked out in the ha_innobase::check_if_incompatible_data implementation. In 5.2 this is not a problem since there is a completely new interface for online alter table where the check_if_supported_alter is passed the complete new table, so a handler can do all checks if needed.
[18 Oct 2007 7:31]
Sergei Golubchik
Can ha_innodb::check_if_incompatible_data() simply iterate over all table->field[] and check if any of them has FIELD_IS_RENAMED set ?
[18 Oct 2007 7:40]
Martin Skold
The new fields are iterated by: List_iterator_fast<Create_field> new_field_it(alter_info->create_list); , but alter_info is not passed to check_if_incompatible_data. table referers to the old table before any changes. This is why I changed this in 5.2 so that the new table is created as a special temporary table that can be completely checked in check_if_supported_alter.
[18 Oct 2007 8:15]
Martin Skold
Ok, I see now, my mistake, the field flags of the original are actually changed, so yes HA_innoDB::check_if_incompatible_data should have all info as is.
[31 Oct 2007 14:56]
Martin Skold
Changing assignee since the fix is local to the handler (ha_innodb.cc).
[7 Nov 2007 11:39]
Marko Mäkelä
Is there a specification for handler::check_if_incompatible_data()? The method is not commented in handler.h or in ha_innodb.cc. The method was implemented in ha_innodb.cc by someone at MySQL AB. The file is jointly owned by MySQL AB and Innobase Oy.
[7 Nov 2007 13:48]
Heikki Tuuri
Martin, maybe the function ::check_if_incompatible_data() should return TRUE (or false?) if there is a RENAME TABLE, and the rename would rename a column that is in foreign key or in a referenced key? The question is where are the docs so that Marko is able to implement this? Regards, Heikki
[7 Nov 2007 13:48]
Heikki Tuuri
I meant RENAME COLUMN, sorry.
[7 Nov 2007 18:45]
Jani Tolonen
check_if_incompatible_data() does not have documentation, but I will add it to the source. In the meanwhile, I'll describe it right here in more detail: This function is called through mysql_alter_table() -> compare_tables() in the latter function. compare_tables() will first go through several checks that must pass, or the function will return 1 meaning that the altered table is not compatible with the original one and a full copy is needed. At the very end of compare_tables() it calls check_if_incompatible_data() if the tables (original and altered) may still be close enough to each other so that a copy is not needed. check_if_incompatible_data() goes into the table handler level, for which there exists a separate function for each handler. These are in ha_<handler_name>.cc and ha_<handler_name>.h files. So this function checks handler specific code whether the two tables are similar enough to each other that a copy not needed. It uses result from function is_equal() that is called in function compare_tables() before, in the middle. There is an "is_equal()" function for different datatypes (string, int, decimal..) in field.cc. The ultimate purpose for check_if_incompatible_data() is to find out if alter table can still be done without a full copy of the table. It uses all the information that has been gathered so far and checks on table handler level that it's still compatible. If it returns 1, tables are not compatible, ALTER_TABLE_DATA_CHANGED is set in variable need_copy_table and a full copy will be performed.
[12 Nov 2007 9:37]
Marko Mäkelä
In the test case from "how to repeat", ha_innobase::check_if_incompatible_data() is not called: create table t1 (col1 int primary key) engine=innodb; create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb; show create table t2\G alter table t2 change col1 col2 int; The ALTER TABLE results in ERROR 1025 (HY000): Error on rename of './test/#sql-2809_1' to './test/t2' (errno: 150) and InnoDB writes to the error log: 071112 11:31:26 InnoDB: Error: in ALTER TABLE `test`.`t2` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition. 071112 11:31:26 InnoDB: Error: table `test`.`t2` 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/refman/5.1/en/innodb-troubleshooting.html However, the statement alter table t2 change col1 col2 int not null; triggers a call to ha_innobase::check_if_incompatible_data(), which should prohibit the operation, but currently does not. I am working on a fix.
[12 Nov 2007 13:19]
Marko Mäkelä
Could someone please give me a hint how ha_innobase::check_if_incompatible_data() gets to see the changed column names?
The HA_CREATE_INFO structure seems to contain table-level information only.
The member variable handler::table_share looks promising, but unfortunately it seems to contain the original column names only:
(gdb) break 'ha_innobase::check_if_incompatible_data(st_ha_create_information*, unsigned int)'
create table t1 (col1 int primary key) engine=innodb;
create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb;
alter table t2 change col1 col2 int NOT NULL;
(gdb) p table_share->fieldnames
$5 = {count = 1, name = 0x0, type_names = 0x878fac0, type_lengths = 0x0}
(gdb) p *table_share->fieldnames.type_names
$6 = 0x878fad5 "col1"
(gdb) p **table_share->field
$8 = {_vptr.Field = 0x8586628, ptr = 0x878faa9 "", null_ptr = 0x0,
table = 0x0, orig_table = 0x0, table_name = 0x0,
field_name = 0x878fad5 "col1", comment = {str = 0x85e0286 "", length = 0},
key_start = {map = 1}, part_of_key = {map = 1},
part_of_key_not_clustered = {map = 1}, part_of_sortkey = {map = 1},
unireg_check = Field::NONE, field_length = 11, flags = 20483,
field_index = 0, null_bit = 0 '\0'}
Please, where can I find the column name "col2" or get a list of modified column names?
[12 Nov 2007 13:27]
Heikki Tuuri
Jani, can you please help Marko. Regards, Heikki
[11 Dec 2007 16:43]
Heikki Tuuri
Jani, can you please help Marko? Regards, Heikki
[11 Dec 2007 19:00]
Calvin Sun
Reply from Jani via email. Also, assign back to Marko:
I checked the code structure and there is no way to see the changed
columns down that far. There is not enough data passed to
check_if_incompatible_data() function and it would be nice to
avoid extending it for just this case, e.g. avoid sending the
table structure of the other table.
However, there is an alternative way to handle it;
In check_if_incompatible_data() you have access to info->used_fields,
which is a bitmap various options that had been used during create.
All the alternatives are in sql/handler.h, under
"/* Bits in used_fields */"
So the idea would be to add a new bitflag here, called
HA_CREATE_USED_NEW_COLUMN_NAME, currently it would be:
#define HA_CREATE_USED_NEW_COLUMN_NAME (1L << 22)
to be put right under the currently last one:
#define HA_CREATE_USED_PAGE_CHECKSUM (1L << 21)
Then we could use this new flag in sql/sql_table.cc, function
compare_tables(). There is already a loop in the middle that
goes through the fields and checks changes and there is even
a check for a changed field name already:
/* Check if field was renamed */
field->flags&= ~FIELD_IS_RENAMED;
if (my_strcasecmp(system_charset_info,
field->field_name,
new_field->field_name))
field->flags|= FIELD_IS_RENAMED;
Just change this to:
/* Check if field was renamed */
field->flags&= ~FIELD_IS_RENAMED;
if (my_strcasecmp(system_charset_info,
field->field_name,
new_field->field_name))
{
field->flags|= FIELD_IS_RENAMED;
create_info->used_fields|= HA_CREATE_USED_NEW_COLUMN_NAME;
}
Then you can check against this bitmap in ha_innodb.cc, under
check_if_incompatible_data() if
info->used_fields & HA_CREATE_USED_NEW_COLUMN_NAME
is set and return COMPATIBLE_DATA_NO for InnoDB in that case.
Can you please add this and test if it works as a solution for you?
[2 Jan 2008 14:09]
Marko Mäkelä
The fix suggested by Jani seems to work. MySQL/InnoDB will refuse to rename the column: ERROR 1025 (HY000): Error on rename of './test/#sql-1b01_1' to './test/t2' (errno: 150)
[2 Jan 2008 14:26]
Marko Mäkelä
Patch suggested by Jani Tolonen
Attachment: bug21704.patch (text/x-diff), 2.08 KiB.
[2 Jan 2008 14:27]
Marko Mäkelä
Can someone at MySQL please apply the attached patch and adjust the test cases as appropriate? The patch touches MySQL code, and InnoDB developers do not have commit access to the MySQL source tree.
[2 Jan 2008 14:39]
Heikki Tuuri
To fix this in a simple way, MySQL must tell the storage engine what columns it is going to rename. Then, if InnoDB notices there is a foreign key on that column, InnoDB will refuse.
[2 Jan 2008 14:41]
Heikki Tuuri
Currently, MySQL DOES NOT tell what column it is going to rename.
[3 Jan 2008 15:23]
Heikki Tuuri
A satisfactory fix for this requires MySQL to tell the engine the column name(s). Classifying this back to a 'Server' bug.
[15 Feb 2008 11:22]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/42343 ChangeSet@1.2552, 2008-02-15 14:14:09+03:00, anozdrin@quad. +2 -0 Add a test case for Bug#21704: Renaming column does not update FK definition.
[15 Feb 2008 11:22]
Alexander Nozdrin
Pushed into 5.1-runtime.
[15 Feb 2008 11:25]
Alexander Nozdrin
Just added a test case. The bugs is not repeated in 5.1.24-rc.
[3 Mar 2008 18:18]
Bugs System
Pushed into 5.1.24-rc
[3 Mar 2008 18:18]
Bugs System
Pushed into 6.0.5-alpha
[3 Mar 2008 20:34]
Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs. Renaming a column that appeared in a foreign key definition did not update that definition with the new column name. This occurred with both referenced and referencing tables.
[29 Mar 2008 23:52]
Jon Stephens
Also documented fix in 5.1.23-ndb-6.3.11.
[13 Jun 2008 19:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/47857 2662 Davi Arnaut 2008-06-13 Bug#21704: Renaming column does not update FK definition PLEASE DISREGARD, THIS IS A TENTATIVE PATCH: This patch introduces a regression as it will always cause a full table copy to take place when renaming columns. Or in other words, no more fast alter table. The problem was that renaming columns that appear in a foreign key definition does not cause the definition to be updated to the new column name. This occurred because the server was not notifying the storage engine of a possible incompatible change in column names. The solution is to pass a flag to the storage engine signaling that a column was renamed. With this flag the engine will be able to decide whether the change is compatible or not. If it's incompatible or the rename is not supported, the storage engine will issue a error. Patch subimitted Jani Tolonen and Marko Make
[16 Jun 2008 14:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/47907 2664 Davi Arnaut 2008-06-16 Bug#21704: Renaming column does not update FK definition The problem was that renaming columns that appear in a foreign key definition does not cause the definition to be updated to the new column name. This occurred because the server was not notifying the storage engine of a possible incompatible change in column names. The solution is to iterate over the table fields and check if any of the columns were renamed. If a column was renamed, inform that it's not a data compatible change. It's important to note that this modification will cause a full table copy to take place when renaming columns on a InnoDB table.
[24 Jun 2008 16:20]
Heikki Tuuri
http://bugs.mysql.com/bug.php?id=37538 is probably a duplicate of this.
[11 May 2009 14:29]
Samuel Vogel
I am using MySQL 5.1.34 and this Bug still occurs. The test case commited here http://lists.mysql.com/commits/42343 , still "corrupts" the foreign key!
[15 May 2009 14:36]
Harrison Fisk
I can confirm that this does indeed still affect 5.1.34. It looks like the documentation of this being fixed in 5.1.24 is incorrect. I will get that changed.
[15 May 2009 15:29]
Jon Stephens
Removed changelog entry stating that this had been fixed (looks like commit was never actually pushed to tree).
[15 May 2009 15:32]
Samuel Vogel
It would be nice if the commit would get added to the tree thou ;) Is this going to happen?
[15 May 2009 16:45]
Harrison Fisk
Here is a query you can use to find out if you are potentially affected by this bug. It finds orphaned foreign key pointers from the information schema: USE information_schema; -- If the table itself had a renamed column SELECT KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME FROM REFERENTIAL_CONSTRAINTS INNER JOIN KEY_COLUMN_USAGE USING (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME) LEFT JOIN COLUMNS USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) WHERE COLUMNS.COLUMN_NAME IS NULL UNION ALL -- if the parent has a renamed column SELECT KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME FROM REFERENTIAL_CONSTRAINTS INNER JOIN KEY_COLUMN_USAGE USING (CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME) LEFT JOIN COLUMNS ON (COLUMNS.TABLE_SCHEMA = KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA AND COLUMNS.TABLE_NAME = KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME AND COLUMNS.COLUMN_NAME= KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME) WHERE COLUMNS.COLUMN_NAME IS NULL;
[30 Jun 2009 2:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/77503 2988 Davi Arnaut 2009-06-29 Bug#21704: Renaming column does not update FK definition The problem is that renaming columns that are referenced by a foreign key constraint does not cause the definition to be updated to refer to the new column name. The solution is to not allow a column to be renamed if the column is being referenced by a foreign key constraint. This is implemented by iterating over the table's referenced foreign key constraints and checking whether a referenced column name is being renamed. If a rename is detected, the engine indicates that a full table copy should take place. During the table copy, foreign key checks will prevent the table from being altered. @ mysql-test/include/mix1.inc Add test case for Bug#21704 @ mysql-test/r/innodb_mysql.result Add test case result for Bug#21704 @ storage/innobase/handler/ha_innodb.cc Iterate over the referenced list checking whether a column is being renamed.
[1 Jul 2009 21:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/77710 2991 Davi Arnaut 2009-07-01 Bug#21704: Renaming column does not update FK definition The problem is that renaming columns that take part in a foreign key constraint does not cause the definition to be updated to refer to the new column name. The solution is to not allow a column to be renamed if the column is part of a foreign key constraint (either in the referencing or referenced table). This is implemented by iterating over the table's foreign key constraints and checking whether a column that is part of the constraint is being renamed. If a rename is detected, the engine indicates that a full table copy should take place. During the table copy, foreign key checks will prevent the table from being altered. @ mysql-test/include/mix1.inc Add test case for Bug#21704 @ mysql-test/include/mtr_warnings.sql Ignore table t3 errors. @ mysql-test/r/innodb_mysql.result Add test case result for Bug#21704 @ storage/innobase/handler/ha_innodb.cc Iterate over the foreign key lists checking whether a column is being renamed.
[1 Jul 2009 23:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/77714 2991 Davi Arnaut 2009-07-01 Bug#21704: Renaming column does not update FK definition The problem is that renaming columns that take part in a foreign key constraint does not cause the definition to be updated to refer to the new column name. The solution is to not allow a column to be renamed if the column is part of a foreign key constraint (either in the referencing or referenced table). This is implemented by iterating over the table's foreign key constraints and checking whether a column that is part of the constraint is being renamed. If a rename is detected, the engine indicates that a full table copy should take place. During the table copy, foreign key checks will prevent the table from being altered. @ mysql-test/include/mix1.inc Add test case for Bug#21704 @ mysql-test/include/mtr_warnings.sql Ignore table t3 errors. @ mysql-test/r/innodb_mysql.result Add test case result for Bug#21704 @ storage/innobase/handler/ha_innodb.cc Iterate over the foreign key lists checking whether a column is being renamed.
[2 Jul 2009 13:24]
Heikki Tuuri
Assigned Vasil as a reviewer.
[10 Jul 2009 11:36]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/78375 3021 Satya B 2009-07-10 Applying InnoDB snapshot 5.1-ss5488,part 4. Fixes BUG#21704 1. BUG#21704 - Renaming column does not update FK definition 2. Changes in mysql-test/include/mtr_warnings.sql so that the testcase for BUG#21704 doesn't fail because of the warnings generated. Detailed revision comments: r5488 | vasil | 2009-07-09 19:16:44 +0300 (Thu, 09 Jul 2009) | 13 lines branches/5.1: Fix Bug#21704 Renaming column does not update FK definition by checking whether a column that participates in a FK definition is being renamed and denying the ALTER in this case. The patch was originally developed by Davi Arnaut <Davi.Arnaut@Sun.COM>: http://lists.mysql.com/commits/77714 and was later adjusted to conform to InnoDB coding style by me (Vasil), I also added some more comments and moved the bug specific mysql-test to a separate file to make it more manageable and flexible. added: mysql-test/r/innodb_bug21704.result mysql-test/t/innodb_bug21704.test modified: mysql-test/include/mtr_warnings.sql storage/innobase/handler/ha_innodb.cc
[10 Jul 2009 11:57]
Davi Arnaut
Queued to 5.1-bugteam
[10 Jul 2009 12:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/78386 3022 Davi Arnaut 2009-07-10 Bug#21704: Renaming column does not update FK definition Remove commented-out test case. It has been moved to innodb_bug21704.test
[13 Jul 2009 17:48]
Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090713174543-cd2x7q1gi1hzoand) (version source revid:staale.smedseng@sun.com-20090710151930-6e6kq5tp7ux1rtbh) (merge vers: 5.1.37) (pib:11)
[13 Jul 2009 19:14]
Paul DuBois
Noted in 5.1.37 changelog. Renaming a column that appeared in a foreign key definition did not update the foreign key definition with the new column name. Setting report to NDI pending push into 5.4.x.
[4 Aug 2009 19:52]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:davi.arnaut@sun.com-20090710122405-38vxh52p3sf4r5hu) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 23:41]
Paul DuBois
Noted in 5.4.4 changelog.
[12 Aug 2009 23:02]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:19]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[9 Oct 2009 1:31]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[16 Jan 2010 1:11]
Paul DuBois
Noted in 5.5.0 changelog.
[16 Jan 2010 1:12]
Paul DuBois
Ignore previous comment.
[5 May 2010 15:22]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:25]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:04]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:32]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:00]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:32]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[15 Jun 2010 8:18]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:08]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:54]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:35]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[22 Jul 2010 2:37]
Roel Van de Paar
I am not completely following this bug, but this still seems to fail (at the least "a very confusing error message")? ---------- mysql> CREATE DATABASE bg5;USE bg5; SELECT VERSION(); SELECT @@innodb_version; Query OK, 1 row affected (0.01 sec) Database changed +------------------------------------+ | VERSION() | +------------------------------------+ | 5.1.47-enterprise-gpl-advanced-log | +------------------------------------+ 1 row in set (0.00 sec) +------------------+ | @@innodb_version | +------------------+ | 1.0.8 | +------------------+ 1 row in set (0.00 sec) mysql> create table t1 (col1 int primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> alter table t2 change col1 col2 int; ERROR 1025 (HY000): Error on rename of '.\bg5\#sql-b28_1' to '.\bg5\t2' (errno: 150) mysql> alter table t2 change col1 col2 int not null; ERROR 1025 (HY000): Error on rename of '.\bg5\#sql-b28_1' to '.\bg5\t2' (errno: 150) ---------- ---------- 100722 12:20:30 [Warning] Invalid (old?) table or database name '#sql-b28_1' 100722 12:20:30 InnoDB: Error: in ALTER TABLE `bg5`.`t2` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition. 100722 12:20:30 InnoDB: Error: table `bg5`.`t2` 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/refman/5.1/en/innodb-troubleshooting.html ---------- (repeated twice for each alter) Same problem when not using the plugin: ---------- mysql> SELECT @@innodb_version; ERROR 1193 (HY000): Unknown system variable 'innodb_version' mysql> alter table t2 change col1 col2 int; ERROR 1025 (HY000): Error on rename of '.\bg5\#sql-d94_1' to '.\bg5\t2' (errno: 150) ---------- Also: see bug #55465
Description: Renaming a column that appears in a Foreign Key definition does not update the Foriegn Key definition with the new column name. This occurs with both referenced and referencing tables. This could mean that it could be impossible to reload from a dump, as creating constraints against non-existent columns would surely not work very well. How to repeat: create table t1 (col1 int primary key) engine=innodb; create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb; show create table t2\G alter table t2 change col1 col2 int; show create table t2\G alter table t1 change col1 col3 int; show create table t2\G mysql 5.1.11-beta (root) [test]> create table t1 (col1 int primary key) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql 5.1.11-beta (root) [test]> create table t2 (col1 int primary key, constraint `fk1` foreign key (col1) references t1 (col1)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql 5.1.11-beta (root) [test]> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `col1` int(11) NOT NULL, PRIMARY KEY (`col1`), CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql 5.1.11-beta (root) [test]> alter table t2 change col1 col2 int; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1.11-beta (root) [test]> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `col2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`col2`), CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql 5.1.11-beta (root) [test]> alter table t1 change col1 col3 int; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1.11-beta (root) [test]> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `col2` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`col2`), CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The constraint is still checked, even though *neither* of the columns specified in the constraint definition exist: insert into t2 values (1); insert into t1 values (1); insert into t2 values (1); mysql 5.1.11-beta (root) [test]> insert into t2 values (1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `fk1` FOREIGN KEY (`col1`) REFERENCES `t1` (`col1`)) mysql 5.1.11-beta (root) [test]> insert into t1 values (1); Query OK, 1 row affected (0.00 sec) mysql 5.1.11-beta (root) [test]> insert into t2 values (1); Query OK, 1 row affected (0.00 sec) Suggested fix: Renaming columns should result in foreign key definitions being updated appropriately.