| Bug #1667 | create table followed by drop table | ||
|---|---|---|---|
| Submitted: | 26 Oct 2003 9:46 | Modified: | 26 Oct 2003 11:59 |
| Reporter: | Piotr Kapiszewski | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.14 | OS: | Linux (Linux Redhat 7.2) |
| Assigned to: | CPU Architecture: | Any | |
[26 Oct 2003 9:50]
Heikki Tuuri
Hi! Please try the CREATE again and look with SHOW INNODB STATUS\G what is the latest foreign key error. Regards, Heikki
[26 Oct 2003 10:32]
Piotr Kapiszewski
There are appear to be no foreign key errors (we tried looking into that already): ===================================== 031026 13:28:38 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 37 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 11, signal count 11 Mutex spin waits 18, rounds 118, OS waits 2 RW-shared spins 16, OS waits 8; RW-excl spins 1, OS waits 1 ------------ TRANSACTIONS ------------ Trx id counter 0 1524471 Purge done for trx's n:o < 0 1524464 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1522949, not started, process no 2951, OS thread id 45068 MySQL thread id 43215, query id 147255 proxy3.XXXXXX.com xx.xx.xx.xx mk4_dev1_admin -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 85 OS file reads, 770 OS file writes, 745 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 290627, used cells 19, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 44641673 Log flushed up to 0 44641673 Last checkpoint at 0 44641673 0 pending log writes, 0 pending chkp writes 740 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 104827523; in additional pool allocated 1339392 Buffer pool size 4480 Free buffers 4387 Database pages 92 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 86, created 6, written 62 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool activity since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no 2934, state: waiting for server activity Number of rows inserted 676, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ We also also did restart the database.
[26 Oct 2003 10:38]
Heikki Tuuri
Hi! Please look with SHOW CREATE TABLE from your other tables what kind of foreign key constraints reference the table 'person'. http://www.innodb.com/ibman.html#InnoDB_foreign_keys " If you re-create a table which was dropped, it has to have a definition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string. " Regards, Heikki
[26 Oct 2003 11:55]
Piotr Kapiszewski
You are right. It appears that another table somewhere still had a foreign key referencing the dropped person table mentioned in this example. Once those foreign keys were dropped we were able to create the person table without any problems. I guess an only suggestion / feature that could perhaps help would be a note in the ERROR message to check foreign keys. Than again we should have cought it ourselves. Thank you for you amazingly fast response on a Sunday.
[26 Oct 2003 11:59]
Heikki Tuuri
Hi! Ok, I am closing this bug report. I have to check why SHOW INNODB STATUS\G failed to print out the explanation of the error. Regards, Heikki
Description: The following sequence of create / drop table statements appears to cause some kind of a bug preventing the recreation of a table. create database test2; DROP TABLE IF EXISTS person; create table person ( serial bigint not null auto_increment, fname char(50), lname char(50), street char(100), city char(50), state bigint, zip char(9), country bigint, phone char(20), email char(100), birthdate bigint, status1 bigint, bill_next_date bigint, bill_frequency bigint, bill_anniversary bigint, PRIMARY KEY (serial) ) TYPE=INNODB; ERROR 1005: Can't create table '.\mk4_dev\person.frm' (errno: 150) Additional information: - mysql engine installed using the RPM distribution - this problem has been also observed on a windows box running the same version of mysql - there are no permissions on the database - file system permissions are correct - after the above sequence is executed trying a create table person1 drop table person1 create table person1 sequence works fine. The bug appears to behave like a reserved word type problem once its encountered but 'person' doesn't appear to be a reserved word. - if the table type is NOT InnoDB the above sequence works fine and can be repeated. How to repeat: create database test2; DROP TABLE IF EXISTS person; create table person ( serial bigint not null auto_increment, fname char(50), lname char(50), street char(100), city char(50), state bigint, zip char(9), country bigint, phone char(20), email char(100), birthdate bigint, status1 bigint, bill_next_date bigint, bill_frequency bigint, bill_anniversary bigint, PRIMARY KEY (serial) ) TYPE=INNODB;