Demi, try prepending:
SET FOREIGN_KEY_CHECKS=0; SET SQL_LOG_OFF=1; SET SQL_LOG_UPDATE=0; to your backup file. I'm guessing your dumpfile tries to create the tables in the wrong order in terms of restraints. The SQL_LOG_... lines are there to speedup things a little. DK wrote: > I have striped all the insert statements to first create the tables. > > Now I am getting 'LATEST FOREIGN KEY ERROR' > > Below I have also included the INNODB MONITOR OUTPUT in case someone > can make sense of it. > > ERROR: > > mysql> source /backup/sql/dbmail-table.sql; > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.01 sec) > > Query OK, 0 rows affected (0.00 sec) > > ERROR 1005: Can't create table './dbmail/dbmail_acl.frm' (errno: 150) > Query OK, 0 rows affected (0.01 sec) > > ERROR 1005: Can't create table > './dbmail/dbmail_auto_notifications.frm' (errno: 150) > ERROR 1005: Can't create table './dbmail/dbmail_auto_replies.frm' > (errno: 150) > ERROR 1005: Can't create table './dbmail/dbmail_filters.frm' (errno: 150) > ERROR 1005: Can't create table './dbmail/dbmail_mailboxes.frm' (errno: 150) > ERROR 1005: Can't create table './dbmail/dbmail_messageblks.frm' (errno: > 150) > ERROR 1005: Can't create table './dbmail/dbmail_messages.frm' (errno: 150) > Query OK, 0 rows affected (0.01 sec) > > Query OK, 0 rows affected (0.00 sec) > > ERROR 1005: Can't create table './dbmail/dbmail_subscription.frm' > (errno: 150) > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.01 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.01 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.01 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.00 sec) > > Query OK, 0 rows affected (0.01 sec) > > mysql> > > HERE IS THE STATUS: > > Status: > ===================================== > 060616 0:47:07 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 22 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 79, signal count 79 > Mutex spin waits 48, rounds 800, OS waits 8 > RW-shared spins 140, OS waits 70; RW-excl spins 1, OS waits 1 > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 060616 0:35:24 Error in foreign key constraint of table > `dbmail/dbmail_subscription`: > FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON > DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) > REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON > UPDATE CASCADE > ) TYPE=InnoDB: > Cannot resolve table name close to: > (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) > REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON > UPDATE CASCADE > ) TYPE=InnoDB > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 14058 > Purge done for trx's n:o < 0 14017 undo n:o < 0 0 > Total number of lock structs in row lock hash table 0 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 13777, not started, process no 2670, OS thread id > 1183603632 > MySQL thread id 8, query id 137129 localhost root > SHOW INNODB STATUS > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > 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 > 83 OS file reads, 13449 OS file writes, 13226 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 34679, used cells 2, node heap has 1 buffer(s) > 0.00 hash searches/s, 0.00 non-hash searches/s > --- > LOG > --- > Log sequence number 0 27843988 > Log flushed up to 0 27843988 > Last checkpoint at 0 27843988 > 0 pending log writes, 0 pending chkp writes > 13131 log i/o's done, 0.00 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 18742722; in additional pool allocated 1048448 > Buffer pool size 512 > Free buffers 1 > Database pages 510 > Modified db pages 0 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 93, created 1996, written 2599 > 0.00 reads/s, 0.00 creates/s, 0.00 writes/s > No buffer pool page gets since the last printout > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > Main thread process no. 2670, id 1166556080, state: waiting for server > activity > Number of rows inserted 12715, 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 > ============================ > > 1 row in set (0.04 sec) > -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl
