Author Topic: Foreign key upgrade error 1.6 -> 1.7 (Read 6669 times)

gma · April 30, 2007, 12:41:03 am

Hi,
Having searched the old Nabble posts and this forum, we are still hunting for a solution to yet another foreign key error message.

We have been upgrading a drupal 5.1 site from civiCRM 1.6 to 1.7.9327

We reached step 8 in the upgrade instructions and then received the following error message after logging in as root user and visiting civiCRM:

Code: [Select]

  Sorry. A non-recoverable error has occurred.

    Database Error Code: Cannot add or update a child row: a foreign key constraint fails, 1216

    Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_contact (domain_id , contact_type , sort_name , display_name , hash ) VALUES ( 1 , 'Individual' , 'webmaster@blah.asn.au' , 'webmaster@blah.asn.au' , 'e0688d9adbee548bf2232b9ce0da0653' )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_contact (domain_id , contact_type , sort_name , display_name , hash ) VALUES ( 1 , 'Individual' , 'webmaster@blah.asn.au' , 'webmaster@blah.asn.au' , 'e0688d9adbee548bf2232b9ce0da0653' )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_contact (domain_id , contact_type , sort_name , display_name , hash ) VALUES ( 1 , 'Individual' , 'webmaster@blah.asn.au' , 'webmaster@blah.asn.au' , 'e0688d9adbee548bf2232b9ce0da0653' )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

We have emptied the sessions table and tried to cleanup template cache, but can't even get to the point of turning debugging on.

Hope you can help,

Thanks,
Geoff. [/code]

Donald Lobo · April 30, 2007, 12:45:56 am

Geoff:

Can u check your db and see the contents of civicrm_domain table

in specific can u ensure that there is an entry for id=1 (should exist if u r upgrading)

thanx

lobo

gma · April 30, 2007, 04:27:26 am

Hey Lobo,
Thanks for the swift reply.

Nothing for id=1 I'm afraid:

"MySQL returned an empty result set (i.e. zero rows)."

That was one place where I think we got an error in while importing the data dump into the new 1.7 DB. Just wouldn't import the civiCRM admin user details.

Sorry I didn't mention that earlier

Is it possible to manually enter the values into the table for the 1st user?

Thanks heaps,
Geoff

Dave Greenberg · April 30, 2007, 11:08:25 am

Geoff - Does the following query returns zero rows?

SELECT * from civicrm_domain;

There should be at least one record in this table. If not, we need to figure out why this table is empty, as it must have been populated in your 1.6 install.

What errors did you get while running the upgrade script (civicrm_upgradedb_v1.6_v1.7_41.mysql) ?

Donald Lobo · April 30, 2007, 11:28:32 am

Geoff:

Would be good to find out why there was an upgrade error on that table. This table was not changed in the 1.6->1.7 upgrade process, though the contents were used. So this might cause some problems down the road

In general might be a better idea to go back and upgrade your original 1.6 db and ensure it works fine etc to be safe and avoid potential errors. If you still want to insert a row manually, here's the default data:

id: 1
name: Domain Name 1
description: NULL
contact_name: Domain Contact Name
email_domain: FIXME.ORG
email_return_path: NULL
config_backend: NULL
config_frontend: NULL

lobo

philiphart · April 30, 2007, 05:47:30 pm

Hi,

I'm working with Geoff a little on this problem.

Below is the content of our civicrm_domain table from v1.6. Is the row config_backend supposed to contain all that code?

thanks
Phil (and Geoff)

Code: [Select]

