Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unhappy Unanswered: Error while trying to alter table (errno: 150)

    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

  2. #2
    Join Date
    Sep 2003
    Posts
    69
    1 Try this:
    Code:
    SHOW ERRORS
    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...
    Last edited by trieder; 09-26-03 at 00:27.

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    Thanks

    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

  4. #4
    Join Date
    Nov 2009
    Posts
    1
    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.

  5. #5
    Join Date
    Mar 2010
    Posts
    1

    error on rename of ... errno: 150

    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.

    Quote Originally Posted by Ronit View Post
    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

  6. #6
    Join Date
    Jun 2012
    Location
    Denver, CO, USA
    Posts
    20

    Causes of Errno 150 in MySQL

    For those viewing this thread such as @Ronit, here is an extensive list of causes for errno 150.

    MySQL Foreign Key Errors and Errno 150

    The most useful thing to do (if you have SUPER privileges) is to use:

    SHOW ENGINE INNODB STATUS;

    That will give you a much more detailed explanation of what went wrong. If you don't have SUPER privileges, you'll just have to go down the list of causes in the link.

  7. #7
    Join Date
    Jan 2013
    Posts
    1

    Fixing errno 150

    The error is from FKs, of course.

    Drop them using something like:
    "ALTER TABLE table_name DROP FOREIGN KEY fk_column_id"

    Reference: justbugs.wordpress.com/2013/01/08/fixing-sql-errno-150/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •