Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

FATAL icingadb-migrate/misc.go:119 pq: syntax error at or near "name" #884

Open
netphantm opened this issue Jan 27, 2025 · 5 comments · May be fixed by #885
Open

FATAL icingadb-migrate/misc.go:119 pq: syntax error at or near "name" #884

netphantm opened this issue Jan 27, 2025 · 5 comments · May be fixed by #885
Assignees
@oxzi
Labels
bug Something isn't working

Comments

@netphantm
Copy link

Describe the bug

I get an error on migrating the ido to icingadb

To Reproduce

icingadb-migrate -c icingadb-migration.yml -t /tmp/tmp.aJ0B6Uz3xq
2025-01-27T08:08:22.182+0100	INFO	icingadb-migrate/main.go:82	Starting IDO to Icinga DB history migration
2025-01-27T08:08:22.182+0100	INFO	icingadb-migrate/main.go:179	Connecting to databases
2025-01-27T08:08:22.213+0100	INFO	icingadb-migrate/main.go:145	Preparing cache
2025-01-27T08:08:22.215+0100	INFO	icingadb-migrate/main.go:97	Computing progress
2025-01-27T08:08:23.176+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "ack & comment", "migrated": 0, "total": 67167}
2025-01-27T08:08:23.243+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "flapping", "migrated": 0, "total": 353462}
2025-01-27T08:08:23.246+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "downtime", "migrated": 0, "total": 297040}
2025-01-27T08:08:24.268+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "notification", "migrated": 0, "total": 3891107}
2025-01-27T08:08:25.093+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "state", "migrated": 0, "total": 6678620}
2025-01-27T08:08:25.093+0100	INFO	icingadb-migrate/main.go:107	Filling cache
flapping 100 %    0s  0/s
notification 100 %    0s  0/s
state 100 %    0s  0/s
2025-01-27T08:08:25.157+0100	INFO	icingadb-migrate/main.go:110	Actually migrating
2025-01-27T08:08:25.159+0100	FATAL	icingadb-migrate/misc.go:119	pq: syntax error at or near "name"
can't prepare query
main.sliceIdoHistory[...]
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:119
main.migrateOneType[...]
	github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:430
main.init.func2
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:252
main.migrate.func1
	github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:368
main.historyTypes.forEach.func1
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:230
golang.org/x/sync/errgroup.(*Group).Go.func1
	golang.org/x/[email protected]/errgroup/errgroup.go:78
runtime.goexit
	runtime/asm_amd64.s:1700	{"query": "SELECT ch.commenthistory_id, UNIX_TIMESTAMP(ch.entry_time) entry_time,\n  ch.entry_time_usec, ch.entry_type, ch.author_name, ch.comment_data, ch.is_persistent,\n  COALESCE(UNIX_TIMESTAMP(ch.expiration_time), 0) expiration_time,\n  COALESCE(UNIX_TIMESTAMP(ch.deletion_time), 0) deletion_time,\n  ch.deletion_time_usec,\n  COALESCE(ch.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', ch.commenthistory_id, '-', ch.object_id)) name,\n  o.objecttype_id, o.name1, COALESCE(o.name2, '') name2\nFROM icinga_commenthistory ch\nINNER JOIN icinga_objects o ON o.object_id=ch.object_id\nWHERE ch.commenthistory_id BETWEEN :fromid AND :toid\nAND ch.commenthistory_id > :checkpoint -- where we were interrupted\nORDER BY ch.commenthistory_id -- this way we know what has already been migrated from just the last row's ID\nLIMIT :bulk\n"}
main.sliceIdoHistory[...]
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:119
main.migrateOneType[...]
	github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:430
main.init.func2
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:252
main.migrate.func1
	github.com/icinga/icingadb/cmd/icingadb-migrate/main.go:368
main.historyTypes.forEach.func1
	github.com/icinga/icingadb/cmd/icingadb-migrate/misc.go:230
golang.org/x/sync/errgroup.(*Group).Go.func1
	golang.org/x/[email protected]/errgroup/errgroup.go:78

Expected behavior

migration to run smoothly

Your Environment

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

  • Icinga DB version: 1.2.1
  • Icinga 2 version: r2.14.4-1
  • Operating System and version: Debian GNU/Linux 11 (bullseye)

Additional context

icingadb-migration.yml:

icinga2:
  # Content of /var/lib/icinga2/icingadb.env
  env: "********"

# IDO database
ido:
  type: pgsql # or "pgsql" for PostgreSQL
  host: monitoring-db
  port: 5432
  database: icinga2
  user: icinga2
  password: ***
  #tls: false
  #cert: <Path to TLS client certificate>
  #key: <Path to TLS private key>
  #ca: <Path to TLS CA certificate>
  #insecure: false

  # Input time range
  from: 0
  to: 1737536974

# Icinga DB database
icingadb:
  type: pgsql # or "pgsql" for PostgreSQL
  host: monitoring-db
  port: 5432
  database: icingadb
  user: icingadb
  password: ***
  #tls: false
  #cert: <Path to TLS client certificate>
  #key: <Path to TLS private key>
  #ca: <Path to TLS CA certificate>
  #insecure: false
@oxzi oxzi self-assigned this Jan 27, 2025
@oxzi oxzi added the bug Something isn't working label Jan 27, 2025
@oxzi
Copy link
Member

oxzi commented Jan 27, 2025

Thank you for reporting this bug and please excuse the trouble.

The failing query is in the comment_query.sql file, failing on PostgreSQL due to an "syntax error at or near "name"".

