Unable to start endtb distribution with provided database

Hi Bahmni team,

As of yesterday I started getting an error while trying to set up Bahmni for endtb with the database located here: https://bahmni-repo.twhosted.com/endtb/release-1.0/mysql_dump.sql

The error I am getting is as follows:

ERROR 1449 (HY000) at line 7501: The user specified as a definer ('openmrs-user'@'') does not exist

Grepping the mysql_dump.sql file above for “” returns the following:

[vagrant@192 deployment-artifacts]$ grep '' mysql_dump.sql 
INSERT INTO `db` VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y'),('%','test\\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y'),('%','openmrs','openmrs-user','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),('localhost','openmrs','openmrs-user','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),('','openmrs','openmrs-user','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'),('','openmrs','openmrs-user','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO `user` VALUES ('localhost','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',NULL,'N'),('','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',NULL,'N'),('','openmrs-user','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','','N'),('localhost','openmrs-user','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','','N'),('%','openmrs-user','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','','N'),('','openmrs-user','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','','N'),('','root','*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','','N');
/*!50013 DEFINER=`openmrs-user`@`` SQL SECURITY DEFINER */
/*!50013 DEFINER=`openmrs-user`@`` SQL SECURITY DEFINER */
/*!50013 DEFINER=`openmrs-user`@`` SQL SECURITY DEFINER */
[vagrant@192 deployment-artifacts]$

Can you please let me know if there is something I am doing wrong, or perhaps this database artifact was recently updated with this error (I wasn’t having this problem a few days ago)?

Perhaps instead of distributing this SQL separately it could be included within the endtb_config.zip (this would allow us to see it’s version history within the endtb_config repository).

@petmongrels / @shruthidipali / @maheshonopenmrs or others? (Not sure the right list to ping about endtb issues)

This sql is a full db dump (containing mysql system db and tables) and not just an OpenMRS database dump. If we restore this dump without any existing database, it works fine. In our earlier discussion, we spoke about removing unneccessary databases like jasperreports which we have done.

We are also working on providing just the openmrs database without any other database (like mysql). The ansible scripts will be updated to take care of creating a specified user mentioned here as part of the installation:

We will update you when this is completed.

I hope this is not a blocker for you to test out Release 1.0. You can still get this deployed onto your machines and run Bahmni as usual.

We will be checking-in the updated version of endtb sql in endtb-config repo and update you. Please feel free to pull us in a call if required.

Thanks @maheshonopenmrs,

Hmm. I was actually experiencing this by simply running through the Ansible install on a fresh CentOS Vagrant box. I was aware of the issue of this dump containing all of the mysql tables.

If you are able to run through this without any errors, can you please share the setup.yml file that you are running the installation with?

Thanks, Mike

I have tried several times (5-6) with the exact steps that we have mentioned in release notes and it was running perfectly fine. Please find my setup.yml file content here:


Can you please try again with this and let me know if you are able to run it without errors?

After a bunch more testing, I’m pretty sure this is a real issue. No matter what I do, I cannot get endtb to install using the database supplied under the 1.0 release.

Please confirm that you can get this to work using the database at: https://bahmni-repo.twhosted.com/endtb/release-1.0/mysql_dump.sql

Note: if I use the non-release-1.0 version, things work.

To be explicit…

This works: https://bahmni-repo.twhosted.com/endtb/mysql_dump.sql This does not work: https://bahmni-repo.twhosted.com/endtb/release-1.0/mysql_dump.sql

Here is the error I see when using release-1.0/mysql_dump.sql:

> TASK: [bahmni-emr | Install openmrs database dump if provided and database does not exist] *** 
> <localhost> EXEC ['/bin/sh', '-c', 'mkdir -p $HOME/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940 && echo $HOME/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940']
> <localhost> PUT /opt/bahmni-installer/bahmni-playbooks/roles/bahmni-emr/files/restore_mysql_dump.sh TO /root/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940/restore_mysql_dump.sh
> <localhost> EXEC /bin/sh -c 'sudo -k && sudo -H -S -p "[sudo via ansible, key=uemuhvoxzdttpnzzsarmtryryqbwjhdm] password: " -u root /bin/sh -c '"'"'echo BECOME-SUCCESS-uemuhvoxzdttpnzzsarmtryryqbwjhdm; chmod +rx /root/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940/restore_mysql_dump.sh'"'"''
> <localhost> EXEC $SHELL -c 'sudo -k && sudo -H -S -p "[sudo via ansible, key=xffenebkcakxvpxcsdsisgijpwshkcxf] password: " -u root $SHELL -c '"'"'echo BECOME-SUCCESS-xffenebkcakxvpxcsdsisgijpwshkcxf; LANG=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 /root/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940/restore_mysql_dump.sh '"'"''
> <localhost> EXEC ['/bin/sh', '-c', 'rm -rf /root/.ansible/tmp/ansible-tmp-1458306012.35-61858280348940/ >/dev/null 2>&1']
> failed: [localhost] => {"changed": true, "rc": 1}
> stderr: + . /etc/bahmni-installer/bahmni.conf
> ++ OPENMRS_DB_USERNAME=openmrs-user
> ++ OPENMRS_DB_SERVER=localhost
> ++ mysql -h localhost -uroot -ppassword --skip-column-names -e 'SHOW DATABASES LIKE '\''openmrs'\'''
> Warning: Using a password on the command line interface can be insecure.
> + '[' '' '!=' openmrs ']'
> + echo 'openmrs database not found... Restoring a base dump'
> + mysql -h localhost -uroot -ppassword
> Warning: Using a password on the command line interface can be insecure.
> ERROR 1449 (HY000) at line 7501: The user specified as a definer ('openmrs-user'@'') does not exist

> stdout: openmrs database not found... Restoring a base dump

> FATAL: all hosts have already failed -- aborting

@mseaton, @cioan, @jmbabazi, can you please try using this database dump instead and let us know if you are still facing the same issue. This database dump is having UAT fixes.

I just tried this out based on the original instruction, and I got the same error that @mseaton is describing.

The exact commands I did are here (I tried this twice and it failed the same way both times):

(Now I will try this with the new database dump.)

I tried with the new database (exactly my recipe from before but getting the db dump via

wget https://raw.githubusercontent.com/Bahmni/endtb-config/release-0.80/dbdump/mysql_dump.sql

The final error:

A few lines before this was also red:

@darius Looks like the second issue during installation is something related to permissions on the filesystem. Did a quick google on “Error in POSTTRANS scriptlet in rpm package”. I think you can try to do a “yum remove -y bahmni-web” and retry the installation. Its definitely not related to database restore.

@mseaton I have picked up the latest db dump that mahesh has pointed out and did a clean install on a digital ocean droplet. It is successfully installed. Mahesh removed all the instances to specific ip address. Can you please try with the latest db and tell us if it is fine? Thank you.

Thanks @bharatak will try to test it out today.

PS - I’m in Rwanda currently, which has reinforced with me the need for an alternative strategy that can utilize cached or pre-downloaded artifacts. I know this is possible in theory by setting up a local yum repository. I may try to do this using one of my existing vagrant boxes. Otherwise it is really difficult to test vanilla provisioning using the bahmni installer from a poor bandwidth setting. Will try to document this for others if I’m able to make any progress this weekend.

@mseaton Sure. Is it just the EMR piece of bahmni you are looking for? or any other component, like erp, lab? Let me know if I can help you.

That new database worked! I’m very happy we’re over that hurdle :slight_smile: Thanks for getting that fixed.

I’ll post on a different topic about my success in getting all of this to work without internet. It took a little doing, but all in all not too hard of a process.