Remote Database Setup for Icinga 2 Web

Hello everyone!

I’m very new to Icinga so first and foremost I would like to apologize if I placed this question in the wrong section or repeated a question in the community message board.

I’m running into the following error when configuring Icinga Web 2:

Unable to create user group “Administrators”. An error occured:
ERROR: Zend_Db_Statement_Exception in /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php:225 with message: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘icinga_users.icingaweb_group’ doesn’t exist, query was: SELECT COUNT(*) AS cnt FROM icingaweb_group AS g WHERE (g.name = ‘Administrators’)

I have two nodes. On one node I installed Icinga 2 and Icinga Web 2 and on another node is simply the database. During the configuration, I was able to connect to the database but I ran into the following issues:

For Authentication I selected Database and I entered the required information and clicked on Validate Configuration which states

" The configuration has been successfully validated."

BUT when I click on “Next” it tells me

" * It seems that either the database you defined earlier does not yet exist and cannot be created using the provided access credentials, the database does not have the required schema to be operated by Icinga Web 2 or the provided access credentials do not have the sufficient permissions to access the database. Please provide appropriate access credentials to solve this."

This is after using the correct username and password for the database which I granted full access to the needed database.

I would assume that it’s not a good idea to “Skip Validation” but if I did everything is fine until I get to the Monitoring Backend set up backend type being IDO. I set up the database information (After I enabled the Ido-mysql feature and imported the database from /usr/share/icinga2-ido-mysql/schema/mysql.sql)

For Monitoring IDO Resource I filled in the database information I got the following:

  • There is currently no icinga instance writing to the IDO. Make sure that a icinga instance is configured and able to write to the IDO.

Validation Log

Connection to icinga_ido as icinga on hostname:3306 successful
have_ssl: DISABLED
protocol_version: 10
version: 5.5.60-MariaDB
version_compile_os: Linux

I can skip it but I’m sure that’s not recommended and once I confirm the settings I get the error message I posted above. Also, on the node that has Icinga2 and Icinga 2 Web I did configure /etc/icinga2/features-enabled/ido-mysql.conf with the required information but not sure what else to do.

Again, sorry if this is answered somewhere else I didn’t really find it myself and thank you in advance for any and all help even if it’s just someone pointing to the proper documentation to figure this out. Thank you and have a great day/goodnight!

Welcome to our community!

Your issue is exactly that:

This inevitably results in the error message you got at the end:

Which credentials did you pass when being asked the second time? These must permit to create tables, otherwise the schema import fails.

And since you’re using a remote database, remember that users may get mapped to their origin host. ('icinga'@'localhost' is not the same as 'icinga'@'10.0.10.25')

2 Likes

Hello,

Thank you so much for your update! I think I may have a better understanding of this issue here.

So, when I clicked on Validate Configuration in the " Database Resource" section and it said " * The configuration has been successfully validated." I thought that meant that it was able to connect and do what it needed to do. I also thought that because the username and password I provided for the database again was given full access to the database: GRANT ALL PRIVILEGES ON icinga.* TO icinga@‘10.0.10.25’ IDENTIFIED BY ‘password’;

So the first time (Under the Database Resource) I put User: icinga Password: password

After, it’s the Database Setup section. Does that require a different username and password? Should I use the root account for that section? If so, I would have to enable it for remote access. Sorry for the misunderstanding as I’m a little confused about this section. As always, thank you very very much for your help!

Hi All , I have a similar issues while setting up the Monitoring IDO resources. I don’t know what do do…Please please help me

    • There is currently no icinga instance writing to the IDO. Make sure that a icinga instance is configured and able to write to the IDO.

Validation Log
Connection to icinga as icinga on localhost:3306 successful
have_ssl: DISABLED
protocol_version: 10
version: 5.5.60-MariaDB
version_compile_os: Linux

AND
[linuxtechi@icinga2 ~]$ sudo vi /etc/icinga2/features-available/ido-mysql.conf
/**

  • The db_ido_mysql library implements IDO functionality
  • for MySQL.
    */
    library “db_ido_mysql”
    object IdoMysqlConnection “ido-mysql” {
    user = “icinga”
    password = “icinga”
    host = “localhost”
    database = “icinga”
    }

[linuxtechi@icinga2 ~]$ mysql -u root -p MariaDB [(none)]> CREATE DATABASE icinga; MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE VIEW, INDEX, EXECUTE ON icinga.* TO ‘icinga’@‘localhost’ IDENTIFIED BY ‘icinga’; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> EXIT;

The root account may be one choice. Any other account with the appropriate access credentials :wink: should also suffice.

The web wizard is designed with the intent that the user utilizing it does not need to do much. This includes the database setup. The only thing required for this is a operable connection and an administrative account. (The GRANT you’ve performed yourself is already more than required)

Though, anytime the web wizard encounters something that is neither a non-existent or fully complete setup it can’t cope with it well.

1 Like

Are we talking about the web-based setup?
Normally you first define the icingaweb2 database. If that does not exist that setup asks for a privileged user to create that database (mostly root). The setup then creates the database.
After some questions about logging and other stuff the setup asks for the ifnormation to the IDO database. That is were icinga2 stores the history, objects and so on. This normally gets created during the icinga2 installation (at least on ubuntu), if I remember correctly.

If you get that message this means, if I am not mistaken, that icinga2 is not running and/or the DB has not been created.

Hi
icinga2 is running well & thats the reason we came up to this stage And previously posted DP config also , i can see DB are correct…something else issuing the problems I hope

have you edited the /etc/icinga2/features-enabled/ido-mysql.conf and put the DB info and credentials there?

Hello,

I figured out this part I think. On my end, the issue was the permissions on the file. I had to change the ownership and permissions. This is what I did:

  1. chown icinga:icinga /etc/icinga2/features-available/ido-mysql.conf
  2. chmod 0660 /etc/icinga2/features-available/ido-mysql.conf
  3. systemctl restart icinga2

Once icinga restart I tried to validate this step again and it worked. I also verified I had the correct username and password for the user with the needed grant permissions. This is what I did to create the user with the needed permissions:

  1. CREATE USER icinga@10.1.1.76 IDENTIFIED BY ‘password123’;
  2. GRANT ALL PRIVILEGES ON . TO ‘icinga’@‘10.1.1.76’ IDENTIFIED BY ‘password123’ WITH GRANT OPTION;
  3. FLUSH PRIVILEGES;

After the above, I put that information where I needed and it worked and created whatever databases were needed and I was able to proceed. The only database I needed to create manually was the icinga_ido database. Everything else was done during the installation.

Creating new database “icinga”…
Creating database schema…
Login “icinga” already exists…
Required privileges were already granted to login “icinga”.
The database has been fully set up!

General configuration has been successfully written to: /etc/icingaweb2/config.ini

Authentication configuration has been successfully written to: /etc/icingaweb2/authentication.ini
Account “admin” has been successfully created.
Account “admin” has been successfully defined as initial administrator.

User Group Backend configuration has been successfully written to: /etc/icingaweb2/groups.ini
User Group “Administrators” has been successfully created.
Account “admin” has been successfully added as member to user group “Administrators”.

Resource configuration has been successfully written to: /etc/icingaweb2/resources.ini

Monitoring backend configuration has been successfully written to: /etc/icingaweb2/modules/monitoring/backends.ini

Hope that helps! This also helped me get by my original question. Thank you guys for all the help in pointing me to this direction!!!

1 Like