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

icingadb-migrate fails with '"name": converting NULL to string is unsupported' #766

Closed
sbraz opened this issue Jul 6, 2024 · 4 comments · Fixed by #767
Closed

icingadb-migrate fails with '"name": converting NULL to string is unsupported' #766

sbraz opened this issue Jul 6, 2024 · 4 comments · Fixed by #767
Assignees
@oxzi
Labels
bug Something isn't working crash
Milestone
1.2.1

Comments

@sbraz
Copy link

sbraz commented Jul 6, 2024

Describe the bug

Hello,
I'm attempting to migrate a 2015 Icinga installation and I get this error:

2024-07-06T04:01:52.802+0200    INFO    icingadb-migrate/main.go:105    Actually migrating
2024-07-06T04:01:52.810+0200    FATAL   icingadb-migrate/misc.go:125    sql: Scan error on column index 10, name "name": converting NULL to string is unsupported
can't perform query
main.sliceIdoHistory[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:125
main.migrateOneType[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421
main.init.func2
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:253
main.migrate.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359
main.historyTypes.forEach.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231
golang.org/x/sync/errgroup.(*Group).Go.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78
runtime.goexit
        /usr/lib/go/src/runtime/asm_arm64.s:1222        {"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, ch.name, 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[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:125
main.migrateOneType[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421
main.init.func2
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:253
main.migrate.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359
main.historyTypes.forEach.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231
golang.org/x/sync/errgroup.(*Group).Go.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78

To Reproduce

I can provide a SQL dump privately if it helps.

Expected behavior

This should not fail.

Your Environment

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

  • Icinga DB version: 1.2.0
  • Icinga 2 version: 2.14.2
  • Operating System and version: Gentoo ~arm64

Additional context

I noticed that some of my comments have a NULL name and this seems to have stopped occurring between August and December 2015. I probably upgraded something during this time frame.

icinga2=# SELECT comment_time FROM icinga_commenthistory WHERE name IS NULL ORDER BY comment_time DESC LIMIT 1;
    comment_time     
---------------------
 2015-08-18 16:29:18
(1 row)

icinga2=# SELECT comment_time FROM icinga_commenthistory WHERE name IS NOT NULL ORDER BY comment_time ASC LIMIT 1;
    comment_time     
---------------------
 2015-12-18 00:39:05
(1 row)
@oxzi oxzi added the bug Something isn't working label Jul 8, 2024
@oxzi
Copy link
Member

oxzi commented Jul 8, 2024

Thanks a lot for your detailed report and please excuse the inconvenience.

Based on your output, it seems the commentMigrationQuery from comment_query.sql has failed due to a NULL value in icinga_commenthistory.name, as you have already posted in your report.

Would it be possible for you to mitigate this issue by updating all those NULL values to an empty string and reporting back to us if the query worked afterwards? Please create a database backup before updating the columns, just in case!

UPDATE icinga_commenthistory SET name = '' WHERE name IS NULL;

Otherwise, we would gladly take the offer of testing this based on your SQL dump.

@sbraz
Copy link
Author

sbraz commented Jul 8, 2024 •
edited
Loading

Thanks for the reply @oxzi :)

I ran into another table with NULL values:

2024-07-08T22:56:53.716+0200    FATAL   icingadb-migrate/misc.go:125    sql: Scan error on column index 15, name "name": converting NULL to string is unsupported
can't perform query
main.sliceIdoHistory[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:125
main.migrateOneType[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421
main.init.func3
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:265
main.migrate.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359
main.historyTypes.forEach.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231
golang.org/x/sync/errgroup.(*Group).Go.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78
runtime.goexit
        /usr/lib/go/src/runtime/asm_arm64.s:1222        {"query": "SELECT dh.downtimehistory_id, UNIX_TIMESTAMP(dh.entry_time) entry_time, dh.author_name, dh.comment_data,\n  dh.is_fixed, dh.duration, UNIX_TIMESTAMP(dh.scheduled_start_time) scheduled_start_time,\n  COALESCE(UNIX_TIMESTAMP(dh.scheduled_end_time), 0) scheduled_end_time, dh.was_started,\n  COALESCE(UNIX_TIMESTAMP(dh.actual_start_time), 0) actual_start_time, dh.actual_start_time_usec,\n  COALESCE(UNIX_TIMESTAMP(dh.actual_end_time), 0) actual_end_time, dh.actual_end_time_usec, dh.was_cancelled,\n  COALESCE(UNIX_TIMESTAMP(dh.trigger_time), 0) trigger_time, dh.name, o.objecttype_id,\n  o.name1, COALESCE(o.name2, '') name2, COALESCE(sd.name, '') triggered_by\nFROM icinga_downtimehistory dh\nINNER JOIN icinga_objects o ON o.object_id=dh.object_id\nLEFT JOIN icinga_scheduleddowntime sd ON sd.scheduleddowntime_id=dh.triggered_by_id\nWHERE dh.downtimehistory_id BETWEEN :fromid AND :toid\nAND dh.downtimehistory_id > :checkpoint -- where we were interrupted\nORDER BY dh.downtimehistory_id -- this way we know what has already been migrated from just the last row's ID\nLIMIT :bulk\n"}
main.sliceIdoHistory[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:125
main.migrateOneType[...]
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421
main.init.func3
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:265
main.migrate.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359
main.historyTypes.forEach.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231
golang.org/x/sync/errgroup.(*Group).Go.func1
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78

I fixed them with update icinga_downtimehistory set name='' where name is null; but then I got this different error:

2024-07-08T23:26:41.312+0200    INFO    icingadb-migrate/main.go:105    Actually migrating                                                                                                                                                                                                                                  
ack & comment 100 %    0s  0/s                                                                                                                                                                                                                                                                                              
downtime   0 % [----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------]   0s  0/s                                                                                                         flapping 100 %    0s  0/s                                                                                                                                                                                                                                                      
notification 100 %    0s 6077/s                                                                                                                                                                                                                                                
state 100 %    0s 13378/s                                                                                                                                                                                                                                                      
2024-07-08T23:31:41.566+0200    FATAL   icingadb-migrate/main.go:435    pq: null value in column "downtime_id" of relation "downtime_history" violates not-null constraint                                                                                                     
can't perform "INSERT INTO \"downtime_history\" (\"trigger_time\", \"triggered_by_id\", \"service_id\", \"has_been_cancelled\", \"entry_time\", \"is_flexible\", \"environment_id\", \"flexible_duration\", \"start_time\", \"parent_id\", \"scheduled_end_time\", \"scheduled_start_time\", \"object_type\", \"host_id\", \
"cancelled_by\", \"comment\", \"scheduled_by\", \"downtime_id\", \"cancel_time\", \"author\", \"end_time\", \"endpoint_id\") VALUES (:trigger_time, :triggered_by_id, :service_id, :has_been_cancelled, :entry_time, :is_flexible, :environment_id, :flexible_duration, :start_time, :parent_id, :scheduled_end_time, :sched
uled_start_time, :object_type, :host_id, :cancelled_by, :comment, :scheduled_by, :downtime_id, :cancel_time, :author, :end_time, :endpoint_id) ON CONFLICT ON CONSTRAINT pk_downtime_history DO NOTHING"                                                                        
github.com/icinga/icingadb/internal.CantPerformQuery                                                                                                                                                                                                                           
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/internal/internal.go:30                                                                                                                                                                                   
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2.1                                                                                                                                                                              
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/pkg/icingadb/db.go:412                                                                                                                                                                                    
github.com/icinga/icingadb/pkg/retry.WithBackoff                                                                                                                                                                                                                               
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/pkg/retry/retry.go:60                                                                                                                                                                                     
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2                                                                                                                                                                                
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/pkg/icingadb/db.go:407                                                                                                                                                                                    
golang.org/x/sync/errgroup.(*Group).Go.func1                                                                                                                                                                                                                                   
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78                                                                                                                                                                  
runtime.goexit                                                                                                                                                                                                                                                                 
        /usr/lib/go/src/runtime/asm_arm64.s:1222                                                         
retry deadline exceeded                                                                                                                                                                                            
github.com/icinga/icingadb/pkg/retry.WithBackoff
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/pkg/retry/retry.go:95               
github.com/icinga/icingadb/pkg/icingadb.(*DB).NamedBulkExec.func1.(*DB).NamedBulkExec.func1.1.2
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/pkg/icingadb/db.go:407              
golang.org/x/sync/errgroup.(*Group).Go.func1                                                                                                                                                                       
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78                                                                                                                                                                                                               
runtime.goexit                                                                                                                                                                                                                                                                 
        /usr/lib/go/src/runtime/asm_arm64.s:1222                                                         
can't perform DML                                                                                                                                                                                                  
main.migrateOneType[...].func3                                                                                                                                                                                     
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:435                                                                                                              
main.sliceIdoHistory[...]                                                                                                                                                                                          
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:134                                                                                                              
main.migrateOneType[...]                                                                                                                                                                                           
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421    
main.init.func3                                                                                                                                                                                                    
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:265    
main.migrate.func1                                                                                       
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359    
main.historyTypes.forEach.func1                                                                                                                                                                                    
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231                                                                                                              
golang.org/x/sync/errgroup.(*Group).Go.func1                                                                                                                                                                       
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78                                                                                                      
runtime.goexit                                                                                                                                                                                                     
        /usr/lib/go/src/runtime/asm_arm64.s:1222        {"backend": "Icinga DB", "op": "INSERT IGNORE", "table": "downtime_history"}                                                                                                                                                                                        
main.migrateOneType[...].func3                                                                                                                                                                                                                                                 
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:435                                                         
main.sliceIdoHistory[...]                                                                                
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:134    
main.migrateOneType[...]                                                                                 
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:421                                                                                                              
main.init.func3                                                                                                                                                                                                    
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:265                                                         
main.migrate.func1                                                                                                                     
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/main.go:359                                                         
main.historyTypes.forEach.func1                                                                                                        
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/icingadb-1.2.0/cmd/icingadb-migrate/misc.go:231    
golang.org/x/sync/errgroup.(*Group).Go.func1                                                                                           
        /var/tmp/portage/net-misc/icingadb-1.2.0/work/go-mod/golang.org/x/[email protected]/errgroup/errgroup.go:78

I emailed you the dump.

@oxzi oxzi self-assigned this Jul 9, 2024
oxzi added a commit that referenced this issue Jul 9, 2024
@oxzi
icingadb-migrate: Mitigate NULL names in {comment,downtime}_query
59685cd 
Historical data from an older Icinga 2 installation contained NULL
values for the name column in some rows of the icinga_commenthistory and
icinga_downtimehistory tables.

Normally this field contains something like
${name1}!${name2}!${unique_value} where the $unique_value is based on a
timestamp for older entries and a UUID for newer ones. For a concrete
example, this could be "host.example.com!ping6!123…".

Unfortunately, using an empty string for these NULL values will cause an
error later because the new primary key will be calculated based on it.
Therefore, a new deterministic name is generated based on the primary
keys and the known name1 and name2 values.

Closes #766.
@oxzi oxzi added the crash label Jul 9, 2024
@oxzi oxzi mentioned this issue Jul 9, 2024
Merged
@oxzi
Copy link
Member

oxzi commented Jul 9, 2024

Thanks a lot for sending your dump, which helped a lot working on this.

I have just created #767 and would kindly ask you to test this updated version. This change allowed me to import your dump into Icinga DB, but without your Icinga 2 setup, some things were hard to verify.

To build this PR, checkout the PR branch and execute go build ./cmd/icingadb-migrate to compile it.

Please note that you have to undo your UPDATE which I incorrectly suggested before:

UPDATE icinga_commenthistory SET name = NULL WHERE name = '';

Please report back if this worked for you.

@sbraz
Copy link
Author

sbraz commented Jul 9, 2024

Thanks a lot! The migration worked and I can see events from 2015-03 in the Icinga DB module for Icingaweb2.

@oxzi oxzi added this to the 1.2.1 milestone Jul 30, 2024
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 crash
Projects
None yet
Milestone
1.2.1
Development

Successfully merging a pull request may close this issue.

icingadb-migrate: Mitigate NULL names in {comment,downtime}_query Icinga/icingadb
2 participants
@sbraz @oxzi