Using check_interval < 1s crashes Icinga DB

[yhabteab]

Describe the bug

All timestamps < 1s used as check interval result in floating point numbers when converted to timestamp aka seconds. However, while such values can be encoded/decoded into the corresponding struct field without problems, it crashes when trying to insert 0.001 as check_interval value, which is of type bigint. There was a similar issue in Icinga DB Web Icinga/icingadb-web#910, but apparently MySQL/MariaDB simply round these numbers down to 0, causing a division by zero error instead of rejecting them like PostgreSQL does. We observed this last time during a debugging session with @nilmerg a mysterious crash by such a check interval.

2025-01-21T15:39:18.592+0100	WARN	config-sync	Aborted config sync after 10.086498s
2025-01-21T15:39:19.539+0100	FATAL	main	pq: invalid input syntax for type bigint: "0.001"
can't perform "INSERT INTO \"host\" (\"check_timeperiod_id\", \"command_endpoint_id\", \"eventcommand_id\", \"display_name\", \"passive_checks_enabled\", \"address6\", \"flapping_enabled\", \"flapping_threshold_high\", \"icon_image_alt\", \"active_checks_enabled\", \"zone_name\", \"is_volatile\", \"flapping_threshold_low\", \"icon_image_id\", \"max_check_attempts\", \"name_ci\", \"notifications_enabled\", \"check_retry_interval\", \"eventcommand_name\", \"address\", \"checkcommand_name\", \"properties_checksum\", \"name_checksum\", \"check_interval\", \"command_endpoint_name\", \"event_handler_enabled\", \"zone_id\", \"name\", \"notes\", \"environment_id\", \"id\", \"notes_url_id\", \"affected_children\", \"check_timeout\", \"checkcommand_id\", \"action_url_id\", \"perfdata_enabled\", \"address_bin\", \"address6_bin\", \"check_timeperiod_name\") VALUES (:check_timeperiod_id, :command_endpoint_id, :eventcommand_id, :display_name, :passive_checks_enabled, :address6, :flapping_enabled, :flapping_threshold_high, :icon_image_alt, :active_checks_enabled, :zone_name, :is_volatile, :flapping_threshold_low, :icon_image_id, :max_check_attempts, :name_ci, :notifications_enabled, :check_retry_interval, :eventcommand_name, :address, :checkcommand_name, :properties_checksum, :name_checksum, :check_interval, :command_endpoint_name, :event_handler_enabled, :zone_id, :name, :notes, :environment_id, :id, :notes_url_id, :affected_children, :check_timeout, :checkcommand_id, :action_url_id, :perfdata_enabled, :address_bin, :address6_bin, :check_timeperiod_name) ON CONFLICT ON CONSTRAINT pk_host DO UPDATE SET \"check_timeperiod_id\" = EXCLUDED.\"check_timeperiod_id\", \"command_endpoint_id\" = EXCLUDED.\"command_endpoint_id\", \"eventcommand_id\" = EXCLUDED.\"eventcommand_id\", \"display_name\" = EXCLUDED.\"display_name\", \"passive_checks_enabled\" = EXCLUDED.\"passive_checks_enabled\", \"address6\" = EXCLUDED.\"address6\", \"flapping_enabled\" = EXCLUDED.\"flapping_enabled\", \"flapping_threshold_high\" = EXCLUDED.\"flapping_threshold_high\", \"icon_image_alt\" = EXCLUDED.\"icon_image_alt\", \"active_checks_enabled\" = EXCLUDED.\"active_checks_enabled\", \"zone_name\" = EXCLUDED.\"zone_name\", \"is_volatile\" = EXCLUDED.\"is_volatile\", \"flapping_threshold_low\" = EXCLUDED.\"flapping_threshold_low\", \"icon_image_id\" = EXCLUDED.\"icon_image_id\", \"max_check_attempts\" = EXCLUDED.\"max_check_attempts\", \"name_ci\" = EXCLUDED.\"name_ci\", \"notifications_enabled\" = EXCLUDED.\"notifications_enabled\", \"check_retry_interval\" = EXCLUDED.\"check_retry_interval\", \"eventcommand_name\" = EXCLUDED.\"eventcommand_name\", \"address\" = EXCLUDED.\"address\", \"checkcommand_name\" = EXCLUDED.\"checkcommand_name\", \"properties_checksum\" = EXCLUDED.\"properties_checksum\", \"name_checksum\" = EXCLUDED.\"name_checksum\", \"check_interval\" = EXCLUDED.\"check_interval\", \"command_endpoint_name\" = EXCLUDED.\"command_endpoint_name\", \"event_handler_enabled\" = EXCLUDED.\"event_handler_enabled\", \"zone_id\" = EXCLUDED.\"zone_id\", \"name\" = EXCLUDED.\"name\", \"notes\" = EXCLUDED.\"notes\", \"environment_id\" = EXCLUDED.\"environment_id\", \"id\" = EXCLUDED.\"id\", \"notes_url_id\" = EXCLUDED.\"notes_url_id\", \"affected_children\" = EXCLUDED.\"affected_children\", \"check_timeout\" = EXCLUDED.\"check_timeout\", \"checkcommand_id\" = EXCLUDED.\"checkcommand_id\", \"action_url_id\" = EXCLUDED.\"action_url_id\", \"perfdata_enabled\" = EXCLUDED.\"perfdata_enabled\", \"address_bin\" = EXCLUDED.\"address_bin\", \"address6_bin\" = EXCLUDED.\"address6_bin\", \"check_timeperiod_name\" = EXCLUDED.\"check_timeperiod_name\""
github.com/icinga/icinga-go-library/database.CantPerformQuery
	/Users/yhabteab/Workspace/go/icinga-go-library/database/utils.go:16
github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2.1
	/Users/yhabteab/Workspace/go/icinga-go-library/database/db.go:431
github.com/icinga/icinga-go-library/retry.WithBackoff
	/Users/yhabteab/Workspace/go/icinga-go-library/retry/retry.go:65
github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
	/Users/yhabteab/Workspace/go/icinga-go-library/database/db.go:426
golang.org/x/sync/errgroup.(*Group).Go.func1
	/Users/yhabteab/Workspace/go/pkg/mod/golang.org/x/[email protected]/errgroup/errgroup.go:78
runtime.goexit
	/opt/homebrew/Cellar/go/1.23.4/libexec/src/runtime/asm_arm64.s:1223
retry deadline exceeded
github.com/icinga/icinga-go-library/retry.WithBackoff
	/Users/yhabteab/Workspace/go/icinga-go-library/retry/retry.go:100
github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
	/Users/yhabteab/Workspace/go/icinga-go-library/database/db.go:426
golang.org/x/sync/errgroup.(*Group).Go.func1
	/Users/yhabteab/Workspace/go/pkg/mod/golang.org/x/[email protected]/errgroup/errgroup.go:78
runtime.goexit
	/opt/homebrew/Cellar/go/1.23.4/libexec/src/runtime/asm_arm64.s:1223
exit status 1

[yhabteab]

Note that this is not limited to the check_interval column only, but some other columns such as check_timeout, check_retry_interval etc. also suffer from the same problem.