icinga/icingadb

Icinga configuration and state database supporting multiple environments

icingadb's Issues

Drop null rows

... if they're not needed anymore.

Provide -version output

Common best practices for any CLI command.

[root@icinga2-prometheus yum.repos.d]# icingadb -version
flag provided but not defined: -version
Usage of icingadb:
  -config string
    	path to config (default "icingadb.ini")

CI: test also MySQL 5.6, 5.5 and MariaDB 10.1

PostgreSQL support

In our production environments, PostgreSQL is much preferred over MySQL/MariaDB due primarily to its much more robust replication support. It would be nice to be able to use PostgreSQL instead of MariaDB for Icinga DB.

Release tests

Steps (Do this on every OS supported)

• Test snapshot packages (Icinga DB & Icinga DB Redis)
• Import MySQL Schema
• Configure Icinga 2 with Icinga DB
• Make sure Icinga DB syncs every config type, history and state
• Make sure Icinga DB Redis is only listening on localhost

Rewrite code docs

... so they comply to Go's standards.

Add percent change value for flapping_end events

The flapping history currently doesn't provide a percent_state_change value for flapping_end events. To be able to display graphically in Icinga DB Web why the flapping has ended we need both the starting percent_state_change and the ending one.

Consider moving away from JSON

... to a faster equivalent.

Schema indices

The current set of table indices used by Icinga DB Web are the following. These should be part of the initial schema import.

ALTER TABLE comment ADD INDEX idx_comment_name (name) COMMENT 'Comment detail filter';
ALTER TABLE comment ADD INDEX idx_comment_entry_time (entry_time) COMMENT 'Comment list fileted/ordered by entry_time';

ALTER TABLE downtime ADD INDEX idx_downtime_is_in_effect (is_in_effect, start_time) COMMENT 'Downtime list filtered/ordered by severity';
ALTER TABLE downtime ADD INDEX idx_downtime_name (name) COMMENT 'Downtime detail filter';

ALTER TABLE history ADD INDEX idx_history_event_time (event_time) COMMENT 'History filtered/ordered by event_time';

ALTER TABLE history ADD INDEX idx_history_acknowledgement (acknowledgement_history_id);
ALTER TABLE history ADD INDEX idx_history_comment (comment_history_id);
ALTER TABLE history ADD INDEX idx_history_downtime (downtime_history_id);
ALTER TABLE history ADD INDEX idx_history_flapping (flapping_history_id);
ALTER TABLE history ADD INDEX idx_history_notification (notification_history_id);
ALTER TABLE history ADD INDEX idx_history_state (state_history_id);

ALTER TABLE host ADD INDEX idx_host_display_name (display_name) COMMENT 'Host list filtered/ordered by display_name';
ALTER TABLE host ADD INDEX idx_host_name (name) COMMENT 'Host list filtered/ordered by name; Host detail filter';

ALTER TABLE notification_history ADD INDEX idx_notification_history_event_time (send_time DESC) COMMENT 'Notification list filtered/ordered by entry_time';

ALTER TABLE service ADD INDEX idx_service_display_name (display_name) COMMENT 'Service list filtered/ordered by display_name';
ALTER TABLE service ADD INDEX idx_service_host_id (host_id, display_name) COMMENT 'Service list filtered by host and ordered by display_name';
ALTER TABLE service ADD INDEX idx_service_name (name) COMMENT 'Service list filtered/ordered by name; Service detail filter';

ALTER TABLE user ADD INDEX idx_user_display_name (display_name) COMMENT 'User list filtered/ordered by display_name';

ALTER TABLE usergroup_member ADD UNIQUE INDEX idx_usergroup_member_user_id (user_id, usergroup_id);
ALTER TABLE usergroup_member ADD UNIQUE INDEX idx_usergroup_member_usergroup_id (usergroup_id, user_id);

ALTER TABLE host_customvar ADD UNIQUE INDEX idx_host_customvar_host_id (host_id, customvar_id);
ALTER TABLE host_customvar ADD UNIQUE INDEX idx_host_customvar_customvar_id (customvar_id, host_id);

ALTER TABLE hostgroup_member ADD UNIQUE INDEX idx_hostgroup_member_host_id (host_id, hostgroup_id);
ALTER TABLE hostgroup_member ADD UNIQUE INDEX idx_hostgroup_member_hostgroup_id (hostgroup_id, host_id);

ALTER TABLE notification_user ADD UNIQUE INDEX idx_notification_user_user_id (user_id, notification_id);
ALTER TABLE notification_user ADD UNIQUE INDEX idx_notification_user_notification_id (notification_id, user_id);

ALTER TABLE notification_usergroup ADD UNIQUE INDEX idx_notification_usergroup_usergroup_id (usergroup_id, notification_id);
ALTER TABLE notification_usergroup ADD UNIQUE INDEX idx_notification_usergroup_notification_id (notification_id, usergroup_id);

