upgrade_59.sql fails on existing servers

[NavidSassan]

Expected Behavior

Migrations should run through.

Current Behavior

The upgrade_59.sql migration fails, resulting in [db] component changed from idle to failed.

The migration tries to add a new primary key to the instance_uuid and ts_create columns of the vspheredb_daemonlog table.
The problem is that existing databases can already contain entries where those columns are not unique - since ts_create was handled differently before e14d363.

For example:

MariaDB [icinga_vspheredb]> select * from vspheredb_daemonlog;
+---------------+------------------------------------+------+--------------------+--------------+---------+-----------------------------------------------------+
| ts_create     | instance_uuid                      | pid  | fqdn               | vcenter_uuid | level   | message                                             |
+---------------+------------------------------------+------+--------------------+--------------+---------+-----------------------------------------------------+
| 1699959383103 | 0xD6A86DF6F4F7409391BFB41B93CFC564 | 7116 | master.example.com | 0x           | notice  | [db] DB has been cleaned up                         |
| 1699959383103 | 0xD6A86DF6F4F7409391BFB41B93CFC564 | 7116 | master.example.com | 0x           | notice  | [db] Running DB cleanup (this could take some time) |
| 1699959383103 | 0xD6A86DF6F4F7409391BFB41B93CFC564 | 7116 | master.example.com | 0x           | notice  | [db] DB schema is ready                             |
| 1699959382773 | 0xD6A86DF6F4F7409391BFB41B93CFC564 | 7116 | master.example.com | 0x           | notice  | [db] Applying schema migrations                     |

Steps to Reproduce (for bugs)

1. Install the current master (a700003 at the time of writing).
2. enable verbose output by setting ExecStart=/usr/bin/icingacli vspheredb daemon run --trace --debug via systemctl edit --full icinga-vspheredb.service
3. systemctl daemon-reload
4. systemctl restart icinga-vspheredb.service
5. journalctl -feu icinga-vspheredb.service

Manually try to execute the migration:

mysql --user=icinga_vspheredb_user --password icinga_vspheredb < /usr/share/icingaweb2/modules/vspheredb/schema/mysql-migrations/upgrade_59.sql
--------------
ALTER TABLE vspheredb_daemonlog
  ADD PRIMARY KEY (instance_uuid, ts_create)
--------------

ERROR 1062 (23000) at line 1: Duplicate entry '\xD6\xA8m\xF6\xF4\xF7@\x93\x91\xBF\xB4\x1B\x93\xCF\xC5d-16999...' for key 'PRIMARY'

Your Environment

• Version/GIT-Hash of this module: a700003
• Icinga Web 2 version: 2.12.2
• Operating System and version: Rocky8
• Webserver, PHP versions: Apache HTTPd, PHP 8.1.30

[jrauh01]

Hi @NavidSassan,

due to the potential issues, we have decided not to add the primary key. A solution can already be found in PR #559.

Best regards,
Johannes

[jrauh01]

After discussing with @Thomas-Gelf we have decided to proceed differently and empty the table before adding the primary key (PR #560). I apologize for the back and forth.

[NavidSassan]

no worries. can the fix be merged into master, or do you know when it will be? thanks :)

[jrauh01]

@lippserd ?