How to start with a fresh / clean database for Bahmni?

I have setup Bahmni Vagrant box using the instructions mentioned here: https://bahmni.atlassian.net/wiki/display/BAH/Bahmni+Virtual+Box

But it comes pre-setup with Patient data (I guess for demo purposes). I would like to have a fresh database for my hospital. How do I go about creating a Bahmni instance with FRESH / CLEAN Databases. I would like to keep the existing concepts, but just will need a system which does not have any Patient / Observation data.

Is there a fresh db somewhere I could download / restore? Or some scripts that can remove Patient data?

Hi,

There is a script to delete all transactional data. Please check https://github.com/Bahmni/bahmni-environment/blob/master/scripts/deletePatientData/deletePatientData.rb

Thanks

Hi, If you are planning to install bahmni from scratch on a CentOS machine, you can follow the instructions mentioned here. Basically, the script provided in this wiki installs some prerequisite software like jre, mysql, pgsql (if you are using lab and erp), restores some standard databases (same as what you see in your vagrant box) and then installs bahmni using RPM. The RPM based installation checks for a database with name openmrs during installation. If the database is unavailable (meaning a scratch deployment), it would create an openmrs database with all the migrations in place. You can use this if you want to. Please note that you will have to setup certain metadata like Visit Types, locations etc. Otherwise you will see some errors on the GUI.

@vinkesh i tried the ruby script you suggested but got this error

ruby deletepatientdata.rb /usr/local/rvm/rubies/ruby-2.0.0-p643/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:54:in require': cannot load such file -- mysql (LoadError) from /usr/local/rvm/rubies/ruby-2.0.0-p643/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:54:inrequire’ from deletepatientdata.rb:2:in `’

Please have a look

You will have to install ‘mysql’ & ‘pg’ gems.

Is there a plan in the coming future to provide an easier way to create a “fresh” DB for Bahmni, maybe using the “bahmni” command line program? Basically, it would be nice to have some way to say – please clean the DB (OpenMRS, ELIS, ERP) from all transactional information like Patients, Observations, Encounters, Program Data, Lab Tests, Etc… and leave only the “Master Data” intact.

Well to be honest, the scripts that @vinkesh mentioned are what we go to. I will have to dig a bit to find if we can clean all transactional information that you mention. I understand that this is ‘good to have’, and if we do get enough requests, we would definitely plan to write something in one of the coming releases.

+1 from my side about a reliable way to reset the databases for all components (OpenMRS, OpenELIS, …).

I think someone could see this even more than just ‘good to have’. Especially if you start to explore Bahmni and ‘mess around’. Or for training purposes. Or for ‘live trials’.

1 Like

The more I think about this, the more strongly I feel we need to provide a path for easy database setup of Bahmni. For instance, people install Bahmni with a demo setup. After that, they realize that they want to now start afresh, on the same machine where they have been playing with. What are their options?

  1. Install all of Bahmni again (wipe everything from machine). In this case, they will first need to pre-create the DBs as suggested by @bharatak. But is this true for ELIS and ERP also? Should people just create fresh DBs (with no tables), in MySQL and PostGRES… and then will the fresh install create all the necessary tables without demo patients? They will still need master data like Lab Tests, Panels, Concepts, and minimum setup of ERP – with Stocks. Is this done via CSV uploads?

  2. Lets say they installed Bahmni, with demo data… and liked the default tests and concepts… but wanted to make some edits. So, they use OpenMRS Admin UI and make modifications/additions to concepts… and also to some Lab data. Now, the system looks good… but they want to either

(a) Delete transactional data… like patients, visits, observations, orders, tests from ELIS, prescriptions from ERP… or (b) Export the master data – concepts, tests, providers, etc… and then import it back into a fresh install.

I think option 2(a) would be awesome… but difficult to build / support over time.

I think providing option 2(b) would be really good. Does Bahmni provide something like this? Or is there a way OpenMRS, ELIS, Odoo data can be exported?

