Hi

I am Trying to change column name in a table with no success :-(
This is how I created the table :

mysql> CREATE TABLE `Banks` (
-> `BankID` tinyint(2) unsigned zerofill NOT NULL default '00',
-> `BankName` varchar(50) NOT NULL default '',
-> `OpenDate` date NOT NULL default '0000-00-00',
-> `CloseDate` date default NULL,
-> `Datechanged` timestamp(14) NOT NULL,
-> `UserChanged` varchar(8) default NULL,
-> PRIMARY KEY (`BankID`,`OpenDate`,`Datechanged`)
-> ) TYPE=InnoDB
-> ;
Query OK, 0 rows affected (0.01 sec)

Now I want to change the column BankID to CustomerId
so I do the folowing ,
as BankID is part of the primary key I am tring
to drop the key first before I change column name:

mysql> alter table Banks drop primary key ;
ERROR 1025: Error on rename of './bp/#sql-efc_1' to './bp/Banks' (errno: 150)

I have tried a different approach , drop the Banks Table and create a new one:

mysql> drop table Banks ;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `Banks` (
-> `CustomerId` tinyint(2) unsigned zerofill NOT NULL default '00',
-> `BankName` varchar(50) NOT NULL default '',
-> `OpenDate` date NOT NULL default '0000-00-00',
-> `CloseDate` date default NULL,
-> `Datechanged` timestamp(14) NOT NULL,
-> `UserChanged` varchar(8) default NULL,
-> PRIMARY KEY (`CustomerId`,`OpenDate`,`Datechanged`)
-> ) TYPE=InnoDB ;
ERROR 1005: Can't create table './bp/Banks.frm' (errno: 150)


Any Idea Why ???
I have restarted the server , there is enought space in /tmp
which is define in my.cnf file for the tmp files (tmpdir=/tmp)
I have also tried starting MySql with mysqld_safe --tmpdir=/tmp

Thanks
Ronit

1 Try this: after you execute any SQL that doesn't work

2 You might try stopping the mysqld service and then `rm`ing the database yourself... although this would probably cause problems. (You would have to modify other tables in the DB etc)

3 Also if you're doing anything mission critical, I wouldn't store anything in /tmp unless you have permission set on it... there could be the possibility of data manipulation by another user...

Thanks for The advise ,

I found out that I forgot to drop a foreign key that refers to Table Banks which I was trying to change........

The odd thing is that I could drop the table with no errors even though
there was that foreign key ?!?

Thanks

Ronit

Note that this error will pop up if you are editing a field involved in any way in a foreign key relationship. If this field references any other, you might see this error. The reason you don't see the error when you are dropping the table is that the field is not being referenced in the FK relationship. It's weird, I know, but that is what I have experienced.

I had a similar problem. I was trying to add a field after the last one and got "error on rename of ... errno: 150". I fixed it as follows:
1. As my last field was a Foreign key I deleted the reference in order to make it no longer a Foreign key.
2. At this point I could add my new field. (successful)
3. Then I created the Foreign key reference again.