mirror of
https://github.com/Icinga/icingadb.git
synced 2026-02-03 20:40:34 -05:00
59685cd623
Historical data from an older Icinga 2 installation contained NULL
values for the name column in some rows of the icinga_commenthistory and
icinga_downtimehistory tables.
Normally this field contains something like
${name1}!${name2}!${unique_value} where the $unique_value is based on a
timestamp for older entries and a UUID for newer ones. For a concrete
example, this could be "host.example.com!ping6!123…".
Unfortunately, using an empty string for these NULL values will cause an
error later because the new primary key will be calculated based on it.
Therefore, a new deterministic name is generated based on the primary
keys and the known name1 and name2 values.
Closes #766.
15 lines
1.2 KiB
SQL
15 lines
1.2 KiB
SQL
SELECT dh.downtimehistory_id, UNIX_TIMESTAMP(dh.entry_time) entry_time, dh.author_name, dh.comment_data,
|
|
dh.is_fixed, dh.duration, UNIX_TIMESTAMP(dh.scheduled_start_time) scheduled_start_time,
|
|
COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) scheduled_end_time, dh.was_started,
|
|
COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) actual_start_time, dh.actual_start_time_usec,
|
|
COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) actual_end_time, dh.actual_end_time_usec, dh.was_cancelled,
|
|
COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) trigger_time,
|
|
COALESCE(dh.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', dh.downtimehistory_id, '-', dh.object_id)) name,
|
|
o.objecttype_id, o.name1, COALESCE(o.name2, '') name2, COALESCE(sd.name, '') triggered_by
|
|
FROM icinga_downtimehistory dh USE INDEX (PRIMARY)
|
|
INNER JOIN icinga_objects o ON o.object_id=dh.object_id
|
|
LEFT JOIN icinga_scheduleddowntime sd ON sd.scheduleddowntime_id=dh.triggered_by_id
|
|
WHERE dh.downtimehistory_id BETWEEN :fromid AND :toid
|
|
AND dh.downtimehistory_id > :checkpoint -- where we were interrupted
|
|
ORDER BY dh.downtimehistory_id -- this way we know what has already been migrated from just the last row's ID
|
|
LIMIT :bulk
|