CiviCRM Community Forums (archive)

After upgrading from 1.6 to 1.7 I find:

- My profiles, which were working fine, are gone. So our standalone Volunteer form now points to a non-existent profile and fails.
- I created a new profile and Volunteer form, but in testing the form it appears to fail with "DB Error: already exists" and (for example) "nativecode=1062 ** Duplicate entry '3220' for key 2" (full message below).
- Yet the test Volunteer form submissions do end up in the database - except that their data is mixed with existing information from other contacts (alarming behavior!). So a new test user will have the same, say, physical and email addresses as an existing user. I imagine this is what's causing a "duplicate entry" error.

We're running MySQL 4.1.9, PHP 5.2.1 and Apache 2.

Full error message:

Sorry. A non-recoverable error has occurred.
Database Error Code: Duplicate entry '3220' for key 2, 1062

Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3220 , '5959 Downtown Place' , 'Suite 2' , 'Testerville' )  [nativecode=1062 ** Duplicate entry '3220' for key 2]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3220 , '5959 Downtown Place' , 'Suite 2' , 'Testerville' )  [nativecode=1062 ** Duplicate entry '3220' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3220 , '5959 Downtown Place' , 'Suite 2' , 'Testerville' )  [nativecode=1062 ** Duplicate entry '3220' for key 2]"]

I tested it at the sandbox, and it looks like the same thing is happening...

- I created a new group called Volunteers.
- I created a new profile called Volunteer, featuring fields for First Name, Last Name, Email & Phone.
- I clicked the link to generate the code for a standalone form from the Volunteer profile.
- I pasted that code with no modifications or additions into a new test Volunteer page on our site.
- Using the new test Volunteer form, I entered information for "Test Testguy" with email address test@tester.com and phone number 555-555-5555.
- I searched for this new contact in the sandbox CiviCRM and found him at:

http://sandbox.civicrm.org/drupal/civicrm/contact/view?reset=1&cid=108

- But the new contact has physical address information from somebody else, when he should have no such info since there are no fields for it in the form:

1115 W 7th St
Erie, PA 16502-1105
United States

- I can't find any other contact with this address among the sandbox contacts. I think that's because the information is actually being moved from an old contact to the new one. This is what happens with our install (v 1.7.9821).

Will file the issue against 1.8.

Thanks for the quick reply. I tried replacing "edit" with "create" and that did fix the problem I thought I was having on the sandbox.

Unfortunately I guess I have some other problem with my upgrade from 1.6 to 1.7, as reported earlier. I tried the same fix but still get the error message below on using the new standalone form, and I still see that the new contact has information moved from a previously existing contact.



     Sorry. A non-recoverable error has occurred.

    Database Error Code: Duplicate entry '3221' for key 2, 1062

    Return to CiviCRM menu.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3221 , '367 10th St' ,  NULL , 'Monterey' )  [nativecode=1062 ** Duplicate entry '3221' for key 2]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3221 , '367 10th St' ,  NULL , 'Monterey' )  [nativecode=1062 ** Duplicate entry '3221' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_address (location_id , street_address , supplemental_address_1 , city ) VALUES ( 3221 , '367 10th St' ,  NULL , 'Monterey' )  [nativecode=1062 ** Duplicate entry '3221' for key 2]"]
)


Spent some time with spencer on his machine debugging this issue, here are a few observations:

1. Unknown to him, his database was defaulting to MyISAM tables rather than InnoDB. He had to manually turn on InnoDB support in his mysql conf file and restart mysql server.

2. MyISAM is quite awful. It has no transaction support AND it has no Foreign Key Check support. CiviCRM relies on these quite extensively. Having no transaction support meant that some objects were created even when the transaction failed. Having no FK checks meant that some of the FK's were pointing to objects that did not exist. I'm not sure how some of his tables got into that state, but the end result was, there were email records pointing to non-existent locations.

3. We switched to using InnoDB. however since we were some email id's had high numbered location ids, new contacts got some mysterious data in them

Bottom line: InnoDB is quite important. Please check your database and ensure that your tables are InnoDB. When we recently upgraded our local mysql versions, there was some innodb failure and we were using myisam for a few weeks without realizing it

lobo