How do current Bahmni implementations handle their data setup? Do they keep a clean “master” laptop on the side, where they do double entry with a demo system… and once demo system and “master” laptop are good… they export the master DB, and restore the master DB onto the live system – first time? Or, do they use liquibase scripts, which are updated with master data, as someone updates a “demo” system.

Option 2(a) is feasible. The scripts to do them are maintained here - https://github.com/Bahmni/bahmni-environment/tree/master/scripts/deletePatientData Look for *.sql files here.

Hi all,

I wanted to follow-up on this thread given similar recent experiences. I hope the Bahmni team can confirm expectations and the process they would recommend…

Reading the thread above, I agree there there are circumstances where an implementer might enter test or demo data into their system and then later want to delete it before real use. So I appreciate that you provide these scripts. But am I understanding right that Bahmni always comes pre-loaded with demo patient data that needs to be removed? This seems odd and not what we would want.

I came across this by looking over the installation process as described in this file, which sets up the necessary mysql database(s) by downloading and sourcing this file:

https://github.com/Bahmni/emr-functional-tests/blob/master/dbdump/mysql_backup.sql.gz

If I fire up a vanilla vagrant box, install mysql, and then source this file, I see the following:

  1. It creates 4 databases (bahmnireportsdata, jasperserver, openmrs, test)
  • Not sure test is supposed to be there?
  1. Within the openmrs database, there is pre-existing patient data:

mysql> use openmrs; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select count() from patient; ±---------+ | count() | ±---------+ | 43 | ±---------+ 1 row in set (0.00 sec)

mysql> select count() from obs; ±---------+ | count() | ±---------+ | 2031 | ±---------+ 1 row in set (0.01 sec)

mysql> select count() from encounter; ±---------+ | count() | ±---------+ | 201 | ±---------+ 1 row in set (0.00 sec)

  1. It comes with a bunch of pre-installed users (some of these look like they are required, others not so much):

mysql> select username from users; ±---------------+ | username | ±---------------+ | | | daemon | | Lab Manager | | superman | | Lab System | | Billing System | | automation | | yogesh | | suhas | | varsha | | suresh | ±---------------+ 11 rows in set (0.00 sec)

For a default installation of Bahmni, I would really like to see an empty database with the absolute minimum of data and absolute minimum of metadata present so that Bahmni starts up and can function successfully but limited to that. From there it would seem to make sense to provide separate SQL scripts that can be sourced in to provide demo patient data and/or starter metadata and content on top of this minimum installation.

I’d love for this to be prioritized so that we can start confidently using the base Bahmni install without worrying about what demo data or existing user accounts might come with it unexpectedly. Interested to hear others thoughts.

To respond to my own post, perhaps I was looking in the wrong place.

Looking now at what is available here:

It appears that there is an mrs_base.sql file which is used if the implementation name is “default”, and this contains the demo data that I describe above. There is also an openmrs_clean_dump.sql file that is used if the implementation name is not “default”. At first glance it is this openmrs_clean_dump.sql file that I was asking for.

Am I reading this correctly?

Sorry for the confusion, I’ll do more investigating with this in mind.

Thanks, Mike

PS: In case anyone is considering sourcing any of these files directly, BE WARNED. These files seem to contain the actual mysql database itself and many other internal mysql workings - it is not just the “openmrs” database. So if you try innocently sourcing these into an existing DB, and not just a vanilla install in a vagrant box or similar, then you will be in for some headaches, namely the changing of the root database password and an “openmrs” user db password. It could be a lot else is modified as well, but these were the most obvious. So, handle with care.

Bahmni team, is this expected?

Hi @mseaton,

The fresh/clean database for Bahmni is available here - https://github.com/Bahmni/bahmni-package/tree/master/openmrs/resources/openmrs_clean_dump.sql. This dump file does not have any demo data and contains only the metadata and schema of openmrs. Also this dump file does not create bahmnireportsdata, jasperserver databases, but it creates ‘test’ database which we will log it as a bug and remove it soon.

The dump file with demo data is meant to be used when implementer would like to experiment bahmni and it will be restored when implementation_name is set to ‘default’ or when you do yum install openmrs. So when doing ansible way of installation i.e. for real use, when implementation_name is set to anything other than ‘default’ and if you don’t provide any mysql dump file to be restored explicitly, openmrs_clean_dump.sql will be restored which is nothing but a clean/fresh database for bahmni.

