There is a pending schema migration which I can't fix

Icinga Web Modules Icinga Director
1

Hi,
There is a pending schema migration which I try to resolve by running

icingacli director migration run --verbose

I get this Error
ERROR: RuntimeException in /usr/local/share/icingaweb2/modules/director/library/Director/Db/Migration.php:60 with message: Migration 167 failed (SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘director_daemon_info’ already exists) while running CREATE TABLE director_daemon_info (
instance_uuid_hex VARCHAR(32) NOT NULL, – random by daemon
schema_version SMALLINT UNSIGNED NOT NULL,
fqdn VARCHAR(255) NOT NULL,
username VARCHAR(64) NOT NULL,
pid INT UNSIGNED NOT NULL,
binary_path VARCHAR(128) NOT NULL,
binary_realpath VARCHAR(128) NOT NULL,
php_binary_path VARCHAR(128) NOT NULL,
php_binary_realpath VARCHAR(128) NOT NULL,
php_version VARCHAR(64) NOT NULL,
php_integer_size SMALLINT NOT NULL,
running_with_systemd ENUM(‘y’, ‘n’) NOT NULL,
ts_started BIGINT(20) NOT NULL,
ts_stopped BIGINT(20) DEFAULT NULL,
ts_last_modification BIGINT(20) DEFAULT NULL,
ts_last_update BIGINT(20) DEFAULT NULL,
process_info MEDIUMTEXT NOT NULL,
PRIMARY KEY (instance_uuid_hex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin

I don’t know how to resolve this

Thanks,
Shadi

2

What Director version are you using?
Is it a fresh isntallation or did you do an update?

What happens if you try applying the Schema migrations from the webinterface?

3

When I try to apply a pending schema migration through web interface I get the same error. The icinga director version is 1.7.2

4

https://github.com/Icinga/icingaweb2-module-director/issues/1981

1 Like
5

@log1c awesome , worked like a charm , Thanks :slight_smile:

6

Any idea why this wouldn’t work for me?

I see in the GUI:
Migration 0 failed (SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'director_daemon_info' already exists) while running CREATE TABLE director_daemon_info ( instance_uuid_hex VARCHAR(32) NOT NULL, -- random by daemon schema_version SMALLINT UNSIGNED NOT NULL, fqdn VARCHAR(255) NOT NULL, username VARCHAR(64) NOT NULL, pid INT UNSIGNED NOT NULL, binary_path VARCHAR(128) NOT NULL, binary_realpath VARCHAR(128) NOT NULL, php_binary_path VARCHAR(128) NOT NULL, php_binary_realpath VARCHAR(128) NOT NULL, php_version VARCHAR(64) NOT NULL, php_integer_size SMALLINT NOT NULL, running_with_systemd ENUM('y', 'n') NOT NULL, ts_started BIGINT(20) NOT NULL, ts_stopped BIGINT(20) DEFAULT NULL, ts_last_modification BIGINT(20) DEFAULT NULL, ts_last_update BIGINT(20) DEFAULT NULL, process_info MEDIUMTEXT NOT NULL, PRIMARY KEY (instance_uuid_hex) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin

> DROP TABLE director_daemon_info;

ERROR 1051 (42S02): Unknown table 'icinga.director_daemon_info'
7

Are you sure that you’re in the right DB? What’s the output of your tables?

8

OK I was in the wrong DB, now I’m in icingausers. Now DROP TABLE director_daemon_info; works. However, back in the GUI, create schema results in:

Migration 0 failed (SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'director_activity_log' already exists) while running CREATE TABLE director_activity_log ( id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL, object_type VARCHAR(64) NOT NULL, object_name VARCHAR(255) NOT NULL, action_name ENUM('create', 'delete', 'modify') NOT NULL, old_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON', new_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON', author VARCHAR(64) NOT NULL, change_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, checksum VARBINARY(20) NOT NULL, parent_checksum VARBINARY(20) DEFAULT NULL, PRIMARY KEY (id), INDEX sort_idx (change_time), INDEX search_idx (object_name), INDEX search_idx2 (object_type(32), object_name(64), change_time), INDEX search_author (author), INDEX checksum (checksum) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then I try another drop:

DROP TABLE director_activity_log;
ERROR 3730 (HY000): Cannot drop table 'director_activity_log' referenced by a foreign key constraint 'director_generated_config_activity' on table 'director_generated_config'.

So down the rabbit hole I go:

mysql> DROP TABLE director_generated_config;

ERROR 3730 (HY000): Cannot drop table 'director_generated_config' referenced by a foreign key constraint 'director_generated_config_file_config' on table 'director_generated_config_file'.

mysql> DROP TABLE director_generated_config_file;

Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE director_generated_config;

ERROR 3730 (HY000): Cannot drop table 'director_generated_config' referenced by a foreign key constraint 'config_checksum' on table 'director_deployment_log'.

mysql> DROP TABLE director_deployment_log;

Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE director_generated_config;

Query OK, 0 rows affected (0.08 sec)

No matter what I delete when I go to Create schema in the GUI I keep getting these already exists messages. How can I reset this?

9

I’m not sure if this is safe if you have configs in director

You could just drop all of the director tables and try again – or just point to a new DB.

Not sure how you ended up here; did you start a director installation a while back, abandon it, and came back to it?

10

Yes I was in the middle of doing that but then I ran into this error:

mysql> DROP TABLE director_datafield;
ERROR 3730 (HY000): Cannot drop table 'director_datafield' referenced by a foreign key constraint 'icinga_command_field_datafield' on table 'icinga_command_field'.

And that’s not a director table, at least from what it seems based on the table name not having ‘director’ in it. I then tried to uninstall following suggestions here, but there is no separate ‘director’ database just icinga and icingausers.

I started it a few days ago and Google led me here based on the error message.

11

Running out of ideas, you might just remove the constraint and then try to drop the table again.

12

I took a look at the installation instructions again. I originally tried using the exiting icinga and icingausers database. The solution was staring right at me:

Create an empty Icinga Director database

So that’s what I did and used the web-based Kickstart wizard as well as creating a Database resource via Configuration / Application / Resources. Now I’m trying to figure out what I want Director to monitor. But at least no more DB errors!

1 Like
13

There it is!

I was urged to use an existing database when I onboarded with my current employer, so I didn’t even think about that.

1 Like