DB.NamedBulkExec: Honor MySQL's max_allowed_packet

[oxzi]

An Icinga DB crash was reported in the Icinga Community Forum, https://community.icinga.com/t/icingadb-max-allowed-packet-exceeded/14414. After a temporary Icinga DB downtime due to a version update, the Redis contained too much data for the state_history table, exceeding MySQL's max_allowed_packet value of 64 MiB.

Dec 22 10:13:58 skinner2.backbone.admin icingadb[1507116]: Error 1105 (HY000): Parameter of prepared statement which is set through mysql_send_long_data() is longer than 'max_allowed_packet' bytes
                                                           can't perform "INSERT INTO \"state_history\" (\"state_type\", \"hard_state\", \"previous_soft_state\", \"check_attempt\", \"soft_state\", \"long_output\", \"object_type\", \"host_id\", \"check_source\", \"endpoint_id\", \"service_id\", \"event_time\", \"previous_hard_state\", \"output\", \"max_check_attempts\", \"id\", \"scheduling_source\", \"environment_id\") VALUES (:state_type,:hard_state,:previous_soft_state,:check_attempt,:soft_state,:long_output,:object_type,:host_id,:check_source,:endpoint_id,:service_id,:event_time,:previous_hard_state,:output,:max_check_attempts,:id,:scheduling_source,:environment_id) ON DUPLICATE KEY UPDATE \"id\" = VALUES(\"id\")"
                                                           github.com/icinga/icinga-go-library/database.CantPerformQuery
                                                                   github.com/icinga/[email protected]/database/utils.go:16
                                                           github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2.1
                                                                   github.com/icinga/[email protected]/database/db.go:535
                                                           github.com/icinga/icinga-go-library/retry.WithBackoff
                                                                   github.com/icinga/[email protected]/retry/retry.go:65
                                                           github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
                                                                   github.com/icinga/[email protected]/database/db.go:530
                                                           golang.org/x/sync/errgroup.(*Group).Go.func1
                                                                   golang.org/x/[email protected]/errgroup/errgroup.go:78
                                                           runtime.goexit
                                                                   runtime/asm_amd64.s:1700
                                                           retry deadline exceeded
                                                           github.com/icinga/icinga-go-library/retry.WithBackoff
                                                                   github.com/icinga/[email protected]/retry/retry.go:100
                                                           github.com/icinga/icinga-go-library/database.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
                                                                   github.com/icinga/[email protected]/database/db.go:530
                                                           golang.org/x/sync/errgroup.(*Group).Go.func1
                                                                   golang.org/x/[email protected]/errgroup/errgroup.go:78
                                                           runtime.goexit
                                                                   runtime/asm_amd64.s:1700

The reporter solved this issue by performing a FLUSHALL in the Redis.

However, as I wrote in the thread, "Icinga DB always sets max_allowed_packet to 64 MiB, as this is what the underlying MySQL client library does, https://github.com/go-sql-driver/mysql/blob/4395c45fd098a81c5251667cda111f94c693ab14/dsn.go#L88. However, if I read the following MySQL bug - https://bugs.mysql.com/bug.php?id=83958 - correctly, prepared statements may easily exceed this limit, as it seems to be the case for your crash."

Without having looked too closely, the DB.NamedBulkExec method allows bulking. Maybe we can adjust something over there. I have mostly created this issue here to not lose this over in the community forum.