Error 1406 (22001): Data too long for column 'icon_image_alt'

[TheCry]

Describe the bug

After I created a new server in the direector and apply, the Icinga DB daemon crashes

icingadb[2584879]: Error 1406 (22001): Data too long for column 'icon_image_alt' at row 1
                                            can't perform "INSERT INTO \"host\" (\"check_timeout\", \"active_checks_enabled\", \"command_e>
                                            github.com/icinga/icingadb/internal.CantPerformQuery
                                                    github.com/icinga/icingadb/internal/internal.go:30
                                            github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.>
                                                    github.com/icinga/icingadb/pkg/icingadb/db.go:412
                                            github.com/icinga/icingadb/pkg/retry.WithBackoff
                                                    github.com/icinga/icingadb/pkg/retry/retry.go:60
                                            github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
                                                    github.com/icinga/icingadb/pkg/icingadb/db.go:407
                                            golang.org/x/sync/errgroup.(*Group).Go.func1
                                                    golang.org/x/sync@v0.7.0/errgroup/errgroup.go:78
                                            runtime.goexit
                                                    runtime/asm_amd64.s:1695
                                            retry deadline exceeded
                                            github.com/icinga/icingadb/pkg/retry.WithBackoff
                                                    github.com/icinga/icingadb/pkg/retry/retry.go:95
                                            github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
                                                    github.com/icinga/icingadb/pkg/icingadb/db.go:407
                                            golang.org/x/sync/errgroup.(*Group).Go.func1
                                                    golang.org/x/sync@v0.7.0/errgroup/errgroup.go:78
                                            runtime.goexit
                                                    runtime/asm_amd64.s:1695

To Reproduce

Create a server in the director and use a long "image alternate name" (this is my alternate name: aes-srv-lokal10.domai.tld (AES Application Enablement - AES 3.1)

Expected behavior

A clear and concise description of what you expected to happen.

Your Environment

Include as many relevant details about the environment you experienced the problem in

1. Icinga DB version: 1.2.0
2. Icinga DB Web: 1.1.2
3. Icinga 2 version: 2.14.2
4. Operating System and version: Debian 11

Additional context

I'd checked the DB of ICINGA DB the field

Is it possible to set a higher value?

[K0nne]

The source of the issue seems to be #699. Before activating strict mode, too long values were silently truncated.

[oxzi]

Thanks for your report. We're going to take a closer look at it soon.

@K0nne identified the SQL strict mode as its cause in #754 (comment). However, just disabling the strict mode would hide the actual problem which was just revealed.

Edit: @K0nne just commented here as well, thanks!

[TheCry]

We now have the MariaDB, which runs on top of the IcingaDB, running without a "strict_mode".
One more thing we noticed... If you create a server in the Director and extend the display name (hostname = my-server.my-domain.tld) and display name => my-server.my-domain.tld (function of the server), the server does not appear in the WebUi under the hosts. Only the assigned services appear. If you click on these services, I get the error from this ticket: Icinga/icingadb-web#959

If I set the display name back to "my-server.my-domain.tld", the WebUi works as desired again.
We also tested creating a server in the display name with "my-server.my-domain.tld" and later changing the display name to "my-server.my-domain.tld (function of server 1.2)". The WebUi works, but the display name doesn't change. Only if you remove the "1.2" it will be displayed again

[oxzi]

@TheCry: The display_name column is currently limited to 255 chars. Is your descriptive display name longer than 255 characters?

icingadb/schema/mysql/schema.sql

Line 172 in 2681634

display_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

Furthermore, are there any errors being logged? Either by icingadb, icingadb-web (might be in your web server or PHP logs, depending on your setup) or MariaDB?

There is also #724, mentioning the limit of 255 chars for timeperiod_range.range_value. We should consider loosening some of those limits, as 255 chars and especially 32 chars are not enough for anyone.

[TheCry]

I have tried the whole thing on another ICINGA instance. No HA. Everything on one server. Same problem.
If I change the display name of an existing server from “Backup server BAREOS02” to “bareos02.my-domain.tld (Backup server BAREOS02)”, I see the following line in the console under “systemctl status icingadb”:
icingadb[3905777]: database: Can't execute query. Retrying

The display name in the WebUi does not change.
I have now set the logging to debug and tried again. You can find the log in the attachment. I have created it like this:
journalctl --boot --reverse --priority=emerg..debug --since=-2h --unit=“icingadb.service” -o json > icingaDB_debug.log

icingaDB_debug.log

[oxzi]

Thanks for your logs, @TheCry. The three SQL errors I found there were all "Error 1406 (22001): Data too long for co
lumn 'icon_image_alt' at row 1".

This error has nothing to do with HA, but with the database schema and mostly switching to SQL STRICT MODE in 1.2.0, resulting in showing MySQL/MariaDB errors instead of silently discarding them.

It seems like icon_image_alt was a VARCHAR(32) for Icinga DB since the initial schema, provided in 05d5e97. On the contrary, in the Icinga 2 IDO it has the type TEXT, also since its emerge in Icinga/icinga2@e8bd81b. Unfortunately, I can find no further information as to why VARCHAR(32) was chosen over TEXT.

Btw, as the Icinga Director was mentioned, it also provides a schema where icon_image_alt is defined as a VARCHAR(255). But as I don't have much experience with the Director, I cannot say how those interact.

@TheCry, you wrote you tried this on another instance. Is this instance a testing instance where you can play around without endangering your production system? If this is the case - and only then - please try this schema upgrade:

-- DON'T BLINDLY EXECUTE THIS UNLESS YOU HAVE READ THE PARAGRAPH ABOVE!
ALTER TABLE host MODIFY COLUMN icon_image_alt TEXT NOT NULL;
ALTER TABLE service MODIFY COLUMN icon_image_alt TEXT NOT NULL;

[TheCry]

@oxzi Yes i have a second system, but it is a live system, too.

[TheCry]

@oxzi I'd tried your queries on the second system (Sql Dump was created). After changing the field "icon_image_alt" to TEXT everything ist working fine now. I can use any "display_name" and "icon_image_alt". The system works fine.