Body: <p>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).</p>
  
  <p>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?</p>
  
  <p>The options I am thinking of are:</p>
  
  <ul>
  <li>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.  </li>
  <li>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?</li>
  </ul>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-31T01:57:26.267Z
Comments.Text: 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?
Comments.UserDisplayName: Sean
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-31T01:59:16.570Z
Comments.Text: 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.
Comments.UserDisplayName: Sean
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-31T02:51:08.167Z
Comments.Text: 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.
Comments.UserDisplayName: Kristine
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-31T04:52:09.713Z
Comments.Text: 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).
Comments.UserDisplayName: Jon G - Megaphone Tech
Tags: <php><mysql>
Title: Duplicate Entry UI_External_Identifier MySQL Error
ThreadText: # Duplicate Entry UI_External_Identifier MySQL Error
  
  (Asked by: username_0 on 2017-08-30)
  
  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?
  
  **Tags:** php, mysql
  
  > **Comments:**
  > **username_1**: 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?  
  > **username_1**: 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.  
  > **username_0**: 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.  
  > **username_2**: 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).  
  
  ---
  
  ## 0 answers
  
  