| Bug #44057 | Unable to create new tables with foreign key references in big databases | ||
|---|---|---|---|
| Submitted: | 2 Apr 2009 22:28 | Modified: | 6 May 2009 18:39 |
| Reporter: | Kai-jens Meyer | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.51a | OS: | Linux (14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb foreign key 150 | ||
[3 Apr 2009 1:11]
MySQL Verification Team
Thank you for the bug report. Try to get better error description with SHOW INNODB STATUS command like I did below:
c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.80-Win X64 revno 2775-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > use test
Database changed
mysql 5.0 > create table winsend2_updates (
-> frameid varchar(6) not null,
-> registration varchar(16) default '' not null,
-> manufacturer varchar(32) default '' not null,
-> model varchar(4) default '' not null,
-> details varchar(24) default '' not null,
-> cn varchar(24) default '' not null,
-> ln varchar(24) default '' not null,
-> added integer unsigned not null,
-> constraint foreign key (`frameid`) references frameids (`icao24`)
-> ) Engine=InnoDB;
ERROR 1005 (HY000): Can't create table '.\test\winsend2_updates.frm' (errno: 150)
mysql 5.0 > SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
090402 22:07:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 6, OS waits 3; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
090402 22:06:53 Error in foreign key constraint of table test/winsend2_updates:
foreign key (`frameid`) references frameids (`icao24`)
) Engine=InnoDB:
Cannot resolve table name close to:
(`icao24`)
) Engine=InnoDB
------------
TRANSACTIONS
[3 Apr 2009 6:22]
Kai-jens Meyer
LATEST FOREIGN KEY ERROR ------------------------ 090403 8:14:18 Error in foreign key constraint of table homeradar/winsend2: foreign key (`frameid`) references frameids (`icao24`) ) Engine=InnoDB: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql> describe frameids; +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | icao24 | varchar(6) | NO | PRI | | | | added | int(10) unsigned | NO | | | | | lastcontact | int(10) unsigned | NO | | | | +-------------+------------------+------+-----+---------+-------+ mysql> show index from frameids; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | frameids | 0 | PRIMARY | 1 | icao24 | A | 74913 | NULL | NULL | | BTREE | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ mysql> select count(icao24) from frameids; +---------------+ | count(icao24) | +---------------+ | 74649 | +---------------+ 1 row in set (0.04 sec) mysql> select count(distinct icao24) from frameids; +------------------------+ | count(distinct icao24) | +------------------------+ | 74649 | +------------------------+
[3 Apr 2009 6:23]
Kai-jens Meyer
And a test with ALTER TABLE.. mysql> alter table winsend2 add constraint `frameid_ref_winsend2` foreign key (`frameid`) references frameids (`icao24`); 090403 8:20:57 Error in foreign key constraint of table homeradar/#sql-600a_98fe: foreign key (`frameid`) references frameids (`icao24`): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.
[3 Apr 2009 8:20]
Sveta Smirnova
Thank you for the feedback. Have you run CHECK TABLE frameids FOR UPGRADE and CHECK TABLE frameids? If not, please do: this looks like table frameids created with elder version of MySQL and has internal errors.
[3 Apr 2009 9:01]
Kai-jens Meyer
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 59745
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CHECK TABLE frameids FOR UPGRADE;
+--------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| homeradar.frameids | check | status | OK |
+--------------------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> CHECK TABLE frameids;
+--------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| homeradar.frameids | check | status | OK |
+--------------------+-------+----------+----------+
1 row in set (6.44 sec)
mysql> create table winsend2a (
-> frameid varchar(6) not null,
-> registration varchar(16) default '' not null,
-> manufacturer varchar(32) default '' not null,
-> model varchar(4) default '' not null,
-> details varchar(24) default '' not null,
-> cn varchar(24) default '' not null,
-> ln varchar(24) default '' not null,
-> added integer unsigned not null,
-> constraint `frameid_ref_winsend2` foreign key (`frameid`) references frameids (`icao24`)
-> ) Engine=InnoDB;
ERROR 1005 (HY000): Can't create table './homeradar/winsend2a.frm' (errno: 150)
No success
[6 Apr 2009 17:00]
Valeriy Kravchuk
Just to make sure CHECK worked OK, please, send the results of: show table status like ' frameids'\G
[6 Apr 2009 17:53]
Kai-jens Meyer
mysql> show table status like 'frameids'\G
*************************** 1. row ***************************
Name: frameids
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 71318
Avg_row_length: 51
Data_length: 3686400
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2009-04-03 08:20:41
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 2599936 kB
1 row in set (0.03 sec)
[6 Apr 2009 18:39]
Sveta Smirnova
Thank you for the feedback. We were not able to repeat described behavior. Additionally version of MySQL you are using is old. Please try our current 5.0.77 binaries available from dev.mysql.com/downloads and if problem still exists provide your configuration file.
[6 May 2009 23:00]
Bugs System
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".
Description: I have a very big database using the InnoDB storage engine. Within these DB i have a lot of tables which are referencing to a base table with addresses. A lot of tables are bound to this master table: MASTER TABLE: create table frameids ( icao24 varchar(6) not null, added integer unsigned not null, lastcontact integer unsigned not null, constraint `icao24_frameid` primary key (icao24) ) Engine=InnoDB; and a working reference (table created with empty database appr. 1 year ago) create table registrations ( regid bigint unsigned not null auto_increment, frameid varchar(6) not null, registration varchar(16) not null, added integer unsigned not null, updated integer unsigned default 0, model varchar(4) default '*' not null, cn varchar(24) default '' not null, constraint `regid_unique` primary key (regid), constraint `acicao_reference` foreign key (`model`) references icaoaccodes (`icaoac`) on delete cascade, constraint `frameid_reference` foreign key (`frameid`) references frameids (`icao24`) on delete cascade ) Engine=InnoDB; and now i have to add a contribution table where the frameid is again referencing to the 'frameids' table: create table winsend2_updates ( frameid varchar(6) not null, registration varchar(16) default '' not null, manufacturer varchar(32) default '' not null, model varchar(4) default '' not null, details varchar(24) default '' not null, cn varchar(24) default '' not null, ln varchar(24) default '' not null, added integer unsigned not null, constraint foreign key (`frameid`) references frameids (`icao24`) ) Engine=InnoDB; and this will fail with errno 150. Since all others (5 tables) are using the same syntax i have tested this creation string on one of my stand-bye server (5.0.32 debian 32bit) and... mysql> create table winsend2_updates ( -> acaddr varchar(6) not null, -> registration varchar(16) default '' not null, -> manufacturer varchar(32) default '' not null, -> model varchar(4) default '' not null, -> details varchar(24) default '' not null, -> cn varchar(24) default '' not null, -> ln varchar(24) default '' not null, -> added integer unsigned not null, -> constraint `winsend_primary` primary key (acaddr), -> constraint `frameid_referenceb` foreign key (`acaddr`) references frameids (`icao24`) -> ) Engine=InnoDB; Query OK, 0 rows affected (0.21 sec) and on the main server (64bit version of mysql on a 3GB database) mysql> create table winsend2_updates ( -> frameid varchar(6) not null, -> registration varchar(16) default '' not null, -> manufacturer varchar(32) default '' not null, -> model varchar(4) default '' not null, -> details varchar(24) default '' not null, -> cn varchar(24) default '' not null, -> ln varchar(24) default '' not null, -> added integer unsigned not null, -> constraint foreign key (`frameid`) references frameids (`icao24`) -> ) Engine=InnoDB; ERROR 1005 (HY000): Can't create table './homeradar/winsend2_updates.frm' (errno: 150) I have no idea how to fix this and i need this table and also need the reference to the basetable to keep the DB struct clear. How to repeat: This cannot be repeated because i have no other 64bit mysql available and on empty database or different system (32bit) the creation works