mySQL 5.5.17 / Workbench 5.2 synchronize fails (errno: 150)

Question

I'm trying to synchronize some model updates with an existing database on mySQL. It worked well previously but now i'm getting an error saying:

#1005 - Can't create table 'edu_dev.#sql-132f_eef09' (errno: 150)

I can't really get what's wrong. The update i'm trying to run was generated like below:

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';

ALTER TABLE `edu_dev`.`user` 
  ADD CONSTRAINT `fk_user_language1`
  FOREIGN KEY (`language` )
  REFERENCES `edu_dev`.`language` (`iso` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

..
..
..

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

edu_dev.language exists already with the column used for foreign key check. I have 50 other tables that should be updated too, so i would prefer not to do it manually.

score 0 · Accepted Answer · answered Feb 07 '12 at 11:03

Problem solved.

Make sure all the tables with foreign key constraints have the same collation. For some reason some have switched to latin1_swedish_ci while they had to be utf8_general_ci

There is also a good tool to change the collation of all tables of a database: Phoca Changing Collation

mySQL 5.5.17 / Workbench 5.2 synchronize fails (errno: 150)

1 Answers1