id = 1
name = campcooinda.asn.au
description = Camp Cooinda Inc.
contact_name = Webmaster
email_domain = campcooinda.asn.au
email_return_path = *removed*@campcooinda.asn.au
config_backend = a:80:{s:24:"userFrameworkResourceURL";s:56:"http://www.campcooinda.asn.au/sites/all/modules/civicrm/";s:14:"imageUploadURL";s:63:"http://www.campcooinda.asn.au/files/civicrm/persist/contribute/";s:18:"dateformatDatetime";s:21:"%E%f %B , %Y %l:%M %P";s:14:"dateformatFull";s:12:"%E%f %B , %Y";s:17:"dateformatPartial";s:5:"%B %Y";s:14:"dateformatYear";s:2:"%Y";s:16:"dateformatQfDate";s:8:"%d %b %Y";s:20:"dateformatQfDatetime";s:20:"%d %b %Y, %I : %M %P";s:10:"lcMessages";s:5:"en_US";s:10:"lcMonetary";s:5:"en_US";s:11:"moneyformat";s:5:"%c %a";s:12:"countryLimit";a:2:{i:0;s:4:"1228";i:1;s:4:"1013";}s:13:"provinceLimit";a:2:{i:0;s:4:"1013";i:1;s:4:"1228";}s:21:"defaultContactCountry";s:4:"1013";s:15:"defaultCurrency";s:3:"USD";s:14:"legacyEncoding";s:12:"Windows-1252";s:5:"debug";s:1:"0";s:9:"backtrace";s:1:"0";s:18:"fatalErrorTemplate";s:13:"CRM/error.tpl";s:17:"fatalErrorHandler";s:0:"";s:8:"daoDebug";i:0;s:9:"smartyDir";s:26:"/opt/local/lib/php/Smarty/";s:10:"pluginsDir";s:34:"/opt/local/lib/php/Smarty/plugins/";s:11:"templateDir";s:12:"./templates/";s:8:"mainMenu";N;s:12:"resourceBase";s:46:"http://www.campcooinda.asn.au/modules/civicrm/";s:15:"DAOFactoryClass";s:23:"CRM_Contact_DAO_Factory";s:9:"uploadDir";s:47:"/home/cooinda/public_html/files/civicrm/upload/";s:14:"imageUploadDir";s:59:"/home/cooinda/public_html/files/civicrm/persist/contribute/";s:19:"customFileUploadDir";s:54:"/home/cooinda/public_html/files/civicrm/upload/custom/";s:8:"cleanURL";b:0;s:13:"addressFormat";s:130:"{street_address}
{supplemental_address_1}
{supplemental_address_2}
{city}{, }{county}{ }{state_province}{ }{postal_code}
{country}";s:18:"AddressStdProvider";N;s:16:"AddressStdUserID";N;s:13:"AddressStdURL";N;s:15:"addressSequence";a:8:{i:0;s:14:"street_address";i:1;s:22:"supplemental_address_1";i:2;s:22:"supplemental_address_2";i:3;s:4:"city";i:4;s:6:"county";i:5;s:14:"state_province";i:6;s:11:"postal_code";i:7;s:7:"country";}s:15:"currencySymbols";s:0:"";s:21:"defaultCurrencySymbol";s:1:"$";s:14:"gettextCodeset";s:5:"utf-8";s:13:"gettextDomain";s:7:"civicrm";s:18:"gettextResourceDir";s:7:"./l10n/";s:10:"smtpServer";s:9:"localhost";s:8:"smtpPort";i:25;s:8:"smtpAuth";b:0;s:12:"smtpUsername";s:28:"webmaster@campcooinda.asn.au";s:12:"smtpPassword";s:7:"giraffe";s:13:"userFramework";s:6:"Drupal";s:20:"userFrameworkVersion";d:4.5999999999999996447286321199499070644378662109375;s:18:"userFrameworkClass";s:23:"CRM_Utils_System_Drupal";s:13:"userHookClass";s:21:"CRM_Utils_Hook_Drupal";s:19:"userPermissionClass";s:26:"CRM_Core_Permission_Drupal";s:19:"userFrameworkURLVar";s:1:"q";s:16:"userFrameworkDSN";N;s:27:"userFrameworkUsersTableName";s:5:"users";s:21:"userFrameworkFrontend";b:0;s:12:"mysqlVersion";d:4.0999999999999996447286321199499070644378662109375;s:9:"mysqlPath";s:9:"/usr/bin/";s:17:"maxImportFileSize";i:1048576;s:11:"mapProvider";s:0:"";s:9:"mapAPIKey";s:0:"";s:13:"geocodeMethod";s:0:"";s:12:"versionCheck";b:1;s:12:"mailerPeriod";i:180;s:13:"verpSeparator";s:1:".";s:16:"mailerBatchLimit";i:0;s:16:"enableComponents";a:1:{i:0;s:8:"CiviMail";}s:9:"enableSSL";i:0;s:17:"maxLocationBlocks";i:2;s:15:"captchaFontPath";s:25:"/usr/X11R6/lib/X11/fonts/";s:11:"captchaFont";s:17:"HelveticaBold.ttf";s:20:"includeEmailInSearch";i:1;s:24:"includeAlphabeticalPager";i:1;s:20:"includeOrderByClause";i:1;s:15:"includeDomainID";i:1;s:13:"oldInputStyle";i:1;s:11:"includeDojo";i:1;s:12:"mapGeoCoding";i:1;s:20:"paymentExpressButton";s:57:"https://www.paypal.com/en_US/i/btn/btn_xpressCheckout.gif";s:27:"paymentPayPalExpressTestUrl";s:22:"www.sandbox.paypal.com";s:23:"paymentPayPalExpressUrl";s:14:"www.paypal.com";}
config_frontend = NULL

Donald Lobo · April 30, 2007, 05:52:17 pm

yes, its basically your config object which is stored in a serialized manner

that table should basically move to 1.7 unchanged

lobo

philiphart · April 30, 2007, 06:38:48 pm

Hi Lobo,

Thanks for another quick response. I've tried creating an identical row in the new v1.7 civicrm_domain table but the config_backend field is getting overwritten. The resulting error now refers to the table 'civicrm_location' rather than 'civicrm_contact'.

I can see that CiviCRM is replacing the new contents of ROW config_backend in TABLE civicrm_domain with "N;", instead of the long field entry copied from v1.6. Is there some other routine I need to follow for creating that row in the civicrm_domain table so that it doesn't get overwritten?

I'm using phpMyAdmin. The first time I tried, I added the new row while 'logged out' of Drupal and CiviCRM. The second time I tried updating the config_backend field while logged in but got the same error when I refreshed the CiviCRM admin page.

What do you suggest?

thanks
Phil.

Code: [Select]

     Sorry. A non-recoverable error has occurred.

    Database Error Code: Cannot add or update a child row: a foreign key constraint fails, 1216

    Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_location (entity_table , entity_id , is_primary ) VALUES ('civicrm_contact' ,  1 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_location (entity_table , entity_id , is_primary ) VALUES ('civicrm_contact' ,  1 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_location (entity_table , entity_id , is_primary ) VALUES ('civicrm_contact' ,  1 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

[/code]

Donald Lobo · April 30, 2007, 06:49:35 pm

Can u'll get on IRC and we can chat there. I'll have to take a look at it.

alternatively can u try loading civicrm_41.mysql and civicrm_data.mysql and see if u can get that working (i.e. a new db) and then u'll can figure out how to migrate your existing data

lobo

Donald Lobo · April 30, 2007, 07:16:42 pm

check

http://civicrm.org/participate/

philiphart · April 30, 2007, 08:59:01 pm

Thanks everyone, especially Donald!

We may have been exporting empty data using phpMyAdmin. Having tried again and exporting both Structure and Data and re-importing into a clean civicrm database, v1.7 seems to be working fine under Drupal 5.1.

Running debugging mode didn't return any errors, but I did get the error below when importing the data into the new database, but it doesn't seem to be affecting anything yet? Will post again if anything changes.

thanks!
Phil (& Geoff).

Code: [Select]

SQL query:

-- 
-- Constraints for table `civicrm_acl_cache`
-- 
ALTER TABLE `civicrm_acl_cache` ADD CONSTRAINT `civicrm_acl_cache_ibfk_1` FOREIGN KEY ( `contact_id` ) REFERENCES `civicrm_contact` ( `id` ) ,
ALTER TABLE `civicrm_acl_cache` ADD CONSTRAINT `civicrm_acl_cache_ibfk_1` FOREIGN KEY ( `contact_id` ) REFERENCES `civicrm_contact` ( `id` ) ,
ADD CONSTRAINT `civicrm_acl_cache_ibfk_2` FOREIGN KEY ( `acl_id` ) REFERENCES `civicrm_acl` ( `id` ) ;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE `civicrm_acl_cache`
  ADD CONSTRAINT `civicrm_acl_cache_ibfk_1` FOREIGN KE' at line 3

philiphart · May 02, 2007, 11:17:27 pm

Hi,

The data import isn't quite right yet.

When trying to Synchronise Users-to-Contacts, we get the error below. The table "cooinda_civicrm.users" doesn't exist.

All the other v1.6 tables in the Drupal database had the prefix "civicrm_" and were imported into v1.7. The table "cooinda_drupal_users" looks like a Drupal table so I'm reluctant to try and import that into the new and separate v1.7 civicrm database?

Where should I find the table "cooinda_civicrm.users"?

Appreciate a bit more help to resolve this?

cheers
Phil.

Code: [Select]

Synchronize Users-to-Contacts

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    Database Error Code: Table 'cooinda_civicrm.users' doesn't exist, 1146

    Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] => SELECT uid, mail FROM users where mail != '' [nativecode=1146 ** Table 'cooinda_civicrm.users' doesn't exist]
    [type] => DB_Error
    [user_info] => SELECT uid, mail FROM users where mail != '' [nativecode=1146 ** Table 'cooinda_civicrm.users' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT uid, mail FROM users where mail != '' [nativecode=1146 ** Table 'cooinda_civicrm.users' doesn't exist]"]
)

[/code]

Donald Lobo · May 03, 2007, 12:56:07 am

make sure this settings in civicrm.settings.php points to the Drupal database:

/**
* Content Management System (CMS) Datasource:
*
* Update this setting with your CMS (Drupal or Joomla) database username, server and DB name.
* Datasource (DSN) format:
* define( 'CIVICRM_UF_DSN', 'mysql://cms_db_username:cms_db_password@db_server/cms_database?new_link=true');
*/

define( 'CIVICRM_UF_DSN' , 'mysql://DBUSER:PASSWORD@HOSTNAME/DATABASE?new_link=true' );

lobo

philiphart · May 03, 2007, 07:07:12 pm

Thanks - that does it.

For info and posterity.. the problem arises because in v1.6 our CiviCRM tables were in the Drupal database, but for v1.7 we created a separate database.

Now we have added the CiviCRM user to the Drupal database and updated 'civicrm.settings.php' accordingly. The Synchronise function now works fine from Civi Admin.

cheers
Phil.

CiviCRM Community Forums (archive)

News:

Author Topic: Foreign key upgrade error 1.6 -> 1.7 (Read 6669 times)

gma

Foreign key upgrade error 1.6 -> 1.7

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

gma

Re: Foreign key upgrade error 1.6 -> 1.7

Dave Greenberg

Re: Foreign key upgrade error 1.6 -> 1.7

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

philiphart

Re: Foreign key upgrade error 1.6 -> 1.7

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

philiphart

Re: Foreign key upgrade error 1.6 -> 1.7

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

philiphart

Fixed: Re: Foreign key upgrade error 1.6 -> 1.7

philiphart

Next Step.. Can't Sychronise Users-to-Contacts

Donald Lobo

Re: Foreign key upgrade error 1.6 -> 1.7

philiphart

Re: Foreign key upgrade error 1.6 -> 1.7