Unfortunately I was unable to reproduce this error locally. Could you please provide some more information about your setup? Which version of PostgreSQL are you using?

Could you please execute the following query manually in a psql shell connected to your IDO database and report if it fails as well?

SELECT ch.commenthistory_id, UNIX_TIMESTAMP(ch.entry_time) entry_time,
  ch.entry_time_usec, ch.entry_type, ch.author_name, ch.comment_data, ch.is_persistent,
  COALESCE(UNIX_TIMESTAMP(ch.expiration_time), 0) expiration_time,
  COALESCE(UNIX_TIMESTAMP(ch.deletion_time), 0) deletion_time,
  ch.deletion_time_usec,
  COALESCE(ch.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', ch.commenthistory_id, '-', ch.object_id)) name,
  o.objecttype_id, o.name1, COALESCE(o.name2, '') name2
FROM icinga_commenthistory ch
INNER JOIN icinga_objects o ON o.object_id=ch.object_id;

@oxzi oxzi assigned oxzi and unassigned oxzi Jan 27, 2025
@oxzi
Copy link
Member

oxzi commented Jan 27, 2025

I have just given it another shot on a Debian 11, using PostgreSQL in version 13+225+deb11u1, were I was able to reproduce the error. Please give us some time to work on a fix and thanks again for reporting this.

postgres=# SELECT ch.commenthistory_id, UNIX_TIMESTAMP(ch.entry_time) entry_time,
  ch.entry_time_usec, ch.entry_type, ch.author_name, ch.comment_data, ch.is_persistent,
  COALESCE(UNIX_TIMESTAMP(ch.expiration_time), 0) expiration_time,
  COALESCE(UNIX_TIMESTAMP(ch.deletion_time), 0) deletion_time,
  ch.deletion_time_usec,
  COALESCE(ch.name, CONCAT(o.name1, '!', COALESCE(o.name2, ''), '!', ch.commenthistory_id, '-', ch.object_id)) name,
  o.objecttype_id, o.name1, COALESCE(o.name2, '') name2
FROM icinga_commenthistory ch
INNER JOIN icinga_objects o ON o.object_id=ch.object_id;
ERROR:  syntax error at or near "name"
LINE 6: ...2, ''), '!', ch.commenthistory_id, '-', ch.object_id)) name,
                                                                  ^

@oxzi
Copy link
Member

oxzi commented Jan 27, 2025

The issue at hand is the rewritten output_name to "name" in the longer COALESCE column. Because reasons, the output_name is assigned without the AS keyword, which is valid but has its limit, as documented:

To specify the name to use for an output column, write AS output_name after the column's expression. (You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.)

This keyword list contains NAME, both in version 13 as well as in the latest version 17, but stating it as "non-reserved".

I will look into adding the AS keyword, as it is supported both by MySQL and PostgreSQL. Following the quoted advice to always use AS seems like a good idea to me.

oxzi added a commit that referenced this issue Jan 27, 2025
@oxzi
icingadb-migrate: Explicit AS in SELECT
025711d 
By introducing an explicit "AS" to set output names in the SELECT column
list, there are no issues with reserved names. Unfortunately, this
happened on PostgreSQL in the older version 13 with the reserved name
"name". Adding "AS" mitigates this issue.

Furthermore, I have put each column name in its own line for the SELECT
queries to ease the readability of the query itself and of future diffs.

Closes #884.
@oxzi oxzi linked a pull request Jan 27, 2025 that will close this issue
Open
@oxzi
Copy link
Member

oxzi commented Jan 27, 2025

@netphantm: I have created #885 addressing this very issue. Please feel free to try it out, if you like to.

@netphantm
Copy link
Author

cool, it worked ;)

git/icingadb/cmd/icingadb-migrate/icingadb-migrate -c icingadb-migration.yml -t /tmp/tmp.aJ0B6Uz3xq
2025-01-27T11:00:56.044+0100	INFO	icingadb-migrate/main.go:82	Starting IDO to Icinga DB history migration
2025-01-27T11:00:56.044+0100	INFO	icingadb-migrate/main.go:179	Connecting to databases
2025-01-27T11:00:56.081+0100	INFO	icingadb-migrate/main.go:145	Preparing cache
2025-01-27T11:00:56.083+0100	INFO	icingadb-migrate/main.go:97	Computing progress
2025-01-27T11:00:56.768+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "ack & comment", "migrated": 0, "total": 67167}
2025-01-27T11:00:56.834+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "flapping", "migrated": 0, "total": 353462}
2025-01-27T11:00:56.837+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "downtime", "migrated": 0, "total": 297040}
2025-01-27T11:00:57.811+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "notification", "migrated": 0, "total": 3891107}
2025-01-27T11:00:58.802+0100	INFO	icingadb-migrate/main.go:338	Counted migrated IDO events	{"type": "state", "migrated": 0, "total": 6678620}
2025-01-27T11:00:58.802+0100	INFO	icingadb-migrate/main.go:107	Filling cache
flapping 100 %    0s  0/s
notification 100 %    0s  0/s
state 100 %    0s  0/s
2025-01-27T11:00:58.857+0100	INFO	icingadb-migrate/main.go:110	Actually migrating
ack & comment 100 %    0s 3350/s
downtime 100 %    0s 3497/s
flapping 100 %    0s 6474/s
notification 100 %    0s 1550/s
state 100 %    0s 2969/s
2025-01-27T11:37:35.240+0100	INFO	icingadb-migrate/main.go:113	Cleaning up cache

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@oxzi oxzi

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

icingadb-migrate: Explicit AS in SELECT Icinga/icingadb
2 participants
@netphantm @oxzi