MySQL Bugs: #34320: Cannot create foreign key relationship (seems to be related to the table name)

Bug #34320	Cannot create foreign key relationship (seems to be related to the table name)
Submitted:	5 Feb 2008 18:26	Modified:	19 Feb 2008 17:24
Reporter:	mike avergun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:		OS:	Any
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Regardless of the tool used there is seems to be problem with the table name "policy". I get all sort of failures on creation of the table or on creation of foreign key relation to the table (I don't think the column name seems to be the problem, but it is possible that the failure related to the column name "policy_id"). All the failures are sporadic - mean I never know when next modification is going to fail.

How to repeat:
1. Create couple of tables DB
2. Add table called "policy" with primary key "policy_id"
3. Create foreign key relationship with "policy_id"

I am including screen shot of failure and screen shot of the table

failed table mod

Attachment: bug2.doc (application/msword, text), 35.00 KiB.

table with fk to policy

Attachment: bug3.doc (application/msword, text), 62.50 KiB.

Thank you for a problem report. Please, send the exact version of MySQL server anf GUI Tools used. Have you tried to create tables in mysql command line client?

Please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for this `policy` table.

Server 5.045 CE QB 1.2.12
following script will fail like a clock. strange behavior if you'll move 'policy' table out or to the different location - other tables will fail. Script generated by exporting db from MySQL Workbench 5.0.12 SE. At some point sever let me create this table if I drop all fk from it, but then I cannot restore fk relationsnither from this table not to it.

script that display the problem

Attachment: soemon_mod3.sql (application/octet-stream, text), 13.69 KiB.

Mike,

what does SHOW INNODB STATUS\G print as the latest foreign key error?

Regards,

Heikki

show engine innodb status;
does not print anything, I tried both QB and cmd to get the status, however I am modifying tables only in QB.
Did you try to run my script? I have success rate of failure of 100%.

Mike,

this is not a bug.

Looking to your script:

This means, that you activated strict mode:
...
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
...
...
CREATE  TABLE `soemon4`.`policy` (
  `policy_id` INT(11) NOT NULL ,
  `name` VARCHAR(255) NULL DEFAULT NULL ,
  `description` VARCHAR(255) NULL DEFAULT NULL ,
  `policy_definition_id` INT(11) NOT NULL ,
  `subject` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`policy_id`, `policy_definition_id`) ,
  INDEX fk_policy_permitions (`policy_id` ASC) ,
  CONSTRAINT `fk_policy_permitions`
    FOREIGN KEY (`policy_id` )
    REFERENCES `soemon4`.`user permitions` (`policy_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
...

The problem here is:
FOREIGN KEY (`policy_id` ) REFERENCES `soemon4`.`user permitions` (`policy_id` )

But there is non table "user_permitions'.
You create this table later in your script:
...
CREATE  TABLE  `soemon4`.`user_permitions` (
...

The problem is, the skript will be executed from the top to the bottom. I your case, this mean, you have to create the table "user_permitions" before you create the Foreign Key for the table "policy".

All would work fine, when you will put the "CREATE TABLE `soemon4`.`user_permitions` ( ..." before the "CREATE  TABLE `soemon4`.`policy` (..."

You just can't reference on a non existing table at strict mode.

I would only agree that this is not a bug from engine point of view if 
1. I could later create table and all fk creation will work – but they don’t 
2. Script created by Workbench and I don’t want to edit it in any way – because I am relying on to create migration script.
3. If you thru the script all other fk relation fork even they are out of order too.

I addition did you try to set correct mode and run the script? 
Why after I manage to create table and want to edit fk relation from other tables – they all failed? (see original screen shot)

>>All would work fine, when you will put the "CREATE TABLE >>`soemon4`.`user_permitions` (
>>..." before the "CREATE  TABLE `soemon4`.`policy` (..."

Actually it is even worth - if I moved creation of policy to the beginning of the script - I get failure on other tables.

With:

SET FOREIGN_KEY_CHECKS=0;

...

SET FOREIGN_KEY_CHECKS=1;

you can import circular table definitions.

Regards,

Heikki

Susanne and Heikki
Please look closely in my (generated by Workbench) script. First 2 lines should explain everything. Also please run the script with suggested fixes - not just suggest them, because so far they don't work. 
In response to Susanne suggestion of the problem as I mentioned before I managed to create table with intension to fix fk - but I cannot fix them because they FAILED (look in first two files submitted for this bug) and there is NOTHING I can do at this point to fix fk in created database - I can send you database if you want.

Hello
I need help on this. If it is "Not a Bug" then I should be able to add fk to/and from the table - but I cannot! I need to release db but neither script nor manual modifications is working.