Joomla backup installation from JoomlaPack having Foreign Key Error abruptly stops process.

Hello mdadnan,

You have some data in an other table that depend of data of this table. SO you're not allowed to drop it or delete this data.
You need :
- find this foreign(s) key(s)
- temporary remove it
- drop the table "locations"
- restore data in table "locations"
- recreate the foreign(s) key(s)

To find the foreign(s) key(s) look at the end of each  : CREATE TABLE `<< table name >>` .......... At the end you should see something like CONSTRAINT `<< constraint name >>` FOREIGN KEY REFERENCES `locations` <---- reference to the table "locations"

To remove a constraint : ALTER TABLE table name DROP CONSTRAINT constraint_name;
To add a constraint : ALTER TABLE table name ADD CONSTRAINT constraint_name FOREIGN KEY (`field_name`) REFERENCES `locations` (`loccode`) ON DELETE CASCADE ON UPDATE CASCADE;

Regards

Hello leakim971, unfortunately I am not very good in mysql but I will tend to some elements I understood.

Let me explain a few things before I do..

I am recovering a database from a dump from JoomlaPack that also creates an installer. In short the installer included the previous database (as it is) and included in the installer. When I am executing the package during the import of mysql it started giving Foreign Key errors.

I understood your discussion on it and with some help I managed to reduce errors using

SET FOREIGN_KEY_CHECKS = 0;

That only ignored the checks if True.

My question is because I am working on an *sql* dump file, and your solution probably works on an existing database.

Would it be possible you can help in setting up syntaxes, so that if I am following properly I would have to:

1. Ignore constraint info when creating the table.
2. Add constraint info once the table is created and necessary data.

Can you also take the real data from the provided code above instead "constraint_name". Reason I am asking I am blank on the working logic of Constraints or References.

Thank you!

PS: I am stuck at the last 5% of the code so really close to a solution!

Check your *sql* dump file and search for all CREATE TABLE with CONSTRAINT ... FOREIGN KEY

>Ignore constraint info when creating the table.
example with "location", remove : CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`taxprovinceid`) REFERENCES `taxprovinces` (`taxprovinces`)

>Add constraint info once the table is created and necessary data.
example with "location" : ALTER TABLE `locations` ADD CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`taxprovinceid`) REFERENCES `taxprovinces` (`taxprovinces`) ON DELETE CASCADE ON UPDATE CASCADE;

Sorry for the delayed response. Still having a few issues in last two days and here is what I have found..

executed your solution but now getting error on the ALTER line you suggested.

Query:ALTER TABLE `locations` ADD CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`taxprovinceid`) REFERENCES `taxprovinces` (`taxprovinces`) ON DELETE CASCADE ON UPDATE CASCADE;

MySQL: Can't create table './wri_joomla/#sql-38cc_fca1c.frm' (errno: 150)

I requested webhost for out command SHOW ENGINE INNODB STATUS;
result is posted under Code below.

On searching I found two solutions..

1. It happens when some fields are signed and others mismatched with Unsigned. I checked and I couldnt find any related Unsigned fields in the sql dump file. esp with the error generating lines.
2. Second solution says to define 'index' for a foreignkey field which is beyond my understanding.

The sql query also shows that Taxprovinces reference seems invalid because that table is created later in the sql dump, would that be a problem?

I can also provide you with the database file, if you wish to have a look at it.

Thank you for your support until now.

100104 13:54:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 16731, signal count 16564
Mutex spin waits 0, rounds 102113, OS waits 4470
RW-shared spins 24115, OS waits 12010; RW-excl spins 256, OS waits
209
------------------------
LATEST FOREIGN KEY ERROR
------------------------
100104 13:50:18 Error in foreign key constraint of table
wri_joomla/#sql-38cc_fca1c:
 FOREIGN KEY (`taxprovinceid`) REFERENCES `taxprovinces`
(`taxprovinces`) ON DELETE CASCADE ON UPDATE CASCADE:
Cannot resolve table name close to:
 (`taxprovinces`) ON DELETE CASCADE ON UPDATE CASCADE
------------
TRANSACTIONS
------------
Trx id counter 0 28720532
Purge done for trx's n:o < 0 28720526 undo n:o < 0 0
History list length 8
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 14540, OS thread id
2139511712
MySQL thread id 1036493, query id 196748208 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 28720531, not started, process no 14540, OS thread id
2138114976
MySQL thread id 1036375, query id 196747743 localhost 127.0.0.1
quaddmgc_ruby
--------
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
22794 OS file reads, 394547 OS file writes, 147903 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: size 1, free list len 15, seg size 17,
4082 inserts, 4082 merged recs, 2963 merges
Hash table size 553253, used cells 75131, node heap has 80 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 10 2702640879
Log flushed up to   10 2702640879
Last checkpoint at  10 2702640879
0 pending log writes, 0 pending chkp writes
63111 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 180469458; in additional pool allocated
10408192
Buffer pool size   8192
Free buffers       1
Database pages     8111
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 35112, created 5345, written 348367
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
1 read views open inside InnoDB
Main thread process no. 14540, id 2614815648, state: sleeping
Number of rows inserted 97340, updated 24825, deleted 82067, read
1689518406
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

Select allOpen in new window

You MUST create the table "taxprovinces" before add the constraint.
You should restore its data too (taxprovinces)

Seems it's not the case : Cannot resolve table name close to...

I'm alright, thanks for sharing your issue! I think you know you should accept your resolution as answer for further Internet users!