MySQL #1025 Error

Question

I get a #1025 error when this query is run.

SQL query:

ALTER TABLE  `routes` CHANGE  `end_loc`  `end_loc` VARCHAR( 500 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

MySQL said:

#1025 - Error on rename of './ridestr/#sql-1f44_26eeb' to './ridestr/routes' (errno: 150) 

Which is funny because I set up this query in PHP MyAdmin…I don't know why I'm getting a foreign key error…I'm just trying to change something from int to varchar

Can anyone help?

Answer 1

If the routes.end_loc column is being used in a foreign key, then you can't change its data type. You will need to drop the foreign key, change the data type of both the parent and child columns, and then add the foreign key again.

For details on this error, run SHOW ENGINE INNODB STATUS\G and then look at the LATEST FOREIGN KEY ERROR section.

Answer 2

but it is very hard if you want to change in the whole charset to utf8 of all tables here is what i did

     1 export the database (all tables) and open by notepad

     2 replace all "latin1" by utf8 and save

     3 then try to restore using mysqlAdministrator but it says wrong

     4 so try restoring again but you have to select ignore errors radio button
     5 it tries to restore some tables 

     6 then you have to restore again until no error message 

i know this is the hard way but i am sure this will work for you too