Certain Icinga 2 object fields of a floating type are incorrectly stored
as unsigned integers in the schema. Since none of those columns are in
the history tables, changing them was considered not too invasive.
Furthermore, some struct fields were changed from "float64" to
"types.Float", since the SQL schema supports NULL values.
Fixes#882.
Add a new INDEX to user_notification_history.notification_history_id to
speed up the notification history retention.
This column is a FOREIGN KEY to notification_history with a "ON DELETE
CASCADE" clause. The notification_history table can be cleaned up by the
notification retention, resulting in lots of DELETE queries. However,
without the INDEX, the DELETE CASCADE might result in a full table scan
for each retention operation.
I have further checked every other retention table, but no other had the
same issue. Most retention tables have their primary key referenced by a
foreign key in the history table, where indexes were already present.
Many thanks to @rezemble for both reporting this issue and coming up
with the exact solution.
Fixes#1003.
During #792, the storage mode for this column got lost. After further
inspection, setting a distinct storage mode for a column of type varchar
seems unnecessary.
From the beginning, the Icinga DB schema allowed 64 characters for both
the command arguments and environment variable names[0]. In particular,
this affects CheckCommand, EventCommand and NotificationCommand Icinga 2
objects.
But if a command with either an argument key or an environment variable
that is longer than 64 characters was defined in Icinga 2, Icinga DB
will try to insert it into the database and may end up crashing.
Although it may seem large enough, it is sometimes exceeded.
After evaluating that there was no technical limitation[1], the limit
was increased to 255 characters. This limit was chosen over the wider
text type as it allows indexes in the future and requires less space.
For example, the following CheckCommand was not possible before:
> object CheckCommand "icingadb-i791" {
> import "plugin-check-command"
> command = [ "/bin/true" ]
> env = {
> "THAT_ARE_64_AS_WOW_AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA" = "huhu"
> }
> arguments = {
> "java.class.that.was.used.as.an.argument.that.was.eighty.seven.characters.long.and.broke" = {
> value = "F"
> }
> }
> }
Another thing was a type difference between the MySQL and PostgreSQL
schemas. While the MySQL schema defined argument_key_override as
varchar(64), in PostgreSQL it was a citext. So it was changed to
varchar(255) in MySQL and kept as it was in PostgreSQL.
Closes#791.
[0]: 05d5e97dd5
[1]: https://github.com/Icinga/icingadb/issues/791#issuecomment-2291326687
The icon_image_alt column in both the host and service tables contains
an image alt text. However, because it is defined as a varchar(32), many
alt texts do not fit. The type has been expanded to text, as with most
free text fields.
Closes#752.
When defining a TimePeriod, the maximum length of a range value was
capped at 255 characters. This limitation has now also been removed by
switching to the Text type.
Closes#724.
While re-reading the schema, I stumbled upon some missing
properties_checksum comments that were also added.
A float isn't necessary as in Icinga 2 Checkable#max_check_attempts and
check_attempt are ints. But uint8 isn't enough for e.g. 1 check/s to get
HARD after 5m (300s > 255).
The final division within the get_sla_ok_percent SQL function in its
PostgreSQL implementation silently truncated decimal places. An explicit
decimal cast resulted for this equation resulted in a decimal value.
To both verify and catch this in the future, a test with odd numbers was
added. This already succeeded for MySQL, but needed the modified schema
for PostgreSQL.
Closes#648.
This improves the resulting sort order when `ORDER BY event_time, event_type`
is used. `state_change` comes first as it can cause many of the other events
like trigger downtimes, remove acknowledgements and send notifications.
Similarly, `notification` comes last as any other event can result in a
notification. This will result in history events for scenarios like state
changes, triggers downtime, sends downtime start notification being sorted in
that order.
Apart from that, end events sort before the corresponding start events as any
ack/comment/downtime/flapping period should last for more than a millisecond,
therefore if there should be two events within the same millisecond, the end
event corresponds to the older period and is sorted first.
When UPSERT and DELETE statements are executed at the same time, a
deadlock can occur if both want to get an exclusive lock on one of the
PRIMARY KEY index pages. This happens with DELETE statements when there
is no suitable index for the columns used in the WHERE clause, which is
true for our history retention queries since commit eccac78. This PR
fixes the problem by adding a suitable index for the columns used in
these queries.
At the moment Icinga DB Web doesn't know the column types, so it sends
SQL queries with LIKE operators for all suggestions in the search bar,
which fails for numeric and enum types on PostgreSQL. To support this,
the LIKE operator (internally translated to ~~) is overloaded. Note
that this is only a temporary solution until Icinga DB Web provides
column type support.
Before:
idb=# select * from icingadb_instance where responsible = 'y';
ERROR: operator does not exist: boolenum = unknown
LINE 1: select * from icingadb_instance where responsible = 'y';
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
idb=#
refs #136
