Duplicate entries in activitylog_logclass table¶
A couple of clients reported errors related to duplicate entries in the
activitylog_logclass database table during
database imports or on upgrades.
The error looks similar to:
Operations to perform: Apply all migrations: activitylog, admin, auth, authtoken, billing, conf, contenttypes, core, cpanel, google_authenticator, logger, notifications, openstack, osbackup, osbilling, pkm, reports, reseller, servers, service_catalogue, sessions, sites, sms_authenticator, tasklog, todo Running migrations: Applying activitylog.0006_alter_logclass_unique_together...Traceback (most recent call last): File "/var/webapps/fleio/env/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) File "/var/webapps/fleio/env/lib/python3.10/site-packages/django/db/backends/mysql/base.py", line 75, in execute return self.cursor.execute(query, args) File "/var/webapps/fleio/env/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute res = self._query(query) File "/var/webapps/fleio/env/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query db.query(q) File "/var/webapps/fleio/env/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query _mysql.connection.query(self, query) MySQLdb.IntegrityError: (1062, "Duplicate entry 'staff_log_in_denied-error-1' for key 'activitylog_logclass_name_type_category_id_1b8d09c9_uniq'")
This seems to be a MariaDB bug that we haven’t been able to identify as a known MariaDB issue. It might be related to
forced server shut downs (like on power loss). Even in this case, MariaDB must be able to enforce the
constraint. But it’s not enforcing it, since there are duplicate entries in the
activitylog_logclass table. That is,
multiple records with the same
type field values.
How to fix duplicate entries in activitylog_logclass table¶
The following steps are confirmed to permanently fix the problem.
Note that a downtime occurs, since fleio is stopped and the production database is recreated.
It is highly recommended that you test this procedure in a non-production environment before running it on production.
Run this if you have version 2022.10.0 or newer:
for older versions run:
sudo -i -u fleio cd ~/compose docker compose down
Start db container and remove duplicate records
# if you've not already sudo as fleio user: sudo -i -u fleio cd ~/compose docker compose up -d db fleio bash . ../env/bin/activate python fleio/utils/scripts/cleanup_duplicate_logclasses.py
You should see some duplicate entries reported if you have a problem to fix. Otherwise you’ll see “Found 0 duplicate
log classes” and you can just run
exit to exit the “fleio bash” container, run
fleio restart and abort this
If you have duplicate entries exit the container and continue:
If you have Fleio version 2022.10.0 or newer, you have the
fleio backup command available:
fleio backup now /home/fleio/fleio.sql.gz
fleio mysqldump | gzip > fleio.sql.gz
Drop and recreate database
fleio mysql DROP DATABASE fleio; CREATE DATABASE fleio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; exit
Import the Fleio database that was exported on step 3:
# make sure you're already under the "fleio" user or run sudo -i -u fleio otherwise db_pass=$(cat /home/fleio/compose/secrets/.db_password) cd /home/fleio/compose gunzip -c /home/fleio/fleio.sql.gz | docker compose exec -T db mysql fleio -u fleio -p"$db_pass"
This should permanently fix the problem. To confirm this, you can later run:
fleio bash . ../env/bin/activate python fleio/utils/scripts/cleanup_duplicate_logclasses.py
It should say “Found 0 duplicate log classes”.