MySQL Bugs: #64098: table unusable after renaming key column in partitioned table

Bug #64098	table unusable after renaming key column in partitioned table
Submitted:	22 Jan 2012 8:45	Modified:	6 Jun 2012 12:24
Reporter:	Meir Maor	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.61	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	partition, rename

Description:
When renaming a column(from the partition function) in a partitioned table 
I get ERROR 1488,

At this the table becomes unusable, anything you try do with it will cause the error above. including trying to undo the rename

this is related to Bug #49975 but a more severe form.

How to repeat:
create table t (oldname varchar(32) primary key, i integer) partition by key(oldname);
Query OK, 0 rows affected (0.17 sec)

alter table t change oldname newname varchar(32) not null;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table

Suggested fix:
preferabbly change the reference in the partition function to the new name,
as a minimum prevent the operation as the table becomes unuseable,

MySQL 5.5 does not allow to rename, but otherwise table remains usable:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.20-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
Query OK, 0 rows affected (0.37 sec)

mysql> create table t (oldname varchar(32) primary key, i integer) partition by
    -> key(oldname);
Query OK, 0 rows affected (0.26 sec)

mysql> alter table t change oldname newname varchar(32) not null;
ERROR 1488 (HY000): Field in list of fields for partition function not found in table
mysql> select * from t;
Empty set (0.02 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `oldname` varchar(32) NOT NULL,
  `i` int(11) DEFAULT NULL,
  PRIMARY KEY (`oldname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (oldname) */
1 row in set (0.02 sec)

5.1.61 is still badly affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;Query OK, 0 rows affected (0.02 sec)

mysql> create table t (oldname varchar(32) primary key, i integer) partition by key(oldname);
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t change oldname newname varchar(32) not null;ERROR 1488 (HY000): Field in list of fields for partition function not found in table
mysql> select * from t;ERROR 1488 (HY000): Field in list of fields for partition function not found in table

Hello!

This bug has the same reason and have the same fix as bug #57985
"ONLINE/FAST ALTER PARTITION CAN FAIL AND LEAVE THE TABLE UNUSABLE",
which was fixed in 5.5. I am closing this report as a duplicate of
the #57985.