Thread "Remote database connections fails (MySQL)"

Hey there,

i tried to install Ilias (v. 5.2.5) with a remote database (MySQL v. 5.6.23) but it fails with with "Database can't be reached. Please check the credentials and if database exists".

I can connect the database remote via shell.

So what is there left to check?

Best regards!

Hi

when checking if you can access your MySQL DB you need to be on the server on which ILIAS is running. Then issuing a command similar to:

> mysql -u userName -p -h addrToYourDBServer

if that works and you can connect into the DB, check if the client.ini.php in ilias/data/iliasClient has all values correctly set. It should look something like:

[db]
type = "innodb"
host = "addrToYourDBServer"
user = "userName”
pass = “********”
name = "NameOfDB”
structure_reload = "0"

Thanks for your reply!

Yes, i can connect from the ilias server to remote database via shell with

> mysql -u userName -p -h addrToYourDBServer

I checked if there is a client.ini.php in ../ilias/data/iliasClient ... but there is nothing. I does not create a file there. I then got suspicous and created a local mysql database (MariaDB v. 5.5.52) to check if it is a permission thing. But apparently its not. I can connect to my local database and it created the file you mentioned in the right folder.Everything looks fine with a local database.

But when i tried again with the remote db i get the same error: "-Database can't be reached. Please check the credentials and if database exists-"

I tried this also, but then i got stuck at the next point. So i cant finish the setup.

Just to be clear: that client.ini.php file is located in ilias/data/<iliasClient>, where <iliasClient> needs to be substituted with the actual name you gave your installation. You can also search for it from the ILIAS root direcory with:

 find . -name client.ini.php

Hey Zeist,

so if I understand you correctly you find a client.ini.php file when you create a local db but with the remote db you are unable to find the client.ini.php?

You say you get stuck at the next point. What is the next point?

If you get a client.ini.php from the local installation that's fine try to modify that in order to point to the DB you are using. Since you are able to connect to it, you have all the required information like:

[db]
type = "{{ db_type }}"
host = "localhost"
user = "root"
pass = "{{ mysql_root_password }}"
name = "{{ ilias_db_name }}"
port = ""

If you set up these correctly you should be able to connect to the DB and then continue with the installation process.

Please tell us about the next point you got stuck.

Best,

Tomasz

@Ralf and Thomas:

Sorry, if this was not clear enough. Yes, i found the client.ini.php file in ilias/data/<iliasClient> after i tested the local database setup. Then i copied this file and modified it accordingly to connect with my remote database. Just the way you mentioned it.

Now it looks like this:

1) Database Selection (green)

2) Basic Data (green)

3) Database (red)

Here Ilias tries to setup up a database if i check the box: "Create Database" and then fails silently.

And if go back one step to 2) Basic Data. The part where you put in the information with the database connection, i can see the stuff i put into client.ini.php but still get the same error:  -Database can't be reached. Please check the credentials and if database exists-

So, this is all i found out. I also tried a very easy password to avoid the password had some special characters, but it did not change anything.

Hi @All,

i have a similar issue on a windows server with xampp. Database is a mariaDB 5.5.x on a windows server.

I had a working ilias 5.1.18 git installation on this server. I tried to make an update via git checkout and updated the 5.1.18 to 5.2.5.

Login to the setup site is working fine, but as i tried to update the database from the List of Clients, i get this:

Looking at the Details, i have the same issue, with Datebase Selection and Basic Data => green / Database red.

Trying to create a new client shows the same errors as above.

Rolling back to 5.1.18, all works fine.

So it seems to me, that the installation cannot find the database by itself. The credential I´m using just works fine. I do not find any errors in logfiles so far. The Firewall either has no blocked traffic.

Hope we can find the missconfiguration somewhere...

Cheers,
Alex

@ Zeist: I guess you see the same screenshot as AlexF just posted? I think you have a typo in your client.ini.php (see below)

@ Zeist && Alex: I wasn't able to reproduce the error by installing 5.1.18 and upgrading to 5.2.5 therefore there is something wrong with the configuration of your system. I played around with the ilias and client ini files and the only time I could reproduce the upper screenshot by AlexF was:

1. I stopped mysql (sudo service mysql stop)
2. altered the host in client.ini.php
3. altered the user || password in client.ini.php

As far as I see it, it also explains the silent failing when Zeist wants to create a new DB. It's not possible because there is no DB-Service running, also there is no error message in neither apache nor ilias logs.

The only things to do I can think of are the following:

• check again the parameters (1,2,3) in client.ini.php (also with quotation marks) and use them (copy paste) for the command line tool. ILIAS also only uses these parameters to connect to the DB, so if you are able to connect inside the shell you will be able to connect through ILIAS (you need to be logged into the guest where ilias is running, not the host (e.g. windows)).
• be sure to use the correct client.ini.php (you can check it by changing the name of the DB or its user and refresh the DB screen (see attachement) you will see your changes instantly)
• take the client.ini.php file from ilias 5.1.18 (which was working) and diff it against the 5.2.5 version to see what has changed -> there should be no change whatsoever
• Just copy the old client.ini.php into the right folder, while backing the current one (e.g. backup.client.ini.php)

I can only agree with the points mentioned by @ToamszKolonko.

Something worth pointing out, there have been notable changes on the DB-Layer, due to the PHP7 refactoring (we shifted to ilPDO). See: https://www.ilias.de/docu/goto.php?target=wiki_1357_ilPDO. However, this "should" not affect your installation.

Maybe one aspect you might check is the field type in your client.ini.php file. What have you set there (e.g. "innodb" or similar).

I have just tried a new , celan, installation with the actual 5.2.5 Version. I use MariaDB 5.5.53 with MyISAM Engine.

When I enter the database credentials, i get the same error as before:

 -Database can't be reached. Please check the credentials and if database exists-

It looks like, the server cannon establish a remote connection to the databe.

I noticed during the setup, that there was not a field to enter the path to the java-installation. Perhaps, ilias needs this, if the database connection is established trough a jdbc?

Cheers,

Alex

@Tomas: i used a very easy password like "pass" to check, so i doubt its a typo problem.

@Timon: i use InnoDB

But according to this thread:

https://www.ilias.de/docu/ilias.php?ref_id=1875&cmdClass=ilobjforumgui&thr_pk=4488&cmd=viewThread&cmdNode=av:t6&baseClass=ilRepositoryGUI

Ilias 5.2.5 is ignoring a different port specification with remote db, which might explain my case too, because i dont use the standard port. And i also dont use root as dbuser.

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Put the password in the box there and you should be just fine.

Quote (Roberto Pasini (kalamun))

I recently experienced the same issue. After investigating, I found that the file Database/classes/PDO/class.ilDBPdoMySQL.php at line 33, function initSqlMode(), contained an invalid value inside the query: NO_AUTO_CREATE_USER.

I removed that value, and it worked.

[...]

(Tested on ILIAS 5.4)

I suspect you are using MySQL 8.0. This version of MySQL is not supported by ILIAS 5.4. MySQL 8.0 does no longer support the sql mode NO_AUTO_CREATE_USER and this error is therefore to be expected. Please check the required software page in the wiki for more info:
https://docu.ilias.de/goto.php?target=wiki_1357_Required_Software_ILIAS_5.4