Case or Appliance Restore in eDiscovery Platform 10.2.x Fails With Error Duplicate Entry for key t_participantemailaddress.EMAILADDRESS

Problem

An error occurs while trying to restore a case backup or appliance backup to eDiscovery Platform (eDP) v10.2.x.  The error states Duplicate Entry <SomeEmailAddress> for key t_participantemailaddress.EMAILADDRESS.

Error Message

Below is an example seen from Clearwell Utility option 2. Restore Appliance.  Similar errors occur with case backups:
[dbbackuprestoreprop] Restore exception thrown: java.util.concurrent.ExecutionException: Runtime error: mysql: [Warning] Using a password on the command line interface can be insecure.
[dbbackuprestoreprop] ERROR 1062 (23000) at line 7169 in file: 'RESTORE_dbdump_lds_case_a52c0h2y19_5849.sql': Duplicate entry 'abigail.smith@edp.lab' for key 't_participantemailaddress.EMAILADDRESS'
[dbbackuprestoreprop] ERROR 1062 (23000) at line 7169 in file: 'RESTORE_dbdump_lds_case_a52c0h2y19_5849.sql': Duplicate entry 'abigail.smith@edp.lab' for key 't_participantemailaddress.EMAILADDRESS'
[dbbackuprestoreprop]   at java.util.concurrent.FutureTask.report(FutureTask.java:122)
[dbbackuprestoreprop]   at java.util.concurrent.FutureTask.get(FutureTask.java:192)
[dbbackuprestoreprop]   at com.teneo.esa.ant.common.DbBackupRestorePropertyTask.executeRestore(DbBackupRestorePropertyTask.java:1056)
[dbbackuprestoreprop]   at com.teneo.esa.ant.common.DbBackupRestorePropertyTask.execute(DbBackupRestorePropertyTask.java:719)
[dbbackuprestoreprop]   at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:299)
[dbbackuprestoreprop]   at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
[dbbackuprestoreprop]   at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:299)
[dbbackuprestoreprop]   at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
[dbbackuprestoreprop]   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[dbbackuprestoreprop]   at java.lang.reflect.Method.invoke(Method.java:498)
[dbbackuprestoreprop]   at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:99)
[dbbackuprestoreprop]   at org.apache.tools.ant.Task.perform(Task.java:350)
[dbbackuprestoreprop]   at org.apache.tools.ant.Target.execute(Target.java:449)
[dbbackuprestoreprop]   at org.apache.tools.ant.Target.performTasks(Target.java:470)
[dbbackuprestoreprop]   at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1401)
[dbbackuprestoreprop]   at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:299)
[dbbackuprestoreprop] ERROR 1062 (23000) at line 7169 in file: 'RESTORE_dbdump_lds_case_a52c0h2y19_5849.sql': Duplicate entry 'abigail.smith@edp.lab' for key 't_participantemailaddress.EMAILADDRESS'

[dbbackuprestoreprop] Begin cancellation of all restore tasks...
[dbbackuprestoreprop] All restore tasks cancelled.
[dbbackuprestoreprop] Attempting to stop all active tasks.
[dbbackuprestoreprop] Waiting for all tasks to complete the execution.
[dbbackuprestoreprop] Cleaning up the temp files from current directory: c:\backups\eDP-App1 09-18-2023
[dbbackuprestoreprop] Cleaning up the temp files from tempDir: D:\CW\V102\scratch\temp
[dbbackuprestoreprop] Cleaning up the temp file: D:\CW\V102\scratch\temp\RESTORE_dbdump_lds_email_locator_a52c0h2y1d_89869.sql
[dbbackuprestoreprop] Cleaning up the temp file: D:\CW\V102\scratch\temp\RESTORE_dbdump_lds_index_a52c0h2y1g_36621.sql
[dbbackuprestoreprop]
[dbbackuprestoreprop] Total time taken to restore DB data stores: 23 Mins

BUILD FAILED
D:\CW\V102\build.xml:2323: The following error occurred while executing this line:
D:\CW\V102\build\build-schema.xml:1711: The following error occurred while executing this line:
D:\CW\V102\build\build-schema.xml:2092: The following error occurred while executing this line:
D:\CW\V102\build\build-schema.xml:2437: Runtime error: mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 7169 in file: 'RESTORE_dbdump_lds_case_a52c0h2y19_5849.sql': Duplicate entry 'abigail.smith@edp.lab' for key 't_participantemailaddress.EMAILADDRESS'

Cause

Character encoding is handled differently in newer versions of MySql.

Solution

Upgrade to eDiscovery Platform 10.3.3 or newer.  If upgrading the product is not an immediate option then call into support to work around this issue manually.  eDiscovery Platform 10.3.3 introduced a new property called esa.common.dbms.restore.encoding that will allow the character encoding to be changed.  This will give customer's the ability try different character encodings to find one more suitable for their language and then try the restore again.  If still fails try using another character encoding.  Below are the steps:

2. Go to System
3. Go to Support Features
4. Select Property Browser from Step 1 dropdown
5. For Name of property to change type the following:  esa.common.dbms.restore.encoding
6. For New value type one of the following character encodings:
   1. utf8mb4_general_ci - Default encoding used by eDP; suitable for general use and special characters.
   2. utf8mb4_0900_as_cs - Case-sensitive collation; use this when duplicate issues are caused by trailing spaces in email addresses.
   3. utf8mb4_0900_ai_ci - Case-insensitive collation; also handles trailing space issues.
7. Select the checkbox next to Confirm change, are you sure?
8. Press the Submit button.  Here is an example screenshot:
   
9. Try the restore again