We will update the documentation soon.

I’d suggest renaming the implement_name that installs demo data from “default” to “demo”, and have the “default” install openmrs_clean_dump.sql to avoid confusion?

+1 to renaming this to “demo” for clarity. I certainly was confused.

@preethi_s - to your comment above, thank you for logging a big to remove the test database. Can you please post back the mingle card associated with this?

However, the more insidious bug is not with the “test” database, but with the inclusion of the “mysql” database in that export as I describe above in my PS.

This is how openmrs_clean_dump.sql that you link to above starts:

> -- MySQL dump 10.13  Distrib 5.6.28, for Linux (x86_64)
> --
> -- Host: localhost    Database: 
> -- ------------------------------------------------------
> -- Server version	5.6.28-log

> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
> /*!40101 SET NAMES utf8 */;
> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
> /*!40103 SET TIME_ZONE='+00:00' */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

> --
> -- Current Database: `mysql`
> --

> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

> USE `mysql`;

> --
> -- Table structure for table `columns_priv`
> --

> DROP TABLE IF EXISTS `columns_priv`;
> /*!40101 SET @saved_cs_client     = @@character_set_client */;
> /*!40101 SET character_set_client = utf8 */;
> CREATE TABLE `columns_priv` (
>   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
>   `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
>   `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
>   `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
>   `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
>   `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
>   `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
>   PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
> /*!40101 SET character_set_client = @saved_cs_client */;

> --
> -- Dumping data for table `columns_priv`
> ...

I have no idea what havoc this caused in my existing mysql database. What I do know is that after sourcing this file my root database password was changed to “password” and my openmrs user password was removed altogether.

I think we should raise another, more urgent bug for this.

Thanks a lot for bringing this up @mseaton. Please do accept our apologies for the inconvenience. We have been focusing our testing on fresh and for “bahmni only” machine installations till now, and hence haven’t really caught this one.

A little bit of detail - the database clean script runs when -

  1. there is no ‘openmrs’ database on the mysql server
  2. an implementation_name has been chosen and this is not ‘default’ - which means a clean openmrs database will be created with no demo data

Step 2 above is where we have been assuming we will be running this step on fresh and for “bahmni only” machine installations. Given your feedback, we will work towards fixing this. It does feel wrong to be overwriting mysql server database, regardless of any assumptions, so thanks for being thorough when clearly we were looking elsewhere.

Finally, here’s the mingle card for this.

Great, thanks @rnjn. Definitely understand your focus and really like all of the work on the rpms. I get the “clean machine” approach. Hopefully as more of us come at this from other angles we can iron these pieces out together.

One other point I wanted to highlight for @gsluthra and others, is that I ended up down this path originally in following the instructions on this page: https://bahmni.atlassian.net/wiki/display/BAH/Install+Bahmni+on+CentOS

Step 2 on this page recommends running the script located at: https://raw.githubusercontent.com/Bahmni/bahmni-vagrant/master/packer/scripts/base.sh

And this script sets up the OpenMRS mysql database with demo data and many of the other issues described above using the database located here: https://github.com/Bahmni/emr-functional-tests/blob/master/dbdump/mysql_backup.sql.gz

I know a lot of this has been a rapid moving target over the past few months, but i have a hard time keeping track of what exists in the bahmni-vagrant, bahmni-package, and bahmni-playbooks repositories.

I was thinking of doing the following. On this page: https://bahmni.atlassian.net/wiki/display/BAH/Install+Bahmni+on+CentOS for now I will mention

a) A clear note saying that this setup installs a “demo database” unless it finds an “openmrs” db already existing on your machine.

b) Do I also need to say that these steps will DELETE your MySQL DB if “openmrs” db doesn’t already exist?

@rnjn Can you please confirm the above?

@mseaton Did you have other suggestions for the page above?

I think that is, if there is no openmrs database and if you haven’t specified your own database in the installation configuration.