MySQL Bugs: #61067: Foreign key creation order is essential

Bug #61067	Foreign key creation order is essential
Submitted:	5 May 2011 9:55	Modified:	22 Sep 2011 23:02
Reporter:	Sergiy Tkachuk	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.11	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any

Description:
There are two scripts.

good.sql works fine.
fail.sql fails on last insert statement.

The only difference in the order of creating foreign key constraints.

Just FYI. MySql 5.1.56 works fine for both scripts.

===========================================================================

good.sql:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS `CategoryProductProperty`;
CREATE TABLE IF NOT EXISTS `CategoryProductProperty` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `productCategoryId` bigint(20) NOT NULL,
  `productPropertyId` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `productPropertyId` (`productPropertyId`),
  KEY `productCategoryId` (`productCategoryId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Usual properties in category' AUTO_INCREMENT=20 ;

DROP TABLE IF EXISTS `ProductCategory`;
CREATE TABLE IF NOT EXISTS `ProductCategory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parentId` bigint(20) DEFAULT NULL COMMENT 'parent category id - this is to build tree structure',
  `name` varchar(1024) NOT NULL,
  `description` varchar(16384) NOT NULL DEFAULT '',
  `imageId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(255)),
  KEY `parentId` (`parentId`),
  KEY `imageId` (`imageId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

INSERT INTO `ProductCategory` (`id`, `parentId`, `name`, `description`, `imageId`) VALUES
(12, 12, 'name123', '', NULL);

DROP TABLE IF EXISTS `ProductProperty`;
CREATE TABLE IF NOT EXISTS `ProductProperty` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `ProductProperty` (`id`, `name`) VALUES
(3, 'name1'),
(1, 'name2');

ALTER TABLE `ProductCategory`
  ADD FOREIGN KEY (`parentId`) REFERENCES `ProductCategory` (`id`);

 ALTER TABLE `CategoryProductProperty`
   ADD FOREIGN KEY (`productCategoryId`) REFERENCES `ProductCategory` (`id`),
   ADD FOREIGN KEY (`productPropertyId`) REFERENCES `ProductProperty` (`id`);

INSERT INTO CategoryProductProperty (productCategoryId, productPropertyId) VALUES (12, 3);

===========================================================================

fails.sql:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

DROP TABLE IF EXISTS `CategoryProductProperty`;
CREATE TABLE IF NOT EXISTS `CategoryProductProperty` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `productCategoryId` bigint(20) NOT NULL,
  `productPropertyId` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `productPropertyId` (`productPropertyId`),
  KEY `productCategoryId` (`productCategoryId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Usual properties in category' AUTO_INCREMENT=20 ;

DROP TABLE IF EXISTS `ProductCategory`;
CREATE TABLE IF NOT EXISTS `ProductCategory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parentId` bigint(20) DEFAULT NULL COMMENT 'parent category id - this is to build tree structure',
  `name` varchar(1024) NOT NULL,
  `description` varchar(16384) NOT NULL DEFAULT '',
  `imageId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`(255)),
  KEY `parentId` (`parentId`),
  KEY `imageId` (`imageId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

INSERT INTO `ProductCategory` (`id`, `parentId`, `name`, `description`, `imageId`) VALUES
(12, 12, 'name123', '', NULL);

DROP TABLE IF EXISTS `ProductProperty`;
CREATE TABLE IF NOT EXISTS `ProductProperty` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `ProductProperty` (`id`, `name`) VALUES
(3, 'name1'),
(1, 'name2');

 ALTER TABLE `CategoryProductProperty`
   ADD FOREIGN KEY (`productCategoryId`) REFERENCES `ProductCategory` (`id`),
   ADD FOREIGN KEY (`productPropertyId`) REFERENCES `ProductProperty` (`id`);

ALTER TABLE `ProductCategory`
  ADD FOREIGN KEY (`parentId`) REFERENCES `ProductCategory` (`id`);

INSERT INTO CategoryProductProperty (productCategoryId, productPropertyId) VALUES (12, 3);

How to repeat:
Run fail.sql

Suggested fix:
fail.sql and good.sql should perform the same actions.

Thank you for the report.

I can not repeat described behavior on Linux. Please provide your configuration file.

This is on Windows Vist - I specified in ticket this.

my.ini in attachment.

Thank you for the feedback.

We must to test all bugs on Linux to be sure this is not cross-platform bug. This is why I asked for configuration file. Btw problem is not repeatable on Linux and in Cygwin environment. Seems to be platform specific.

No problems. If you need more info please let me know.

I couldn't repeat on Vista with current source server.

Below fail.sql:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >CREATE DATABASE d25
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >use d25
Database changed
mysql 5.5 >SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 >DROP TABLE IF EXISTS `CategoryProductProperty`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >CREATE TABLE IF NOT EXISTS `CategoryProductProperty` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `productCategoryId` bigint(20) NOT NULL,
    ->   `productPropertyId` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `productPropertyId` (`productPropertyId`),
    ->   KEY `productCategoryId` (`productCategoryId`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Usual properties in category'
    -> AUTO_INCREMENT=20 ;
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 >
mysql 5.5 >DROP TABLE IF EXISTS `ProductCategory`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >CREATE TABLE IF NOT EXISTS `ProductCategory` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `parentId` bigint(20) DEFAULT NULL COMMENT 'parent category id - this is to build tree
    '> structure',
    ->   `name` varchar(1024) NOT NULL,
    ->   `description` varchar(16384) NOT NULL DEFAULT '',
    ->   `imageId` bigint(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `name` (`name`(255)),
    ->   KEY `parentId` (`parentId`),
    ->   KEY `imageId` (`imageId`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >
mysql 5.5 >INSERT INTO `ProductCategory` (`id`, `parentId`, `name`, `description`, `imageId`)
    -> VALUES
    -> (12, 12, 'name123', '', NULL);
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >
mysql 5.5 >DROP TABLE IF EXISTS `ProductProperty`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 >CREATE TABLE IF NOT EXISTS `ProductProperty` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(128) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `name` (`name`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >
mysql 5.5 >INSERT INTO `ProductProperty` (`id`, `name`) VALUES
    -> (3, 'name1'),
    -> (1, 'name2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 > ALTER TABLE `CategoryProductProperty`
    ->    ADD FOREIGN KEY (`productCategoryId`) REFERENCES `ProductCategory` (`id`),
    ->    ADD FOREIGN KEY (`productPropertyId`) REFERENCES `ProductProperty` (`id`);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >ALTER TABLE `ProductCategory`
    ->   ADD FOREIGN KEY (`parentId`) REFERENCES `ProductCategory` (`id`);
Query OK, 1 row affected (0.21 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >INSERT INTO CategoryProductProperty (productCategoryId, productPropertyId) VALUES (12,
    -> 3);
Query OK, 1 row affected (0.01 sec)

mysql 5.5 >

Could you please try last 5.1.XX released version. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".