'Unknown database' error while installing Bahmni on remote machine (with Bahmni installer)

mksrom · September 12, 2016, 2:10pm

Hi guys! (Bahmni Team)

I am trying to install Bahmni EMR only on a remote machine with the Bahmni installer package. I have followed all the steps detailed in the doc

I am using a custom inventory file in which I set:

/etc/bahmni-installer/my_hosts

    localhost ansible_connection=local
    ...
    [bahmni-emr]
    52.123.456.111

    [bahmni-emr-db]
    52.123.456.111
    ...

Where 52.123.456.111 is my remote CentOS machine (6.8) All the other host groups are empty.

Now when I run: bahmni -i my_hosts install

it runs for a while then returns the following Ansible error:

TASK [bahmni-emr : Backup openmrs markers, failed_events tables] ***************

task path: /opt/bahmni-installer/bahmni-playbooks/roles/bahmni-emr/tasks/main.yml:81

fatal: [52.123.456.111 → 52.123.456.111]: FAILED! => {“changed”: true, “failed”: true, “rc”: 2, “stderr”: “”, “stdout”: “+ . /etc/bahmni-installer/bahmni.conf\r\n++ IMPLEMENTATION_NAME=default\r\n++ OPENMRS_DB_USERNAME=openmrs-user\r\n++ OPENMRS_DB_PASSWORD=password\r\n++ OPENMRS_DB_SERVER=52.123.456.111\r\n++ MYSQL_ROOT_USER=root\r\n++ MYSQL_ROOT_PASSWORD=password\r\n++ date +%Y%m%d_%H%M%S\r\n+ TIME=20160912_191946\r\n+ mysqldump -uroot -ppassword openmrs markers failed_events\r\nWarning: Using a password on the command line interface can be insecure.\r\nmysqldump: Got error: 1049: Unknown database ‘openmrs’ when selecting the database\r\n”, “stdout_lines”: [“+ . /etc/bahmni-installer/bahmni.conf”, “++ IMPLEMENTATION_NAME=default”, “++ OPENMRS_DB_USERNAME=openmrs-user”, “++ OPENMRS_DB_PASSWORD=password”, “++ OPENMRS_DB_SERVER=52.123.456.111”, “++ MYSQL_ROOT_USER=root”, “++ MYSQL_ROOT_PASSWORD=password”, “++ date +%Y%m%d_%H%M%S”, “+ TIME=20160912_191946”, “+ mysqldump -uroot -ppassword openmrs markers failed_events”, “Warning: Using a password on the command line interface can be insecure.”, “mysqldump: Got error: 1049: Unknown database ‘openmrs’ when selecting the database”]}

“mysqldump: Got error: 1049: Unknown database ‘openmrs’ when selecting the database”

Indeed, the ‘openmrs’ database does not exist:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Any idea of what’s the problem ? Thanks

Romain

darius · September 12, 2016, 3:16pm

