"Error 1064 (42000): You have an error in your SQL syntax;..." on initialization

Icinga 2
, ,
1

Logs from icingadb:

2025-08-15T12:53:16.143Z	INFO	icingadb	Starting Icinga DB daemon (1.3.0-ga3bfecb)
2025-08-15T12:53:16.143Z	INFO	icingadb	Connecting to database at 'mysql://root@icinga_mysql:3306/icingadb'
2025-08-15T12:53:16.144Z	WARN	database	Can't connect to database. Retrying	{"error": "dial tcp 172.18.0.7:3306: connect: connection refused"}
2025-08-15T12:53:22.783Z	WARN	database	Can't connect to database. Retrying	{"error": "Error 1049 (42000): Unknown database 'icingadb'"}
2025-08-15T12:53:30.559Z	INFO	database	Reconnected to database	{"after": "14.416229509s", "attempts": 7}
2025-08-15T12:53:30.561Z	INFO	icingadb	Starting database schema auto import
2025-08-15T12:53:31.539Z	INFO	icingadb	The database schema was successfully imported
2025-08-15T12:53:31.539Z	INFO	icingadb	Connecting to Redis at 'redis://icinga_valkey:6379'
2025-08-15T12:53:34.541Z	INFO	icingadb	Waiting for Icinga 2 to write into Redis, please make sure you have started Icinga 2 and the Icinga DB feature is enabled
2025-08-15T12:53:59.318Z	INFO	icingadb	Starting history sync
2025-08-15T12:54:00.315Z	INFO	heartbeat	Received Icinga heartbeat	{"environment": "ae94fc14b510ccc25f1e02f988e1d45243380071"}
2025-08-15T12:54:00.333Z	INFO	icingadb	Taking over	{"reason": "no other instance is active"}
2025-08-15T12:54:00.333Z	INFO	icingadb	Starting config sync
2025-08-15T12:54:00.333Z	INFO	icingadb	Starting initial state sync
2025-08-15T12:54:00.333Z	INFO	icingadb	Starting overdue sync
2025-08-15T12:54:00.340Z	WARN	config-sync	Aborted initial state sync after 7.2436ms
2025-08-15T12:54:00.341Z	WARN	config-sync	Aborted config sync after 7.73886ms
2025-08-15T12:54:01.433Z	FATAL	icingadb	Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"notificationcommand" WHERE "environment_id" = ?' at line 1
can't perform "SELECT \"properties_checksum\", \"id\" FROM \"notificationcommand\" WHERE \"environment_id\" = :environment_id"
github.com/icinga/icinga-go-library/database.CantPerformQuery
	github.com/icinga/icinga-go-library@v0.6.3/database/utils.go:16
github.com/icinga/icinga-go-library/database.(*DB).YieldAll.func1
	github.com/icinga/icinga-go-library@v0.6.3/database/db.go:665
golang.org/x/sync/errgroup.(*Group).Go.func1
	golang.org/x/sync@v0.12.0/errgroup/errgroup.go:78
runtime.goexit
	runtime/asm_amd64.s:1700

I ran into this issue while trying to bootstrap an Icinga2 instance via Docker compose. I have tried a combination of icingadb 1.3 and icinga2 2.13 containers, and I also see this error on the latest versions of those two packages on Docker hub.

My code is different than the one here, but have been referring there for ideas from time to time.

  • Version used: v2.13.12
  • Operating System and version: Ubuntu 24.04.3 LTS
  • Enabled features: api checker icingadb notification
  • Icinga Web 2 version and modules: 2.12.5, icingadb
  • Config validation: passed
2

Welcome to the Icinga Community and thanks for posting.

First, please always try to use the latest versions, especially if you are building something new. Icinga 2.13 is two major versions behind. Icinga DB 1.3.0 was developed against Icinga 2.14 and there is no explicit guarantee that it will work with an older Icinga 2 release branch.

However, I am uncertain if this relates to the reported problem.

Could you please post the output of the following SQL query against the icingadb database.

select * from notificationcommand;

Furthermore, please check your MySQL error log for errors related to the failing query. Depending on your containerized setup, you have to enable them like discussed here.

In addition, to ease reproducing your setup, please share your whole Docker Compose setup with us, if this is somehow possible.

3

It seems there is already an issue --database-auto-import causes SQL syntax error on first start · Issue #1002 · Icinga/icingadb · GitHub for this problem.

4

Thanks for that. Adding restart: on-failure for the icingadb container seems to work around that issue.

1 Like
5

@apenning Here’s the requested source code, in case you’re curious:

6

Thanks for your quick reply.

In the meantime I have debugged this issue and have documented my findings in the mentioned issue #1002. Turns out, from the beginning there was a bug in the schema import logic, letting altered SQL system variables from the schema file affect future queries. I am a bit puzzled why this was not detected earlier.

I have proposed a fix in #1008.

As a workaround, restarting Icinga DB does the trick, as you have also mentioned. The subsequent Icinga DB run would skip the schema import and thereby would not run with an incorrect SQL mode.

Totally unrelated, I have taken a short look at your Open Alert Viewer and it looks cool. I am going to try it out later.

2 Likes
7

Thank you! :smile: Let me know if there’s any issue, or you’re looking for specific features in Open Alert Viewer.