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.
13 lines
835 B
SQL
13 lines
835 B
SQL
SELECT ch.commenthistory_id, UNIX_TIMESTAMP(ch.entry_time) entry_time,
|
|
ch.entry_time_usec, ch.entry_type, ch.author_name, ch.comment_data, ch.is_persistent,
|
|
COALESCE(UNIX_TIMESTAMP(ch.expiration_time), 0) expiration_time,
|
|
COALESCE(UNIX_TIMESTAMP(ch.deletion_time), 0) deletion_time,
|
|
ch.deletion_time_usec,
|
|
COALESCE(ch.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', ch.commenthistory_id, '-', ch.object_id)) name,
|
|
o.objecttype_id, o.name1, COALESCE(o.name2, '') name2
|
|
FROM icinga_commenthistory ch USE INDEX (PRIMARY)
|
|
INNER JOIN icinga_objects o ON o.object_id=ch.object_id
|
|
WHERE ch.commenthistory_id BETWEEN :fromid AND :toid
|
|
AND ch.commenthistory_id > :checkpoint -- where we were interrupted
|
|
ORDER BY ch.commenthistory_id -- this way we know what has already been migrated from just the last row's ID
|
|
LIMIT :bulk
|