Bug #25808 Foreign key drops after repeated statement

• Submitted: 23 Jan 2007 22:29
• Modified: 24 Jan 2007 9:45
• Reporter: Patrick Clery
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.23
• CPU Architecture: Any
• Tags: innodb

[23 Jan 2007 22:29] Patrick Clery

Description:
Foreign key drops after two previous errors.

How to repeat:
trancey ~ # mysqladmin create industry_foo
trancey ~ # mysql -u root industry_foo 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 215
Server version: 5.0.32-log Gentoo Linux mysql-5.0.32
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> CREATE TABLE `categories` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `name` varchar(255) NOT NULL,
    ->   `permalink` varchar(255) NOT NULL,
    ->   `pretty_name` varchar(255) default NULL,
    ->   `preamble` text,
    ->   `header` varchar(255) default NULL,
    ->   `news_header` varchar(255) default NULL,
    ->   `resources_header` varchar(255) default NULL,
    ->   `rss_header` varchar(255) default NULL,
    ->   `organizations_header` varchar(255) default NULL,
    ->   `companies_header` varchar(255) default NULL,
    ->   `rss_code` text,
    ->   `meta_description` text,
    ->   `meta_keywords` text,
    ->   `meta_title` varchar(255) default NULL,
    ->   `section_id` int(11) NOT NULL,
    ->   `parent_id` int(11) NOT NULL default '0',
    ->   `show_rss_feeds` tinyint(1) default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE TABLE `articles` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `name` varchar(255) NOT NULL,
    ->   `permalink` varchar(255) NOT NULL,
    ->   `html_title` varchar(255) default NULL,
    ->   `external` tinyint(1) default '0',
    ->   `published` tinyint(1) default '1',
    ->   `body` text,
    ->   `related_links` text,
    ->   `published_at` datetime default NULL,
    ->   `created_at` datetime default NULL,
    ->   `preamble` text,
    ->   `url` varchar(255) default NULL,
    ->   `keywords` text,
    ->   `geography_id` int(11) NOT NULL,
    ->   `category_id` int(11) NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `fk_articles_category_id` (`category_id`),
    ->   CONSTRAINT `fk_articles_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
mysql> alter table articles drop foreign key fk_articles_category_id;
ERROR 1005 (HY000): Can't create table './industry_foo/#sql-21a1_d7.frm' (errno: 150)
 
mysql> alter table articles drop key fk_articles_category_id;
ERROR 1025 (HY000): Error on rename of './industry_foo/#sql-21a1_d7' to './industry_foo/articles' (errno: 150)
 
mysql> alter table articles drop foreign key fk_articles_category_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table articles drop key fk_articles_category_id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

[24 Jan 2007 1:48] MySQL Verification Team

Thank you for the bug report. I was unable to repeat with current source,
please try with latest released version.

    ->     `category_id` int(11) NOT NULL,
    ->    PRIMARY KEY  (`id`),
    ->    KEY `fk_articles_category_id` (`category_id`),
    ->    CONSTRAINT `fk_articles_category_id` FOREIGN KEY (`category_id`)
    -> REFERENCES `categories` (`id`) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table articles drop foreign key fk_articles_category_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.36-debug | 
+--------------+
1 row in set (0.00 sec)

mysql>

[24 Jan 2007 10:29] Johan Idrén

Duplicate of bug #25816