ALTER TABLE service_customvar ADD UNIQUE INDEX idx_service_customvar_service_id (service_id, customvar_id);
ALTER TABLE service_customvar ADD UNIQUE INDEX idx_service_customvar_customvar_id (customvar_id, service_id);

ALTER TABLE servicegroup_member ADD UNIQUE INDEX idx_servicegroup_member_service_id (service_id, servicegroup_id);
ALTER TABLE servicegroup_member ADD UNIQUE INDEX idx_servicegroup_member_servicegroup_id (servicegroup_id, service_id);

ALTER TABLE customvar_flat ADD INDEX idx_customvar_flat_customvar_id (customvar_id);

ALTER TABLE notification_recipient ADD UNIQUE INDEX idx_notification_recipient_user_id (user_id, notification_id);
ALTER TABLE notification_recipient ADD UNIQUE INDEX idx_notification_recipient_notification_id_user (notification_id, user_id);
ALTER TABLE notification_recipient ADD UNIQUE INDEX idx_notification_recipient_usergroup_id (usergroup_id, notification_id);
ALTER TABLE notification_recipient ADD UNIQUE INDEX idx_notification_recipient_notification_id_usergroup (notification_id, usergroup_id);

ALTER TABLE notification ADD INDEX idx_host_id (host_id);
ALTER TABLE notification ADD INDEX idx_service_id (service_id);

Consider using Miniredis in tests

This is something we wanted to do for a while, but couldn't because Miniredis didn't support Redis 5, which it now does. This would simplify our CI and packaging process. https://github.com/alicebob/miniredis

Customvars including dots

I've added some services which have customvars with a dot in their name.

In table customvar_flat they are then inserted without this dot being escaped, is this intended?

Example vars mapping:

vars = {
    "foo.bar" = "baz"
    "baz" = {
      "bar" = "foo"
    }
  }

flatnames in the database: (indistinguishable)

• foo.bar (variable name)
• baz.bar (variable path)

Comment_history: Remove comment_types downtime & flapping

Consider using go-sqlmock in tests

This would simplify our CI and packaging process, but still needs evaluation. https://github.com/DATA-DOG/go-sqlmock

Missing dependency for IcingaDB in testing repo

Describe the bug

I'm getting missed dependencies during IcingaDB installation

Resolving Dependencies
--> Running transaction check
---> Package icingadb.x86_64 0:1.0.0-0.rc1.1.el7.icinga will be installed
--> Processing Dependency: icinga-redis for package: icingadb-1.0.0-0.rc1.1.el7.icinga.x86_64
---> Package icingadb-redis.x86_64 0:1.0.0-0.rc1.1.el7.icinga will be installed
--> Processing Dependency: icinga-redis for package: icingadb-redis-1.0.0-0.rc1.1.el7.icinga.x86_64
--> Finished Dependency Resolution
Error: Package: icingadb-redis-1.0.0-0.rc1.1.el7.icinga.x86_64 (icinga-stable-release)
           Requires: icinga-redis
Error: Package: icingadb-1.0.0-0.rc1.1.el7.icinga.x86_64 (icinga-stable-release)
           Requires: icinga-redis
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

To Reproduce

1. Follow installation guide for IcingaDB: https://icinga.com/docs/icingadb/latest/doc/02-Installation/#installing-icinga-db-redis
2. Make sure that repo added, doublecheck yum.d:
   # cat /etc/yum.repos.d/ICINGA-testing.repo

[icinga-testing-builds]
name=ICINGA (testing builds for epel)
baseurl=http://packages.icinga.com/epel/$releasever/testing/
enabled=1
gpgcheck=1
gpgkey=https://packages.icinga.com/icinga.key
metadata_expire=1d

3. Run install:

yum install icingadb icingadb-redis --disablerepo="\*" --enablerepo=icinga-testing-builds

Expected behavior

Expected successful package install

Your Environment

Centos 7, Linux 3.10.0-1062.12.1.el7.x86_64

Additional context

#135 - corresponding issue

Package icinga-redis located in "release" repo but missed in "testing" repo, proposal is to have full mirror for clean tests and less confuse for new users that followed instruction and didn't add "release" repo in addition to "testing", also may help for future RC versions.

With BR,
Dmitriy.

go mod update & dependabot

I'd suggest the following changes:

• Update go modules go get -u ./...
• Enable dependabot for the repository (does automatic PRs): https://dependabot.com/go/

For an example, see https://github.com/NETWAYS/check_hp_disk_firmware and pull requests

It helps to first update manually, or dependabot will open a bunch of PRs

MySQL schema import fails on CentOS 7

Steps to reproduce

