MySQL Bugs: #35874: Forward Engineer Wizard Message-Log: Error messages are assigned wrong

Bug #35874	Forward Engineer Wizard Message-Log: Error messages are assigned wrong
Submitted:	7 Apr 2008 9:55	Modified:	11 Jul 2008 8:29
Reporter:	Daniel Haas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.16a RC rev 2911	OS:	Windows (Vista)
Assigned to:	Maksym Yehorov	CPU Architecture:	Any

Description:
I have a table that I cannot get workbench to create using the forward engineer wizard.
The error that mysql gives is:

Error 1005: Can't create table '.\diloc\train_routes.frm' (errno: 150)

CREATE  TABLE IF NOT EXISTS `diloc`.`train_routes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `train_id` INT NOT NULL ,
  `date` DATE NULL ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `train`
    FOREIGN KEY (`train_id` )
    REFERENCES `diloc`.`trains` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

I searched the web for common errors related to this message, but none of the workarounds / corrections apply here:
 * I have only one AUTO_INCREMENT column
 * It IS defined as PRIMARY_KEY
 * The PRIMARY_KEY does not involve other columns (Sometimes INNODB problem)
 * The AUTO_INCREMENT column is the first defined column (also leads to problems
   sometims)

Since I normally created the table using workbench and did nothing fancy, I expected the table to be creatable using the wizard.

How to repeat:
Don't know exactly how to repeat this, but I will attach the file producing the error as reference.

Suggested fix:
The table should be creatable, or the MySQL Validation should show an error for the table, if there exists one. (No MySQL Validation error is shown when being run)

Just for reference:
If I generate this same table from propel (http://propel.phpdb.org) it works fine.
The SQL code that propel generates looks like this:

CREATE TABLE `train_routes`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`train_id` INTEGER  NOT NULL,
	`date` DATE,
	PRIMARY KEY (`id`),
	KEY `train_routes_I_1`(`id`),
	INDEX `FI_in` (`train_id`),
	CONSTRAINT `train`
		FOREIGN KEY (`train_id`)
		REFERENCES `trains` (`id`)
)Type=InnoDB;

Unfortunatly I'm no SQL expert, so I do not know why propel inserts the following statements:

  KEY `train_routes_I_1`(`id`),
  INDEX `FI_in` (`train_id`),

But it seems to do the trick, and works.

Further analyzing this problem it seems I was interpreting the message-log wrong. The displayed error did not come from the shown table, but the next table.
I thought that the error would be displayed AFTER the table relating to the problem, and not before.

Perhaps it would be useful to append a colon after the error message, so that it gets clearer that this error applies to the following table.

Or make some more new lines before the error message, because it just appears under the last table, and there is a new line after the error message, which suggests that the error belongs to the table above, and not the table beneath.

Or show the table name the error belongs to infront of the error message like this:
`diloc`.`train_run_messages`: Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key
That would be more obvious.

I changed the bug title to represent the error better, and still leave this open, as I find it quite a problem.

I think the issue is simply that the default engine on your server is MyISAM and the statement generated by workbench has no 'InnoDB' specification for the table?

Peter
(not a MySQL person)

Bug is fixed. Added validations. Model supplied contains errors. Inconsitent ROW_FORMAT for referenced tables, wrong type for foreign keys (error: 150) and incorrect order of indices in compound key.

errors are now covered with validation modules which point out the problems (tested in bzr rev 3106

An entry has been added to the 5.0.21 changelog:

Forward Engineer wizard fails to create table, but does not show any error messages.