icingadb-migrate: can't insert because key constraint fails "fk_history_flapping_history"

[xeiss]

Describe the bug

I wanted to migrate my IDO from 1. Januar 2021 until I started the icingadb (maybe more after that). But the icingadb-migrate tool stops with an Insert issue and so I can not migrate the important downtime and state infos. Here is the output from my last start with using the already created cache file.

# icingadb-migrate -c icingadb-migration.yml -t ~/icingadb-migration.cache
2022-12-19T10:42:24.965+0100    INFO    icingadb-migrate/main.go:78     Starting IDO to Icinga DB history migration
2022-12-19T10:42:24.965+0100    INFO    icingadb-migrate/main.go:175    Connecting to databases
2022-12-19T10:42:24.971+0100    INFO    icingadb-migrate/main.go:141    Preparing cache
2022-12-19T10:42:24.975+0100    INFO    icingadb-migrate/main.go:93     Computing progress
2022-12-19T10:42:26.232+0100    INFO    icingadb-migrate/main.go:330    Counted migrated IDO events     {"type": "notification", "migrated": 878, "total": 878}
2022-12-19T10:42:26.245+0100    INFO    icingadb-migrate/main.go:330    Counted migrated IDO events     {"type": "ack & comment", "migrated": 3306, "total": 3306}
2022-12-19T10:42:26.290+0100    INFO    icingadb-migrate/main.go:330    Counted migrated IDO events     {"type": "flapping", "migrated": 0, "total": 32270}
2022-12-19T10:42:26.447+0100    INFO    icingadb-migrate/main.go:330    Counted migrated IDO events     {"type": "downtime", "migrated": 0, "total": 108008}
2022-12-19T10:42:26.691+0100    INFO    icingadb-migrate/main.go:330    Counted migrated IDO events     {"type": "state", "migrated": 0, "total": 739982}
2022-12-19T10:42:26.691+0100    INFO    icingadb-migrate/main.go:103    Filling cache
flapping 100 %    0s  0/s
notification 100 %    0s  0/s
state 100 %    0s  0/s
2022-12-19T10:42:28.539+0100    INFO    icingadb-migrate/main.go:106    Actually migrating
ack & comment 100 %    0s  0/s
downtime   0 % [---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0s  0/s
flapping   0 % [---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0s  0/s
notification 100 %    0s  0/s
state   0 % [------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0s  0/s
2022-12-19T10:42:29.201+0100    FATAL   icingadb-migrate/main.go:449    Error 1452: Cannot add or update a child row: a foreign key constraint fails ("icingadb"."history", CONSTRAINT "fk_history_flapping_history" FOREIGN KEY ("flapping_history_id") REFERENCES "flapping_history" ("id") ON DELETE CASCADE)
can't perform "INSERT INTO \"history\" (\"id\", \"host_id\", \"event_type\", \"environment_id\", \"endpoint_id\", \"object_type\", \"service_id\", \"flapping_history_id\", \"event_time\") VALUES (:id, :host_id, :event_type, :environment_id, :endpoint_id, :object_type, :service_id, :flapping_history_id, :event_time) ON DUPLICATE KEY UPDATE \"id\" = \"id\""
github.com/icinga/icingadb/internal.CantPerformQuery
        github.com/icinga/icingadb/internal/internal.go:30
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.1.1.1
        github.com/icinga/icingadb/pkg/icingadb/db.go:394
github.com/icinga/icingadb/pkg/retry.WithBackoff
        github.com/icinga/icingadb/pkg/retry/retry.go:45
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.1.1
        github.com/icinga/icingadb/pkg/icingadb/db.go:389
golang.org/x/sync/errgroup.(*Group).Go.func1
        golang.org/x/[email protected]/errgroup/errgroup.go:57
runtime.goexit
        runtime/asm_amd64.s:1594
can't retry
github.com/icinga/icingadb/pkg/retry.WithBackoff
        github.com/icinga/icingadb/pkg/retry/retry.go:64
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.1.1
        github.com/icinga/icingadb/pkg/icingadb/db.go:389
golang.org/x/sync/errgroup.(*Group).Go.func1
        golang.org/x/[email protected]/errgroup/errgroup.go:57
runtime.goexit
        runtime/asm_amd64.s:1594
can't perform DML
main.migrateOneType[...].func3
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:449
main.sliceIdoHistory[...]
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:135
main.migrateOneType[...]
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:422
main.glob..func5
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:280
main.migrate.func1
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:360
main.historyTypes.forEach.func1
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:232
golang.org/x/sync/errgroup.(*Group).Go.func1
        golang.org/x/[email protected]/errgroup/errgroup.go:57
runtime.goexit
        runtime/asm_amd64.s:1594        {"backend": "Icinga DB", "op": "INSERT IGNORE", "table": "history"}
main.migrateOneType[...].func3
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:449
main.sliceIdoHistory[...]
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:135
main.migrateOneType[...]
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:422
main.glob..func5
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:280
main.migrate.func1
        github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:360
main.historyTypes.forEach.func1
        github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:232
golang.org/x/sync/errgroup.(*Group).Go.func1
        golang.org/x/[email protected]/errgroup/errgroup.go:57

Possible Reason

I am not sure, which entries lead to this error, but my best guess is, that it is a flapping entry which has a deleted host. Thats why he can not insert a line in the icingadb because the host isn't migrated because it is deleted.

Workaround

Sure I could drop the flapping entrys in IDO, that would probaly fix this issue. But I think this could happens to more people.

Possible Fix

When the Host is deleted don't migrate his flapping history or also migrate the deleted host.

Environment

• Icinga DB version: 1.1.0-1+debian11
• Icinga 2 version: 2.13.6-2+debian11
• MariaDB Server: 1:10.5.15-0+deb11u1
• Operating System and version: Debian 11

[Al2Klimov]

Cut off start events as in my linked PR can't be a problem:

icingadb/cmd/icingadb-migrate/convert.go

Lines 421 to 426 in 5ea6831

	if row.EventType == 1001 { // end
	var ok bool
	start, ok = cachedById[row.FlappinghistoryId]
	if !ok {
	continue

I'm afraid I don't have any explanation for this.

[julianbrost]

I found the missing puzzle piece: there is no inconsistency in the IDO data causing this, the issue is in the migration tool when setting ido.from in the config to a time that's within the flapping period of any object.

So for example, this freshly generated flapping history table:

MariaDB [icingaido]> select * from icinga_flappinghistory order by event_time;
+--------------------+-------------+---------------------+-----------------+------------+-------------+---------------+-----------+----------------------+---------------+----------------+--------------+---------------------+--------------------+
| flappinghistory_id | instance_id | event_time          | event_time_usec | event_type | reason_type | flapping_type | object_id | percent_state_change | low_threshold | high_threshold | comment_time | internal_comment_id | endpoint_object_id |
+--------------------+-------------+---------------------+-----------------+------------+-------------+---------------+-----------+----------------------+---------------+----------------+--------------+---------------------+--------------------+
|                  2 |           1 | 2023-07-26 11:56:50 |          119224 |       1000 |           0 |             1 |       485 |                 33.9 |            25 |             30 | NULL         |                   0 |                239 |
|                  3 |           1 | 2023-07-26 11:58:34 |          817096 |       1001 |           1 |             1 |       485 |                   21 |            25 |             30 | NULL         |                   0 |                239 |
+--------------------+-------------+---------------------+-----------------+------------+-------------+---------------+-----------+----------------------+---------------+----------------+--------------+---------------------+--------------------+
2 rows in set (0.000 sec)

Combined with this setting in the config:

ido:
  from: 1690372620 # 2023-07-26 11:57:00 (UTC), i.e. in between the timestamps above

Is what can be used to reproduce the error.

[xeiss]

Thanks for your work.
I can confirm, that with IcingaDB 1.1.1 the migration now works flawless on my production server.