Skip to content
This repository has been archived by the owner on Jan 15, 2019. It is now read-only.

[dev.icinga.com #4953] PostgreSQL Schema Update from 1.9 to 1.10 throws an error #1371

Closed
icinga-migration opened this issue Oct 25, 2013 · 7 comments
Closed

[dev.icinga.com #4953] PostgreSQL Schema Update from 1.9 to 1.10 throws an error #1371

icinga-migration opened this issue Oct 25, 2013 · 7 comments
Labels
bug High IDOUtils
Milestone
1.10.1

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/4953

Created by dmikulski on 2013-10-25 12:55:33 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2013-10-28 22:37:13 +00:00)
Target Version: 1.10.1
Last Update: 2014-12-08 14:36:03 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

When updating the database schema for icinga from 1.9.1 to 1.10 there were errors thrown:

-bash-4.1$ psql -U postgres icinga < pgsql-upgrade-1.10.0.sql
CREATE FUNCTION
ALTER TABLE
ALTER TABLE
ERROR:  syntax error at or near "timedevents_i_id_idx"
LINE 1: ALTER TABLE icinga_timedevents DROP INDEX timedevents_i_id_i...
                                                  ^
ERROR:  syntax error at or near "timedevents_time_id_idx"
LINE 1: ALTER TABLE icinga_timedevents DROP INDEX timedevents_time_i...
                                                  ^
ERROR:  syntax error at or near "timed_e_event_type_idx"
LINE 1: ALTER TABLE icinga_timedevents DROP INDEX timed_e_event_type...
                                                  ^
ERROR:  syntax error at or near "timed_e_object_id_idx"
LINE 1: ALTER TABLE icinga_timedevents DROP INDEX timed_e_object_id_...
                                                  ^
ERROR:  syntax error at or near "timed_e_rec_ev_idx"
LINE 1: ALTER TABLE icinga_timedevents DROP INDEX timed_e_rec_ev_idx...
                                                  ^
ERROR:  syntax error at or near "timedeventq_i_id_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timedeventq_i_...
                                                      ^
ERROR:  syntax error at or near "timedeventq_time_id_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timedeventq_ti...
                                                      ^
ERROR:  syntax error at or near "timedeventqueue_i_id_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timedeventqueu...
                                                      ^
ERROR:  syntax error at or near "timed_e_q_event_type_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timed_e_q_even...
                                                      ^
ERROR:  syntax error at or near "timed_e_q_sched_time_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timed_e_q_sche...
                                                      ^
ERROR:  syntax error at or near "timed_e_q_object_id_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timed_e_q_obje...
                                                      ^
ERROR:  syntax error at or near "timed_e_q_rec_ev_id_idx"
LINE 1: ALTER TABLE icinga_timedeventqueue DROP INDEX timed_e_q_rec_...
                                                      ^
DROP TABLE
DROP TABLE
ALTER TABLE
ALTER TABLE
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "uq_comments" for table "ic
ALTER TABLE
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "uq_commenthistory" for tab
ALTER TABLE
ERROR:  column "check_source" of relation "icinga_hoststatus" already exists
ERROR:  column "check_source" of relation "icinga_servicestatus" already exists
ERROR:  syntax error at or near "unsigned"
LINE 1: ...LTER TABLE icinga_logentries ADD object_id bigint unsigned d...
                                                             ^
 updatedbversion
-----------------

(1 row)

I've already get the information that timedevents is obsolete.
I've already created the check_source columns for testing with icinga-web 1.10 so it's okay.

As there is no unsigned in postgresql the error is thrown. I don't know how critical it is for the core to work.

Changesets

2013-10-25 16:05:47 +00:00 by (unknown) 6fbac66

fix brainfsck with pgsql upgrade script.

refs #4953

2013-10-28 22:35:36 +00:00 by (unknown) 32831fd

idoutils: fix logentry_type as int, not unsigned long

refs #4953

2013-10-29 22:03:51 +00:00 by (unknown) dbd643c

correct pgsql index drop (thx formorer)

refs #4953
@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-25 16:01:51 +00:00

  • Status changed from New to Feedback

it's an additional value which isn't used currently, but the error is ugly. i shouldn't have typed in the upgrade sql instead of copy-pasting while only testing the main sql. but still it unveils that no-one (including myself) tests beta releases anyways, so we might just skip the extra work for that in the future.

a proposed working query is like

diff --git a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.10.0.sql b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.10.0.sql
index fd273a5..6678a25 100644
--- a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.10.0.sql
+++ b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.10.0.sql
@@ -67,7 +67,7 @@ ALTER TABLE icinga_servicestatus ADD check_source TEXT default '';
 -- #4754 add logentries object_id
 -- -----------------------------------------

-ALTER TABLE icinga_logentries ADD object_id bigint unsigned default NULL;
+ALTER TABLE icinga_logentries ADD object_id bigint default NULL;

 -- -----------------------------------------
 -- update dbversion

@icinga-migration
Copy link
Author

Updated by dmikulski on 2013-10-28 08:10:07 +00:00

Hello Michi,

thx for you response. Have a look at those entries in messagelog, please:

Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'INSERT INTO icinga_logentries (instance_id, logentry_time, entry_time, entry_time_usec, logentry_type, logentry_data, realtime_data, inferred_data_extracted) VALUES (1, FROM_UNIXTIME(1382947065), FROM_UNIXTIME(1382947065), '0', 18446744069414584620, E'Event broker module \'IDOMOD\' version \'1.10.0\' from \'/usr/lib64/icinga/idomod.so\' initialized successfully.', '1', '1')' - 'FEHLER:  integer ist auÃerhalb des gültigen Bereichs#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'INSERT INTO icinga_processevents (instance_id, event_type, event_time, event_time_usec, process_id, program_name, program_version, program_date) VALUES (1, 104, FROM_UNIXTIME(1382947065), 941974, 18351, E'Icinga', E'1.10.0', E'10-24-2013')' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_scheduleddowntime WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_programstatus WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_contactstatus WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_comments WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_runtimevariables WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_customvariablestatus WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_hosts WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_host_parenthosts WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_host_contactgroups WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_host_contacts WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'
Oct 28 08:57:45 mat-icinga-core ido2db: Error: database query failed for 'DELETE FROM icinga_hostgroups WHERE instance_id=1' - 'FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert#012'

The first message regards the table icinga_logentries. It looks like the value order is in the wrong way.

Shouldn't it look like this?

INSERT INTO icinga_logentries (instance_id, logentry_time, entry_time, entry_time_usec, logentry_type, logentry_data, realtime_data, inferred_data_extracted) VALUES (1, FROM_UNIXTIME(1382947065), FROM_UNIXTIME(1382947065), 18446744069414584620, '0', E'Event broker module \'IDOMOD\' version \'1.10.0\' from \'/usr/lib64/icinga/idomod.so\' initialized successfully.', '1', '1')

I don't know where those other errors come from and if they are follwing errors of the query above.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-28 10:07:08 +00:00

  • Priority changed from Normal to High

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-28 19:41:47 +00:00

  • Description updated

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-28 20:01:31 +00:00

18446744069414584620 seems to be the wrong int as entry_type but sourcing from a different problem.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-10-28 22:37:13 +00:00

  • Status changed from Feedback to Resolved
  • Done % changed from 0 to 100

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-12-08 14:36:03 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 24 to IDOUtils
  • Icinga Version changed from 1 to 1
  • OS Version set to any

@icinga-migration icinga-migration added this to the 1.10.1 milestone Jan 17, 2017
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
bug High IDOUtils
Projects
None yet
Milestone
1.10.1
Development

No branches or pull requests
1 participant
@icinga-migration