Bug #740 A table called 'address' can be created in 4.0.12 but not 4.0.13

• Submitted: 27 Jun 2003 9:13
• Modified: 30 Oct 2004 9:12
• Reporter: Jason Austin (MySQL)
• Status: No Feedback
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 4.0.13
• OS: Windows (Windows XP Pro)
• Assigned to: Assigned Account
• CPU Architecture: Any

[27 Jun 2003 9:13] Jason Austin (MySQL)

Description:
I have a table called 'address'. Under 4.0.12 and earlier I could create this table no problem. When I tried to create it in 4.0.13 I get an error 1005 (errno:150).

I renamed the table to 'address1' and it created it with no problems.

This leads me to believe there is 1) a bug that prevents a table with the name 'address' or 2) 'address' is now a reserved word.

How to repeat:
*** THIS ONE FAILS ****
create table `address` (`address_id` bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (`address_id`)) TYPE=InnoDB;

*** THIS ONE WORKS ****
create table `address1` (`address_id` bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (`address_id`)) TYPE=InnoDB;

[27 Jun 2003 10:13] Sergei Golubchik

I cannot repeat it:

mysql> create table `address` (`address_id` bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (`address_id`)) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table `address`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table address (`address_id` bigint(20) unsigned NOT NULL auto_increment, PRIMARY KEY (`address_id`)) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table address;
Query OK, 0 rows affected (0.00 sec)

[27 Jun 2003 11:27] Heikki Tuuri

Jason,

"
When I tried to create it in 4.0.13 I get
an error 1005 (errno:150).
"

you are getting a FOREIGN KEY error. In 4.0.13 you can use SHOW INNODB STATUS to print a detailed explanation of the latest foreign key error. What does it print?

If you rename the table then you, of course, can create it because then you do not have child tables referring to it with FOREIGN KEY constraints.

Regards,

Heikki