CiviCRM Community Forums (archive)

Hi everybody,

[I don't know if this is the proper place to post this, but couldn't find anyone better]

I've been trying to transfer a Drupal + CiviCRM site to a new server.
The restore process went OK with the Drupal databases but the restoration of the CiviCRM database is always interrupted when dealing with the table civicrm_premiums_product .

System where the Backup of the database was made:
Server: CENTOS 5.4 i686 -Linux core 2.6.28.9
MySQL Version:  5.1.30
PHP Version:  5.2.9

System where the Restore of the database was tried:
Server Version:   Ubuntu 9.10 -Linux core 2.6.32
MySQL Version: 5.1.37
PHP Version::    5.2.10-2ubuntu6

The tool used was: BigDump: Staggered MySQL Dump Importer (http://www.ozerov.de/bigdump.php)

All the other tables of the drupal installtion were restored correctly.

Error:

"MySQL: Can't create table 'jjhope_civicrm1.civicrm_premiums_product' (errno: 150)"


The complete screen received when the process stopped was:
----------------------------------------------------------------------------------------------------------------------
BigDump: Staggered MySQL Dump Importer v0.29b

Processing file: civicrm1.sql

Starting from line: 3001

Error at the line 3497: ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Query: CREATE TABLE `civicrm_premiums_product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Contribution ID',
`premiums_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to premiums settings record.',
`product_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to each product object.',
`weight` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_civicrm_premiums_product_premiums_id` (`premiums_id`),
KEY `FK_civicrm_premiums_product_product_id` (`product_id`),
CONSTRAINT `FK_civicrm_premiums_product_premiums_id` FOREIGN KEY (`premiums_id`) REFERENCES `civicrm_premiums` (`id`),
CONSTRAINT `FK_civicrm_premiums_product_product_id` FOREIGN KEY (`product_id`) REFERENCES `civicrm_product` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL: Can't create table 'jjhope_civicrm1.civicrm_premiums_product' (errno: 150)

Stopped on error
----------------------------------------------------------------------------------------------------------------------

It seems that it is related to some foreign keys constrain, but I cannot understand EXACTLY what the problem is the problem or how to fix.
If somebody is able and wants to give me an as clearly as possible comment on this or any reference for further study the paroblem I would really appreciate that very much.
.
The problem MAY BE related to some new requirements and maybe the need of some indexation.

I have found some similar problems and information here:

http://bytes.com/topic/mysql/answers/865699-cant-create-table-errno-150-foreign-key-constraints

http://bytes.com/topic/mysql/answers/70026-error-1005-cant-create-table-db-table-frm-errno-150-a
(Unfortunately the link indicated here, where a possible solution is mentioned, links to a non available site)

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html


Just in case it offers an additional clue: When I try to drop the tables from the database I receive the following error:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

(which reinforces the idea about the key constrains above mentioned)

But if I continue confirming the delete all the tables are finally gone.


Many thanks for reading this, and for any possible help or clue!


Régulo