(I just moved this post to the #software:bahmni category, so the relevant people see it.)

padmavati · September 12, 2016, 4:20pm

Hi mksron,

In that step we are trying to take the backup from failed_events table which belongs to openmrs database. The purpose of this step is to take the dump if you have already installed Bahmni and upgrading/downgrading, in that case you might not want to lose the data. That’s why we are taking the dump. However for the first time installation of Bahmni there won’t be any database to take the backup, it is failing. You can ignore that error message for now.

mksrom · September 12, 2016, 4:51pm

Thanks @padmavati

The problem is that the script execution stops after the error:

The complete error is:

TASK [bahmni-emr : Backup openmrs markers, failed_events tables] ***************

task path: /opt/bahmni-installer/bahmni-playbooks/roles/bahmni-emr/tasks/main.yml:81

fatal: [52.123.456.111 -> 52.123.456.111]: FAILED! => {"changed": true, "failed": true, "rc": 2, "stderr": "", "stdout": "+ . /etc/bahmni-installer/bahmni.conf\r\n++ IMPLEMENTATION_NAME=default\r\n++ OPENMRS_DB_USERNAME=openmrs-user\r\n++ OPENMRS_DB_PASSWORD=password\r\n++ OPENMRS_DB_SERVER=52.123.456.111\r\n++ MYSQL_ROOT_USER=root\r\n++ MYSQL_ROOT_PASSWORD=password\r\n++ date +%Y%m%d_%H%M%S\r\n+ TIME=20160912_221038\r\n+ mysqldump -uroot -ppassword openmrs markers failed_events\r\nWarning: Using a password on the command line interface can be insecure.\r\nmysqldump: Got error: 1049: Unknown database 'openmrs' when selecting the database\r\n", "stdout_lines": ["+ . /etc/bahmni-installer/bahmni.conf", "++ IMPLEMENTATION_NAME=default", "++ OPENMRS_DB_USERNAME=openmrs-user", "++ OPENMRS_DB_PASSWORD=password", "++ OPENMRS_DB_SERVER=52.123.456.111", "++ MYSQL_ROOT_USER=root", "++ MYSQL_ROOT_PASSWORD=password", "++ date +%Y%m%d_%H%M%S", "+ TIME=20160912_221038", "+ mysqldump -uroot -ppassword openmrs markers failed_events", "Warning: Using a password on the command line interface can be insecure.", "mysqldump: Got error: 1049: Unknown database 'openmrs' when selecting the database"]}

NO MORE HOSTS LEFT *************************************************************
 [WARNING]: Could not create retry file 'all.retry'.         [Errno 2] No such file or directory: ''


PLAY RECAP *********************************************************************
52.123.456.111             : ok=92   changed=24   unreachable=0    failed=1   
localhost                  : ok=6    changed=1    unreachable=0    failed=1   

Traceback (most recent call last):
  File "/usr/bin/bahmni", line 9, in <module>
    load_entry_point('bahmni==0.1', 'console_scripts', 'bahmni')()
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/core.py", line 716, in __call__
    return self.main(*args, **kwargs)
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/core.py", line 889, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/usr/lib/python2.6/site-packages/click-6.6-py2.6.egg/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/lib/python2.6/site-packages/bahmni-0.1-py2.6.egg/bahmni.py", line 48, in install
    return subprocess.check_call(command, shell=True)
  File "/usr/lib64/python2.6/subprocess.py", line 505, in check_call
    raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command 'ansible-playbook -i /etc/bahmni-installer/my_hosts all.yml -vv  --extra-vars '@/etc/bahmni-installer/rpm_versions.yml' --extra-vars '@/etc/bahmni-installer/setup.yml'' returned non-zero exit status 1

hanishap · September 13, 2016, 7:13am

Hi mksron,

 We are investigating the problem. Meanwhile you can also try ssh in to that remote machine and install bahmni there, since it is a singe machine setup.Or for remote machine setup you can refer to this inventory file https://github.com/Bahmni/bahmni-playbooks/blob/master/dev/dev as an example ( In the above inventory file, ssh credentails for remote machine are not given, please make sure credentials are given).

mksrom · September 13, 2016, 8:23am

Thanks, I’ll do it locally for now.

Let me know if I can do anything in the mean time.

mksrom · September 13, 2016, 4:15pm

This works fine. Thanks.

I think I do not need to enter the ‘ansible_ssh_pass’ variable since I connect with public keys, and probably neither the ‘ansible_ssh_user’ because both machines have a ‘centos’ user. Anyway, here is what I have tried:

cd /opt/bahmni-installer/bahmni-playbooks

ansible-playbook -i /etc/bahmni-installer/my_hosts all.yml -vv  --extra-vars '@/etc/bahmni-installer/rpm_versions.yml' --extra-vars '@/etc/bahmni-installer/setup.yml'

with /etc/bahmni-installer/my_hosts contents:

localhost ansible_connection=local

[nagios-server]

[bahmni-emr] centos-vbox-6.8 centos-aws-6.7

[bahmni-emr-db] centos-vbox-6.8 centos-aws-6.7

[bahmni-emr-db-slave]

[bahmni-erp]

…

and

ansible-playbook -i dev/dev all.yml -vv  --extra-vars '@dev/rpm_versions.yml' --extra-vars '@dev/setup.yml'

with dev/dev contents:

centos-vbox-6.8 ansible_ssh_user=centos centos-aws-6.7 ansible_ssh_user=centos

[nagios-server]

[bahmni-emr] centos-vbox-6.8 centos-aws-6.7

[bahmni-emr-db] centos-vbox-6.8 centos-aws-6.7

[bahmni-emr-db-slave]

[bahmni-erp]

…

Both will fail with the error mentioned above in the thread.

As you can see in the inventory files, I have tried on 2 different machines simultaneously this time:

CentOS 6.7 (Final) hosted on AWS hostname: centos-aws-6.7
CentOS 6.8 (Final) running in VirtualBox. hostname: centos-vbox-6.8

Here is the complete output log: Ubuntu Pastebin

hanishap · September 14, 2016, 6:22am

Hi mksrom, I have checked the logs and found out it is not able to establish connection with mysql host, hence openmrs database is not created. This is the error OPENMRS_DB_USERNAME=openmrs-user\n++ OPENMRS_DB_PASSWORD=password\n++ OPENMRS_DB_SERVER=centos-vbox-6.8\n++ MYSQL_ROOT_USER=root\n++ MYSQL_ROOT_PASSWORD=password\n++ mysql -h centos-vbox-6.8 -uroot -ppassword --skip-column-names -e ‘SHOW DATABASES LIKE ‘\’‘openmrs’\’’’\nERROR 2005 (HY000): Unknown MySQL server host ‘centos-vbox6.8’.

mksrom · September 16, 2016, 2:23pm

Thanks @hanishap for pointing me to the right direction.

In order to solve this problem, I had to ensure that my target machine resolves centos-aws-6.7 domain to 127.0.0.1 by editing the /etc/hosts file.

cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

127.0.0.1 centos-aws-6.7

Then the package will install just fine and all the ansible plays will run without any error.

But the problem comes again if the inventory file points to the public IP address, and not the domain name, failing with the same error.

For instance, if my public IP address is 52.59.230.131 and my inventory file such as: cat /etc/bahmni-installer/my_hosts

localhost ansible_connection=local

[nagios-server]

[bahmni-emr] 52.59.230.131

[bahmni-emr-db] 52.59.230.131

[bahmni-emr-db-slave]

[bahmni-erp] …

then the bahmni-openmrs installation package will run a mysql command with the following arguments:

mysql -h 52.59.230.131 -uroot -ppassword --skip-column-names -e 'SHOW DATABASES LIKE '\\''openmrs'\\'''

See the code: https://github.com/Bahmni/bahmni-package/blob/master/openmrs/resources/initDB.sh#L10

Which won’t work by default in most cases, because the default MySQL port is not accessible on the machine.

The error message returned is:

mysql -h 52.59.230.131 -uroot -ppassword --skip-column-names -e ‘SHOW DATABASES LIKE ‘\’‘openmrs’\’‘’\nWarning: Using a password on the command line interface can be insecure.\nERROR 2003 (HY000): Can’t connect to MySQL server on ‘52.59.230.131’ (110)

(Note that this won’t stop the Ansible script execution. The status is changed and not failed. See screenshots below in Note 1)

The only way to make this work is to open the 3306 port so that MySQL can run its command through TCP/IP.

But I don’t think it is recommended to open 3306 port unless it is necessary. Especially in the case where the application and the database run on the same server.

Furthermore, we configure OpenMRS using Ansible on mutliple hosts and the bahmni-emr-db group hosts are especially configured to be the databases, so the command:

mysql -h 52.59.230.131 -uroot ...

is directly run on the database target machine.

I guess we could modify it to be just:

mysql -h localhost -uroot ...

What do you think?

Note 1:

The task status is changed:

[…]

and the script continues its execution without obvious notification for the user.

preethi.s · September 17, 2016, 8:43am

Hi @mksrom, What I think you are trying to do is, provisioning two different environments simultaneously with a single inventory file. This wont work because an inventory file is a configuration for a single whole environment which can contain single host or multiple hosts. For example: there should be separate inventory file for showcase env and prod env. We cant add the hosts of both showcase and prod in a single inventory file and expect the installation to provision both the environments simultaneously.

Reason: The hosts that you add in the host groups of the inventory file establishes relation with the hosts in other host group. For example: The configuration in your dev/dev inventory file means that ‘centos-vbox-6.8’ in your [bahmni-emr] group have two db-servers ‘centos-vbox-6.8’ and ‘centos-aws-6.7’ which are present in ‘bahmni-emr-db’ group. But that was not what you intended to do ryt?

preethi.s · September 17, 2016, 9:17am

It is not guaranteed that mysql -h 52.59.230.131 -uroot ... will run always in the db machine. The case in which app and db servers are different, the rpm will be installed in app server machine and while installation it will try to contact the db machine and install the db there. So it could not be modified as ‘localhost’. Did it work for you when you opened the port? If not, I think the issue might be in allowing access to mysql from particular host. Can you provide the entire log file of the installation?

mksrom · September 17, 2016, 3:36pm

OK thanks.

OK, I understand. Indeed that is not what I want. What I am trying to do is to use the Bahmni Installer on a CI server to deploy remote production instances of Bahmni.

I don’t really need for now to deploy more than 1 server at a time. I just tried 2 servers for the sake of the example, but now I understand that it is not what it is used for.

Openning the port works yes. See the complete log with the port close: Install log: MySQL port close

See the complete log with the port open: Install log: MySQL port open