Access Server: "DB IntegrityError (AUTHRPC_APIERROR)" and only some databases created on MySQL when creating a cluster or converting to MySQL

Overview

When creating an Access Server cluster or converting from a local database, such as SQLite, to an external database, such as MySQL, you may encounter the following error:

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

This error has several possible causes. This article covers the case where the conversion stops partway: some Access Server databases are created on the MySQL server, but not all of them, and the last one created is as_userprop. This shows that the connection to MySQL, the credentials, and the permissions are correct, and that the problem is in the user data being copied.

Cause

Duplicate usernames that differ only in letter case, such as Roel and roel, can cause this error. SQLite treats them as different usernames, while MySQL doesn't by default because its default collation is case-insensitive.

Resolution

Before making any changes to the database, back up your Access Server configuration: Tutorial: How to Back Up Access Server Configuration.

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

apt install jq

or

yum install jq

Run the following command to identify duplicate usernames:

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

Delete the duplicate usernames. For each set of duplicates, decide which account to keep and delete the others. Settings stored for a deleted account, such as its group assignment, static IP address, or MFA enrollment, are removed with it.

We recommend deleting users from the Admin Web UI.

Alternatively, delete a user from the command line:

sacli --user "<USERNAME>" UserPropDelAll

Replace <USERNAME> with the exact username, including letter case, as shown in the command output above.

Then create the cluster or convert the database again.

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