Upgrade Issues 3.2.1.1 > 3.3.0.3

Sweet, tasty details:

  • Application Version - Upgrading 3.2.1.1 to 3.3.0.3
  • Description of issue
    I’m getting a ‘Duplicate entry’ error when no duplicate exists.
  • Steps you took leading up to the issue
    When upgrading (with php tools/upgrade.php upgrade), I get the following error:
    <snip, upgrade progressing normally>
    [schema: lib/pkp/xml/schema/common.xml]
    ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘email-redacted@email-host.redacted’ for key ‘users_email’ (SQL: ALTER TABLE users ADD UNIQUE INDEX users_email (email))
  • What you tried to resolve the issue
    Read in many places, looked for any duplicates in the table… none exist (before or after)
  • Screenshots
    None…
  • Error log messages if applicable
    … posted above.

Looking for any ideas here… after restoring, and doing a select on the user’s email … I get only 1 result.

This came down to an issue with collation. The index was case insensitive while the collation was case sensitive. This resulted in there being a “duplicate entry” by the index definition (as user@example.com == USER@eXaMpLe.com). What made this extremely difficult to resolve was that the error presented a user’s email that was not part of the duplicate entries. Instead, I had to build a query that would look for two entries that were the same regardless of case and remove the duplicates that way through user merging.