===============================================
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:

.. code-block:: bash

    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 ``UNIQUE``
constraint. But it's not enforcing it, since there are duplicate entries in the ``activitylog_logclass`` table. That is,
multiple records with the same ``name`` and ``type`` field values.

How to fix duplicate entries in activitylog_logclass table
==========================================================

The following steps are confirmed to permanently fix the problem.

.. warning::

    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.

1. Stop fleio

Run this if you have version 2022.10.0 or newer:

.. code-block:: bash

    fleio stop

for older versions run:

.. code-block:: bash

    sudo -i -u fleio
    cd ~/compose
    docker compose down

2. Start db container and remove duplicate records

.. code-block:: bash

    # 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
procedure.

If you have duplicate entries exit the container and continue:

.. code-block:: bash

    exit

3. Export database

If you have Fleio version 2022.10.0 or newer, you have the ``fleio backup`` command available:

.. code-block:: bash

    fleio backup now /home/fleio/fleio.sql.gz

otherwise use ``fleio mysqldump``:

.. code-block:: bash

    fleio mysqldump | gzip > fleio.sql.gz

4. Drop and recreate database

.. warning::::

    The following code section will delete your database. Make sure you have backed up before.

.. code-block:: bash

    fleio mysql

    DROP DATABASE fleio;
    CREATE DATABASE fleio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    exit

5. Import database

Import the Fleio database that was exported on step 3:


.. code-block:: bash

    # 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"

6. Start Fleio

.. code-block:: bash

    fleio restart

This should permanently fix the problem. To confirm this, you can later run:

.. code-block:: bash

    fleio bash
    . ../env/bin/activate
    python fleio/utils/scripts/cleanup_duplicate_logclasses.py

It should say *"Found 0 duplicate log classes"*.