What does mysql error 1025 HY000 Error on rename of foo errorno 150 mean

Question

I tried this in mysql   mysql gt  alter table region drop column country id    And got this   ERROR 1025  HY000   Error on rename of    product  sql-14ae 81  to    product region   errno  150    Any ideas  Foreign key stuff

User · Accepted Answer

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

But the tricky part is that you can't drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:

SHOW CREATE TABLE region;

This should show you the name of the index, something like this:

CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION

Now simply issue an:

alter table region drop foreign key region_ibfk_1;

And finally an:

alter table region drop column country_id;

And you are good to go!

User · Answer

There is probably another table with a foreign key referencing the primary key you are trying to change   To find out which table caused the error you can run SHOW ENGINE INNODB STATUS and then look at the LATEST FOREIGN KEY ERROR section  Use SHOW CREATE TABLE categories to show the name of constraint   Most probably it will be categories ibfk 1  Use the name to drop the foreign key first and the column then   ALTER TABLE categories DROP FOREIGN KEY categories ibfk 1  ALTER TABLE categories DROP COLUMN assets id

User · Answer

It is indeed a foreign key error  you can find out using perror   shell  perror 150 MySQL error code 150  Foreign key constraint is incorrectly formed   To find out more details about what failed  you can use SHOW ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section it contains details about what is wrong     In your case  it is most likely cause something is referencing the country id column

User · Answer

Simply run the alter table query using  KEY  instead of  FOREIGN KEY  in the drop statement  I hope it will help to solve the issue  and will drop the foreign key constraint and you can change the table columns and drop the table   ALTER TABLE slide image sub DROP  KEY  FK slide image sub    here in DROP KEY instead of DROP FOREIGN KEY   hope it will help   Thanks

User · Answer

I had a similar issues once  I deleted the primary key from TABLE A but when I was trying to delete the foreign key column from table B I was shown the above same error   You can t drop the foreign key using the column name and to bypass this in PHPMyAdmin or with MySQL  first remove the foreign key constraint before renaming or deleting the attribute

User · Answer

I know  this is an old post  but it s the first hit on everyone s favorite search engine if you are looking for error 1025   However  there is an easy  hack  for fixing this issue   Before you execute your command s  you first have to disable the foreign key constraints check using this command   SET FOREIGN KEY CHECKS   0    Then you are able to execute your command s    After you are done  don t forget to enable the foreign key constraints check again  using this command   SET FOREIGN KEY CHECKS   1    Good luck with your endeavor

User · Answer

averageRatings  FOREACH groupedRatings GENERATE group AS movieID  AVG ratings rating  AS avgRating  COUNT ratings rating  AS numRatings   If you are using any command like above you must use group in small letters  This may solve your problem it solved mine  At least in PIG script

User · Answer

You can get also get this error trying to drop a non-existing foreign key  So when dropping foreign keys  always make sure they actually exist   If the foreign key does exist  and you are still getting this error try the following   SET  OLD UNIQUE CHECKS   UNIQUE CHECKS  UNIQUE CHECKS 0  SET  OLD FOREIGN KEY CHECKS   FOREIGN KEY CHECKS  FOREIGN KEY CHECKS 0  SET  OLD SQL MODE   SQL MODE  SQL MODE  TRADITIONAL        Drop the foreign key here   SET SQL MODE  OLD SQL MODE  SET FOREIGN KEY CHECKS  OLD FOREIGN KEY CHECKS  SET UNIQUE CHECKS  OLD UNIQUE CHECKS    This always does the trick for me

User · Answer

I got this error with MySQL 5 6 but it had nothing to do with Foreign keys  This was on a Windows 7 Professional machine acting as a server on a small LAN    The client application was doing a batch operation that creates a table fills it with some external data then runs a query joining with permanent tables then dropping the  temporary  table  This batch does this approximately 300 times and this particular routine had been running week in week out for several years when suddenly we get the Error 1025 Unable to rename problem at a random point in the batch   In my case the application was using 4 DDL statements a CREATE TABLE followed by 3 CREATE INDEX  there is no foreign key  However only 2 of the indexes actually get created and the actual table  frm file was renamed  at the point of failure   My solution was to get rid of the separate CREATE INDEX statements and create them using the CREATE TABLE statement  This at the time of writing has solved the issue for me and my help someone else scratching their head when they find this thread

User · Answer

Doing  SET FOREIGN KEY CHECKS 0    before the Operation can also do the trick

User · Answer

Take a look in error file for your mysql database  According to Bug  26305 my sql do not give you the cause  This bug exists since MySQL 4 1  -

User · Answer

I d guess foreign key constraint problem   Is country id used as a foreign key in another table   I m not DB guru but I think I solved a problem like this  where there was a fk constraint  by removing the fk  doing my alter table stuff and then redoing the fk stuff   I ll be interested to hear what the outcome is - sometime mysql is pretty cryptic

User · Answer

In my case  I was using MySQL workbench and I faced the same issue while dropping one of my columns in a table  I could not find the name of the foreign key  I followed the following steps to resolve the issue    Rt  click on your schema and select  schema inspector   This gives you various tables  columns  indexes  ect  Go to the tab named  Indexes  and search the name of the column under the column named  Column   Once found check the name of the table for this record under the column name  Table   If it matches the name of the table you want  then note down the name of the foreign key from the column named  Name   Now execute the query   ALTER table tableNamexx DROP KEY foreignKeyName  Now you can execute the drop statement which shall execute successfully

User · Answer

If you are using a client like MySQL Workbench  right click the desired table from where a foreign key is to be deleted  then select the foreign key tab and delete the indexes   Then you can run the query like this   alter table table name drop foreign key col name

User · Answer

It is indeed a foreign key error  you can find out using perror   shell  perror 150 MySQL error code 150  Foreign key constraint is incorrectly formed   To find out more details about what failed  you can use SHOW ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section it contains details about what is wrong     In your case  it is most likely cause something is referencing the country id column

[mysql] What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

Examples related to mysql

Examples related to mysql-error-1025