Daemon erroring out while using PXC during setup
p4k8 opened this issue · 2 comments
Expected Behavior
vSphereDbDaemon
should be able to load DB configuration and run without database errors while using Galera based Percona Xtradb cluster as a database backend for HA purposes.
Current Behavior
After module installation and creation of the database on default config PXC cluster attempt to run /usr/bin/icingacli vspheredb daemon run
in terminal will make it run in foreground without exiting while outputting following error every few seconds:
notice: [configwatch] DB configuration loaded
notice: [db] sending DB config to child process
notice: [db] Running DB cleanup (this could take some time)
error: [configwatch] Sending DB Config failed: SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table (vspheredb.vspheredb_daemonlog) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER, query was: DELETE FROM vspheredb_daemonlog WHERE ( NOT EXISTS (
SELECT 1 FROM vspheredb_daemon d
WHERE d.instance_uuid = vspheredb_daemonlog.instance_uuid
)) in /usr/share/icinga-php/vendor/vendor/shardj/zf1-future/library/Zend/Db/Statement/Pdo.php(235)
At the same time icingaweb shows that daemon isn't running.
Possible Solution
Possible fix:
Adding explicit primary keys to tables.
Possible cause:
https://docs.percona.com/percona-xtradb-cluster/8.0/strict-mode.html#tables-without-primary-keys
Percona XtraDB Cluster cannot properly propagate certain write operations to tables that do not have primary keys defined. Undesirable operations include data manipulation statements that perform writing to table (especially
DELETE
).
Possible workarounds:
- Put strict mode on PXC cluster-wide on permissive instead of enforcing.
- Use group replication based solution for database HA
- Change HA MySQL database solution
Steps to Reproduce (for bugs)
- Follow installation docs
- Create a database on Percona Xtradb 8.0.32 cluster on Debian 12 with default database server configuration
- Successfully add said database as resource in icingaweb
- Press "Store configuration" button in Database resource window and see green checkmark message with "Configuration has been stored" text
- Try to run
sudo /usr/bin/icingacli vspheredb daemon run
on the icinga server, get error messages as described.
Your Environment
- VMware vCenter®/ESXi™-Version: not applicable
- Version/GIT-Hash of this module: 1.7.1
- Icinga Web 2 version: 2.12.0
- Operating System and version: Debian 12.4
- Webserver, PHP versions: Apache 2.4, PHP 8.2
- Percona Xtradb version: 8.0.32
To be fair vspheredb_daemonlog
actually has primary key defined in schema/mysql.sql
Maybe it doesn't like multiple columns in primary key
Upgrading to master solved the issue.