• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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

Hi,

I am having problem installing Joomla from JoomlaPack backup as it is giving Foreign Key error when importing sql database.

I have included the error generated in the code snippet.

I think I might have to correct all table creating syntaxes for the whole sql file. I would appreciate if someone could guide me to the right direction.

Please let me know if you require any further information.

Thanks.

Error at the line 1938: CREATE TABLE `locations` ( `loccode` varchar(5) NOT NULL default '', `locationname` varchar(50) NOT NULL default '', `deladd1` varchar(40) NOT NULL default '', `deladd2` varchar(40) NOT NULL default '', `deladd3` varchar(40) NOT NULL default '', `deladd4` varchar(40) NOT NULL default '', `deladd5` varchar(20) NOT NULL default '', `deladd6` varchar(15) NOT NULL default '', `tel` varchar(30) NOT NULL default '', `fax` varchar(30) NOT NULL default '', `email` varchar(55) NOT NULL default '', `contact` varchar(30) NOT NULL default '', `taxprovinceid` tinyint(4) NOT NULL default '1', `managed` int(11) default '0', PRIMARY KEY (`loccode`), KEY `taxprovinceid` (`taxprovinceid`), CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`taxprovinceid`) REFERENCES `taxprovinces` (`taxprovinceid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query:DROP TABLE IF EXISTS `locations`;

MySQL: Cannot delete or update a parent row: a foreign key constraint fails

Stopped on error

Open in new window

0
mdadnan
Asked:
mdadnan
  • 4
  • 3
1 Solution
 
leakim971PluritechnicianCommented:
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
0
 
mdadnanAuthor Commented:
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!
0
 
leakim971PluritechnicianCommented:
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;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mdadnanAuthor Commented:
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

Open in new window

0
 
leakim971PluritechnicianCommented:
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...
0
 
mdadnanAuthor Commented:
Hi,

This completely drove me nuts going through all variable throughout the database.

It turns out the database structure half InnoDB and half MyISAM was the problem.

what I did was turn the whole structure as MyISAM and its running perfectly.

Thanks, for all the help though. I wish to partially assign you points if its alright with you.

Do let me know.

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now