This is yet another Error 1005, errno 150 on create table thread. I have seen many of them, but none provide an answer to my question.
I have a database in mysql workbench, which is forward engineered and filled with data. Then, I create, from model view, a new table. Afterwards, I add a n:m relationship, between this new table and an existing table in my database.
Finally, I try to synchronize my database. The code generated, for the creation of the two new tables, is this:
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,ALLOW_INVALID_DATES';
CREATE TABLE IF NOT EXISTS `mydb`.`SEntences` (
`idSEntences` INT(11) NOT NULL,
PRIMARY KEY (`idSEntences`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin;
CREATE TABLE IF NOT EXISTS `mydb`.`Tips_has_SEntences` (
`Tips_idTips` VARCHAR(45) NOT NULL,
`SEntences_idSEntences` INT(11) NOT NULL,
PRIMARY KEY (`Tips_idTips`, `SEntences_idSEntences`),
INDEX `fk_Tips_has_SEntences_SEntences1_idx` (`SEntences_idSEntences` ASC),
INDEX `fk_Tips_has_SEntences_Tips1_idx` (`Tips_idTips` ASC),
CONSTRAINT `fk_Tips_has_SEntences_Tips1`
FOREIGN KEY (`Tips_idTips`)
REFERENCES `mydb`.`Tips` (`idTips`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
FOREIGN KEY (`SEntences_idSEntences`)
REFERENCES `mydb`.`SEntences` (`idSEntences`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
I get an error, with this log:
Executing SQL script in server
ERROR: Error 1005: Can't create table 'mydb.Tips_has_SEntences' (errno: 150)
CREATE TABLE IF NOT EXISTS `mydb`.`Tips_has_SEntences` (
`Tips_idTips` VARCHAR(45) NOT NULL,
`SEntences_idSEntences` INT(11) NOT NULL,
PRIMARY KEY (`Tips_idTips`, `SEntences_idSEntences`),
INDEX `fk_Tips_has_SEntences_SEntences1_idx` (`SEntences_idSEntences` ASC),
INDEX `fk_Tips_has_SEntences_Tips1_idx` (`Tips_idTips` ASC),
CONSTRAINT `fk_Tips_has_SEntences_Tips1`
FOREIGN KEY (`Tips_idTips`)
REFERENCES `mydb`.`Tips` (`idTips`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
FOREIGN KEY (`SEntences_idSEntences`)
REFERENCES `mydb`.`SEntences` (`idSEntences`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin
SQL script execution finished: statements: 3 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
Running mysql from terminal, with the SHOW ENGINE INNODB STATUS\G command, I get this output, from the most recent foreign key error field:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
141007 14:18:53 Error in foreign key constraint of table mydb/Tips_has_SEntences:
FOREIGN KEY (`Tips_idTips`)
REFERENCES `mydb`.`Tips` (`idTips`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
FOREIGN KEY (`SEntences_idSEntences`)
REFERENCES `mydb`.`SEntences` (`idSEntences`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
After researching my problem, I can't figure out what's wrong. All datatypes are the same, the foreign keys are on primary keys(which have primary indexes on them) etc. I researched various stack overflow answers on this error and reviewed this page, linked in one of them: MySQL Error Number 1005 Can’t create table ‘.\mydb#sql-328_45.frm’ (errno: 150) I searched as thoroughly as I could, but the answer could be in there still. In such a case, I am sorry.
Maybe it has to do something with the fact that the old table(Tips) already has data in it. Synchronizing a 1:n relationship, which adds a foreign key in the old table(Tips) though strangely works. if data were the problem, this wouldn't work, as I found in this post: How to add foreign key to MySQL table?
On the other hand, adding a 1:n relationship, which would require adding the foreign key of the old table(Tips) to a new one, generates the same errors and logs(with the exception of referring to a table like this: ‘.\mydb#sql-328_45.frm’).
I should note, that these problems occur for any old table of mine(a table already in the database with data). In addition, I am fairly sure I updated mysql workbench, after I filled the database with data, before I continued filling it and before trying to add these new tables. It is now version 6 and running in kubuntu, linux.
What is the problem here and how can it be treated? Is going to extremes, such as exporting database, dropping and importing it the only way? Even in such a case, what should I do exactly? Keep in mind this is a database with many data entries, about 100000.
If you should want any more information, I would provide them from here on.
Tips table creation code:
SHOW CREATE TABLE mydb.Tips;
CREATE TABLE `Tips` (
`idTips` varchar(45) CHARACTER SET latin1 NOT NULL,
`like_count` int(10) unsigned DEFAULT NULL,
`text` longtext CHARACTER SET latin1,
`created_at` bigint(19) unsigned DEFAULT NULL,
`url` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`todo_count` bigint(19) unsigned DEFAULT NULL,
`save_count` bigint(19) unsigned DEFAULT NULL,
`Venues_idVenues` varchar(45) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`idTips`),
KEY `fk_Tips_Venues1` (`Venues_idVenues`),
CONSTRAINT `fk_Tips_Venues1` FOREIGN KEY (`Venues_idVenues`) REFERENCES `Venues` (`idVenues`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
