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).
`CURRENT_TIMESTAMP()` an alias for `NOW()` returns the current date and
time in the format `YYYY-MM-DD hh:mm:ss` using the session time zone.
Since we are using numeric context, the value is stored in the format
`YYYYMMDDhhmmss`. But actually we want to set a (millisecond) UNIX
timestamp here, so we need to use `UNIX_TIMESTAMP()` instead.
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.
Earlier we did not have any foreign keys for history table. But when we delete a record from the parent
history table the corresponding records in their child tables must also be deleted. This is done with the
introduction of foreign key constraints with on cascade delete.
... to make MySQL storage capabilities of likely large text columns
more similar (16MB -> 4GB) to (upcoming) Postgres ones (unlimited)
and not to have to pre-truncate anything.
refs #260
As of version 8, MySQL is stricter when it comes to setting system
and session variables and requires that the user importing our schema
be granted the SESSION_VARIABLES_ADMIN permission. Although the session
scope is already the default, we will now use SET SESSION for the modes
in our scheme for the sake of clarity.
