Database errors - schema migration

Icinga Web Modules Icinga Director
1

Hello everyone.
since a little time i find more and more database-issues.
I’m unable to apply the schema migration because of this:

Migration 187 failed (SQLSTATE[42601]: Syntax error: 7 FEHLER: Syntaxfehler bei »INSERT« LINE 3: INSERT INTO director_schema_migration ^) while running ALTER TABLE import_row_modifier ADD COLUMN filter_expression text DEFAULT NULL, INSERT INTO director_schema_migration (schema_version, migration_time) VALUES (187, NOW())

But my current main issue is, that i’m unable to open “Service Sets” in Director because of this:

SQLSTATE[42803]: Grouping error: 7 FEHLER:  Spalte »o.object_name« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden
LINE 1: ...os.object_type, os.assign_filter, os.description, o.object_n...
                                                             ^, query was: SELECT COUNT(*) AS cnt FROM (SELECT os.id, os.uuid, (NULL) AS branch_uuid, os.object_name, os.object_type, os.assign_filter, os.description, o.object_name AS service_object_name, COUNT(DISTINCT o.uuid) AS count_services FROM icinga_service_set AS os
 LEFT JOIN icinga_service AS o ON o.service_set_id = os.id WHERE (os.object_type = 'template') GROUP BY os.uuid,
	os.uuid,
	os.id,
	os.object_name,
	os.object_type,
	os.assign_filter,
	os.description ORDER BY os.object_name ASC) AS t (Pdo.php:225)

i installed the master becaus of a good reason i didn’t remind, and i’m now no more able to return to a release, because it’s “older than the installed version”.

I would very thankful when someone could help me out there.

Thank you,
viper

  • Director version (System - About): master
  • Icinga Web 2 version and modules (System - About): 2.11.4
  • Icinga 2 version (icinga2 --version): r2.14.0-1
  • Operating System and version: Debian 11
  • Webserver, PHP versions: Apache, 7.4.33
  • Database: Postgres 11
2

Yesterday I accidentally updated my director to master and applied the migrations. Stupid me but maybe today is your lucky day :slight_smile:

I looked into schema migrations and set up a reverse migration script to the last stable release from today master:

ALTER TABLE import_row_modifier
  DROP COLUMN filter_expression;
DELETE FROM director_schema_migration
  where schema_version = 187;

DROP INDEX uuid ON director_datafield;
ALTER TABLE director_datafield 
  DROP COLUMN uuid;
DROP INDEX uuid ON director_datalist;
ALTER TABLE director_datalist
  DROP COLUMN uuid;
DELETE FROM director_schema_migration
  where schema_version = 186;

ALTER TABLE branched_icinga_notification
  DROP COLUMN users_var;
ALTER TABLE branched_icinga_notification
  DROP COLUMN user_groups_var;
DELETE FROM director_schema_migration
  where schema_version = 184;

ALTER TABLE icinga_notification
  DROP COLUMN users_var;
ALTER TABLE icinga_notification
  DROP COLUMN user_groups_var;
DELETE FROM director_schema_migration
  where schema_version = 183;

At the end your are on schema migration 182 which is the latest stable so you can install 1.10.2

I would recommend to do it step by step, line by line, you might not need the the first block since this migration failed anyway.

THERE IS NO GUARANTEE THAT THIS WORK ON YOUR MACHINE
CHECK EACH STEP BY YOURSELF VERIFYING https://github.com/Icinga/icingaweb2-module-director/tree/master/schema/mysql-migrations 183-186
MAKE A BACKUP FIRST

and for postgres, I think you can leave out the

DROP INDEX uuid…

1 Like
3

Thank you very much!