Everytime I try to import a backup of my CiviCRM sql database, I always get an error: #1062 - Duplicate entry '' for key 'UI_external_identifier' in phpmyadmin (4.7.0, PHP version: 7.1.4, mysql 5).

I noticed that this has to do with the UI_external_identifier in civicrm_contact, it's reading any records with a blank has having a duplicate entry. What would be the best way to handle this?

The options I am thinking of are:

  • Option 1: Is there a way to change myphpadmin settings to NOT read blanks as duplicate entries? My other server does not have this problem when the external ID is NULL.
  • Option 2: Set every ui_external_identifier to equal id value. This will ensure there is a value for every record, but I guess this would mean that I would have to use SQL to do it manually?
  • Questions: Can you provide more information about how you are making this backup? You mentioned you have two different servers... are you using the same server to make the backup as you are to restore it? – Sean Aug 31 '17 at 1:57
  • I would definitely recommend not doing option 2 because (1) the external identifier field has its own meaning which should not be conflated with id, and (2) it would be better to get to the root cause of this problem because it may pop up elsewhere too. – Sean Aug 31 '17 at 1:59
  • To answer your first question, the backup is on the same server. First, I tried using the "copy database" operation in phpmyadmin, but I noticed that it was not backing up all the tables. I also tried using "Export" but same missing tables. Finally, I found the "DB Backup Tool" script and it was able to export all the tables correctly (around 20 mb). Yes, I am using the same server to restore it. – Kristine Aug 31 '17 at 2:51
  • It sounds like there's a bug in the "DB Backup Tool" script. Could you please link to something that describes it? Meanwhile - exporting from PHPMyAdmin shouldn't leave tables missing. Can you tell us which tables are missing? They might not be tables but views (esp. if you have multilingual mode turned on). – Jon G - Megaphone Tech Aug 31 '17 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.