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

+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.

@gsluthra and @vsingh, I guess my first question is whether this page actually is the preferred way for installing Bahmni, and how it relates to the bahmni-installer RPM package, which I don’t see referenced anywhere.

We have been using the bahmni-installer RPM as an alternative approach to running the script referenced on that page. And this bahmni-installer RPM has similar, but not exactly the same, behaviors. So that I think is confusing this topic a bit, since there are various moving pieces.

Are we taking the wrong approach by using the bahmni-installer? Sorry if this doesn’t directly answer your question, but I feel like this is an important clarifying point to address.

Thanks, Mike

@cioan reminided me that we took this approach of using the Bahmni installer based on this wiki page: https://bahmni.atlassian.net/wiki/display/BAH/Install+Bahmni+on+Centos+using+Ansible

And, this page clearly says that this is in progress, and to use the other page we are discussing above, so I guess that answers my question :slight_smile:

As a group that is working on our own automated deployment scripts that include Bahmni, we took this approach with the hope of not having to rewrite everything to use the bahmni-installer a few months down the line. Would still be interested in your thoughts on this decision and whether you are still headed in this direction.

Mike

Gurpreet will have more details. The way forward is rpm based installers for Bahmni, in next release i.e. 0.81 we will completely move to it. We had only OpenMRS part of installation working hence we had kept the old way of installation. In next release all parts would have been migrated to run this way.

Clearly we need to get better are communicating these.

Hello guys, Trying to get a fresh db but i am unable to connect to openERP db…

username and password not validated

@puneet1984 Please refer to this document on connecting to databases: https://bahmni.atlassian.net/wiki/display/BAH/Connecting+to+various+databases

Hi Vsingh, I’m trying to clean all patient data of my Bahmni installation, and this is what I have now:

I couldn’t run the deletePatientData.rb because I couldn’t install pg gem. It was an ruby version isue, I tryed to upgrade ruby but now it can’t find the path to ruby (I’m not too good about ruby :blush:)

Then I went for executing the sql scripts and the deletePatientDataForOpenMRS.sql worked like a charm! All my patients are gone.

But when executing the deletePatientDataForOpenElis.sql it didn’t work. It finished with the following errors messages:

Output from uploaded SQL commands …

psql:/tmp/.webmin/664285_425_1_exec_file.cgi:31: ERROR: relation “result_signature” does not exist psql:/tmp/.webmin/664285_425_1_exec_file.cgi:33: ERROR: relation “person” does not exist LINE 1: delete from person where not exists (select p.person_id from… ^ psql:/tmp/.webmin/664285_425_1_exec_file.cgi:35: ERROR: relation “event_records_offset_marker” does not exist

Can you help me with some instructions on how to solve this?

Regards,

German

Hi German Acevedo,

According to the output you pasted, looks like you don’t have proper OpenElis database. How did you install Bahmni ? Is OpenElis up and running in your system ? Can you check if table “result_signature” and “person” exists in your database.

Thanks, Sravanthi

Here is a link explaining how to connect to OpenELIS database. Maybe the script is connecting with a user that doesn’t have rights to see the tables. Please check.

https://bahmni.atlassian.net/wiki/display/BAH/Connecting+to+various+databases

Hi Sravanthi, I installed Bahmni following the instructions on https://bahmni.atlassian.net/wiki/display/BAH/Install+Bahmni+on+CentOS The install ended without any errors, and OpenElis, Bahmni, OpenMRS and OpenERP are all working fine. In my server I have webmin as control panel, and I log into it as root. This are screenshots of what I see about the requested tables:

As Gurpreet already mentioned, Can you please try using “clinlims” user.

german2209, just to clarify you are passing the .sql script to postgres, correct? openElis uses postgres, not mysql.

btw, which ruby version does the “deletePatientData.rb” script require? I tried with ruby 1.8.7 on CentOS 6 but it doesn’t work (sintax error).

Hi Sravanthi, I tryed again with user clinlims, this time through phpPgAdmin, but again it didn’t worked, this is the result after running the deletePatientDataForOpenElis.sql:

Error de SQL:

ERROR: syntax error at or near “table” LINE 1: SELECT COUNT(*) AS total FROM (truncate table result_signatu… ^

En la declaración:

SELECT COUNT(*) AS total FROM (truncate table result_signature, referral_result, referral, result_inventory, result, worksheet_analyte, note, report_external_export, report_external_import, analysis_qaevent, analysis_storages, analysis_users, analysis, sample_qaevent, sample_requester, sample_human, sample_newborn, sample_animal, sample_environmental, sample_item, sample_organization, sample_projects, sample, observation_history, patient, patient_identity, patient_occupation, person_address, patient_patient_type, patient_relations, organization_contact;

delete from person where not exists (select p.person_id from provider p where p.person_id = person.id);

truncate table event_records_offset_marker) AS sub

Hi Nostalgicamigo, yes I’m using phpPgAdmin to run the script.

Hi German Acevedo,

Can you try the below command from command prompt to delete the data from OpenElis. It worked for me.

psql -Uclinlims clinlims < deletePatientDataForOpenElis.sql

Make sure you have the deletePatientDataForOpenElis.sql file in the folder where you are executing the command.