Access Server: Resolve DB IntegrityError (AUTHRPC_APIERROR) During MySQL Conversion

Overview

When creating an Access Server cluster or converting from SQLite to MySQL, you may encounter the following error:

Error while converting Databases to MySQL. DB IntegrityError (AUTHRPC_APIERROR)

Cause

In some cases, only some databases are created in MySQL, with as_userprop being the last one created.

One possible cause is duplicate usernames that differ only by capitalization, such as Roel and roel. SQLite can treat these as distinct usernames, while a case-insensitive MySQL collation can treat them as duplicates, causing the conversion to fail.

Resolution

Important: Before modifying user accounts, back up your Access Server configuration. Deleting a user's properties can remove settings such as group assignments, static IP addresses, and MFA enrollment.

1. Install jq, a Linux command-line utility for reading, filtering, and manipulating JSON data.

   • On Ubuntu or Debian:

     sudo apt install jq

   • On RHEL-based systems:

     sudo dnf install jq

2. Run the following command to identify usernames that differ only by capitalization:

   sacli UserPropGet | jq -r 'keys[]' | \
   awk '{ key=tolower($0); count[key]++; names[key]=names[key] $0 "\n" }
       END { for (key in count) if (count[key] > 1) printf "%s", names[key] }'

   • Example output:

     Roel
     roel
     Boy
     boY

3. Review each set of duplicate usernames and determine which account to keep. Settings stored for a deleted account, such as its group assignment, static IP address, or MFA enrollment, are removed with it.

4. Delete the duplicate accounts from the Admin Web UI (recommended).
   Alternatively, use the following command to delete a user's properties:

   sacli --user "<USERNAME>" UserPropDelAll

   Replace <USERNAME> with the exact username, including capitalization.

5. Retry the cluster conversion or MySQL conversion.

If you have additional questions or encounter issues, submit a support ticket.