Fresh CentOS 7 Vagrant box. Snapshot repository enabled like documented in icinga2: https://icinga.com/docs/icinga2/latest/doc/21-development/#snapshot-packages-nightly-builds

yum -y install https://packages.icinga.com/epel/icinga-rpm-release-7-latest.noarch.rpm
yum -y install epel-release
yum makecache

yum install --enablerepo=icinga-snapshot-builds icingadb

Going further with the docs draft PR: https://github.com/Icinga/icingadb/pull/3/files

MariaDB install.

yum install mariadb-server mariadb
systemctl enable mariadb
systemctl start mariadb
mysql_secure_installation

# mysql -u root -p
CREATE DATABASE icingadb;
GRANT ALL ON icingadb.* TO 'icingadb'@'127.0.0.1' IDENTIFIED BY 'icingadb';
quit

Schema import.

cat /usr/share/icingadb/schema/mysql/mysql.schema.sql | mysql -p -uroot icingadb
Enter password:
ERROR 1005 (HY000) at line 6: Can't create table 'icingadb.host' (errno: 1478)

Likely related to ROW_FORMAT=dynamic.

More details

• OS: CentOS 7
• icingadb version: 0.0.0.428.28743ca4

Decide if we want to use go mod with or without vendor dir

Schema version information

There should be a table in our schema which stores information about the current database schema version. The columns version and timestamp should be enough.

Indicate overdue

Icinga 2 version and runtime information

The schema currently lacks the following information from Icinga 2:

• Version
• Start time
• The Icinga 2 endpoint for whom we have responsibility

We could rename icingadb_instance to icingadb_status and add the following columns there: icinga2_version, icinga2_start_time and icinga2_endpoint_id or something like that. icinga2_is_running may make sense too.

We would then use this information to display it in the web interface. The old view for that is Monitoring Health beneath System.

Acknowledgements history

... with acks and ack clearings in addition to the notification history.

Panic upon self-shutdown

I don't think panicking in this case is necessary. 😛

time="2020-02-17T13:39:30Z" level=panic msg="Could not connect to SQL for over 5 minutes. Shutting down..."
panic: (*logrus.Entry) (0xa5f0e0,0xc00026ad90)

goroutine 62 [running]:
github.com/sirupsen/logrus.Entry.log(0xc00009c620, 0xc000087c50, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
	/go/pkg/mod/github.com/sirupsen/[email protected]/entry.go:239 +0x2db
github.com/sirupsen/logrus.(*Entry).Log(0xc00026acb0, 0xc000000000, 0xc000311c78, 0x1, 0x1)
	/go/pkg/mod/github.com/sirupsen/[email protected]/entry.go:268 +0xeb
github.com/sirupsen/logrus.(*Logger).Log(0xc00009c620, 0x0, 0xc000311c78, 0x1, 0x1)
	/go/pkg/mod/github.com/sirupsen/[email protected]/logger.go:192 +0x7d
github.com/sirupsen/logrus.(*Logger).Panic(...)
	/go/pkg/mod/github.com/sirupsen/[email protected]/logger.go:233
github.com/sirupsen/logrus.Panic(...)
	/go/pkg/mod/github.com/sirupsen/[email protected]/exported.go:129
github.com/Icinga/icingadb/connection.(*DBWrapper).getConnectionCheckInterval(0xc00017eba0, 0xc000311d20)
	/go/src/github.com/Icinga/icingadb/connection/mysql.go:141 +0xbf
github.com/Icinga/icingadb/connection.(*DBWrapper).checkConnection(0xc00017eba0, 0xdf8475801, 0xc000186500)
	/go/src/github.com/Icinga/icingadb/connection/mysql.go:162 +0x20f
github.com/Icinga/icingadb/connection.NewDBWrapper.func1(0xc00017eba0)
	/go/src/github.com/Icinga/icingadb/connection/mysql.go:101 +0x30
created by github.com/Icinga/icingadb/connection.NewDBWrapper
	/go/src/github.com/Icinga/icingadb/connection/mysql.go:99 +0x223
exit status 2

Do not write history/state/overdue when not reponsible

Concept:

• Only the active Icinga DB writes into MySQL
• The active instance also writes the last Redis stream ID into something like a history_state table
• The inactive instance removes everything before the stream ID plus a time buffer from its own stream (using https://redis.io/commands/xrange)

Write all combinations of bitmask columns if they're optional

This is specifically about the states and types columns of tables notification and user.

I'd like to have 63 for states and 511 for types in both tables as value if the default null is configured for the respective object.

The current state (0 if it's null) requires that the user knows about that if nothing is set all is included. So say someone wants to list all users notified for ok states this is required: states=ok|states=0

I'd like to reduce this to states=ok, which would be possible if this gets implemented.

Schema: environment_id should always be directly beneath id

Deprecation notices on MySQL8

Got several warnings while importing the schema on MySQL 8. They're as follows but get repeated multiple times.

'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.

Integer display width is deprecated and will be removed in a future release.

UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.

History: Only insert one Flapping & Acknowledgement row

We currently insert one row for start/set and one row for stop/clear. The first row could be updated/replaced using the timestamp.

Depends on Icinga/icinga2#7689

Graduate debug logs into debug and trace

Between the years I accidentally discovered that Logrus has also the log level "trace" below debug.

Look at the debug logs, decide, reason and patch which ones shall be trace ones.

Add fields to our log messages

... instead of formatting the variables into the message text.

New table for notification recipients

Please add a new table which includes the same rows of tables notification_user and notification_usergroup. (Basically a union of both tables) Plus indirect relationships.

In the end there should be a row for each notification <-> user and notification <-> usergroup relationship. Also for each notification <-> user <-> usergroup and notification <-> usergroup <-> user indirect relationship.

e.g.

CREATE TABLE notification_recipient (
  id binary(20) NOT NULL COMMENT 'sha1(environment.name + notification_id + (user_id | usergroup_id))',
  environment_id binary(20) NOT NULL COMMENT 'environment.id',
  notification_id binary(20) NOT NULL COMMENT 'notification.id',
  user_id binary(20) NULL COMMENT 'user.id',
  usergroup_id binary(20) NULL COMMENT 'usergroup.id',

  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

Make our counters atomic

... and get rid of the locks.

Error 1264: Out of range value for column 'argument_order' at row 163

Hi,
I installed all the release candidate versions of icinga2, icingaweb2, icingadb ...
After starting icingadb, it crashes a few minutes later with a error messages similar to this:
FATA[0005] Error 1264: Out of range value for column 'argument_order' at row 148

The row number changes sometimes.

I changed the table like this and now its working:
ALTER TABLE checkcommand_argument MODIFY argument_order INTEGER;

Icinga 2 should not write every runtime update into one pub/sub channel

This should be split into one channel per object type

Add & check Redis schema version

The Redis schema version should be set to v1 in our Redis heartbeat. This should be checked by Icinga DB.

History: host_id should be NOT NULL and always set

This allows us to directly have a reference to a services host in every history entry.

Add state information to flapping history

The flapping history currently doesn't include any way to get the object's state at the time the flapping started/stopped.

Please add the following to the flapping history:

• output
• long_output
• state

If possible (and necessary), not just the state but also these:

• state_type
• soft_state
• hard_state
• previous_soft_state
• previous_hard_state
• attempt
• max_check_attempts

Though if flapping doesn't apply to soft states, these and related columns may be omitted.

Populate environment table

Host/Service: Remove customvar_/group_checksum

Those columns are currently unused and we don't plan to use them in the future.

Replace timers with sleeps

... where easily possible.

Replace maps with hardcoded keys

... with structs.

state_sync can be overwritten by initial config sync

If a new checkable has been created, we do 2 things:

• Insert state (Pending) into redis -> Synced by initial config sync
• Schedule first check and insert result into redis -> Synced by continuous state sync

If the first check result gets synced by IcingaDB right before the initial pending state gets synced, the first check result gets overwritten (race condition).

Decide how to insert and upgrade SQL schema

Add License (GPL v2+)

Redis Sentinel support

Our production Redis clusters use Sentinel for High Availability. Please add Sentinel support so that Icinga DB can automatically route requests to active nodes in the cluster as necessary.

Flapping_history: Remove event_type

This is not used and already existent in our main history table.

Millisecond values should not be float32

In many cases we use float32 for our millisecond values. This happened because we used seconds at some point.

icinga-redis dependency package is missing

Hi,

I tried to install IcingaDB but got following error from yum:

# yum install icingadb
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.plusline.net
 * centos-sclo-rh: ftp.plusline.net
 * centos-sclo-sclo: mirror.23media.com
 * epel: ftp.plusline.net
 * extras: mirror.checkdomain.de
 * updates: ftp.plusline.net
Resolving Dependencies
--> Running transaction check
---> Package icingadb.x86_64 0:1.0.0-0.rc1.1.el7.icinga will be installed
--> Processing Dependency: icinga-redis for package: icingadb-1.0.0-0.rc1.1.el7.icinga.x86_64
--> Finished Dependency Resolution
Error: Package: icingadb-1.0.0-0.rc1.1.el7.icinga.x86_64 (icinga-testing-builds)
           Requires: icinga-redis
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

CentOS Linux release 7.6.1810 (Core)

Greets,

instance table not updated after table recreating/truncate

Stopped mysql, dropped the database and started from scratch. After this finished icingadb (which was running during the time) reconnected but did not touch the instance table at all. (No entries) Only after restarting icingadb the table was populated.

Signal handling & shutdown message

Notification_history: Rename event_time to send_time