Hi,
We’re looking to migrate our OpenEMR instance from Windows to the OpenEMR Cloud Express on the AWS Marketplace. But after exporting the openemr table in phpmyadmin (as a .sql) we’re at a loss. Looks like the cloud express is running apache and mysql in docker and phpmyadmin is nowhere to be found at localhost/phpmyadmin for importing the openemr db.
Any help would be greatly appreciated, seems like a common use-case. Thank you!
hi @Medconsult, you can do it pretty easily from the terminal. You scp the file to the aws instance, then docker cp into the mysql container, then run mysql -uroot -proot openemr < openemr.sql from the mysql container command prompt.
hi @stephenwaite. I followed your steps, after first having to DROP and CREATE a new openemr database (it was throwing the error that “addresses” already exists when trying to directly import the database). However, I ran into an error when trying to login to openemr: “FATAL ERROR: crypt() function is not working correctly in OpenEMR”.
It says on the wiki that if this error occurs, I should immediately report it on the forums.
How do I get around this error? It is not incredibly urgent, though.
Thanks
This is the sequence of steps I took
Export openemr.sql file from lightsail instance
Use winSCP to move this file to the ubuntu user area on EC2
In puTTY: " sudo cp openemr.sql /root "
Switch to root user: " sudo -i "
" docker cp openemr.sql lightsail_mysql_1:/ "
" docker exec -it $(docker ps | grep mysql | -cut -f 1 -d ’ ') /bin/bash "
" mysql -u root -p " then input password “root”
" DROP DATABASE openemr;"
" CREATE DATABASE openemr; "
" USE openemr; "
“quit”. Mysql says bye.
" mysql -u root -p root openemr < openemr.sql "
UPDATE users_secure SET password= “$2a$05$MKtnxYsfFPlb2mOW7Qzq2Oz61S26s5E80Yd60lKdX4Wy3PBdEufNu”, salt= “$2a$05$MKtnxYsfFPlb2mOW7Qzq2b$” WHERE username = “admin”
this will reset the admin password to pass
Still receiving the same error when trying to put in one of our own usernames and passwords, and when putting in “admin” and “pass”, it gives a simple invalid password error.
When importing the openemr database, it says that “244 rows affected”. This seems low. How many rows should be affected on a large database like this?
When checking the size of each table in the database, the numbers do add up to the correct amount.
I ran into a similar problem when I was starting out on AWS and the issue was that my password was too complex (20 characters long with multiple special characters). I repeated the setup of my OpenEMR system with a simpler (but non-trivial) password, and I was able to to connect to my database without any problem after that. For some reason, the setup scripts could not handle my password. I have no idea if that is your problem, but there are some details on AWS like this that are not obvious.
After I tried both the Standard and Express Edition, I eventually settled on the OpenEMR Cloud Standard edition rather than the Express. One thing that I like about the Standard edition is that it uses the AWS RDS services, which are quite elegant and powerful with automatic backup, simple restore, etc. You also get a breakdown of your costs on database vs webserver, for what it is worth. The RDS services are very powerful and easy to use. Automatic daily images to backup your data every day for 14 days at a time (configurable) and performance metrics are available so you can monitor what you are doing on the database versus the webserver. This “modular” design becomes important as your system grows, you add https, host a domain on Amazon for security reasons, and your costs start to increase. Separating the database from the web server provides you a way to see what is consuming a lot of database usage, for example, and when it is happening.
You can simply kill the EC2 instance running the Express and choose to build an OpenEMR Standard if you would like to go that route. I recommend it for anything non-trivial unless you really have a lot of AWS experience and understand how much storage and CPU time you are going to use now, 1 year from now, and maybe 5 years from now unless you really like to tinker with cloud systems under high-pressure conditions.
I would also opt for Amazon Technical Support (the developer/email one), which is not very expensive ($29 a month or 3% of usage) and their technical support is exceptionally good (unlike tech support you get from most anything else). Trying to figure everything I need to do on AWS out on my own is simply too time-consuming, and the tech support is a time-saving and “life-saving” bargain. They really know their technology inside and out and give you well thought-out, tested, and detailed replies that solve your problem, usually with one reply but more if you have something very tricky to solve. The folks on here (like Stephen) are an invaluable help, but they cannot look at every single little detail on your system and diagnose why it is not working for your special case unless you are a lot smarter than I am about giving him all the details that influence the solution, which is certainly very possible but beyond my abilities.
Amazon AWS also has database migration services. It isn’t very expensive (they claim you can migrate a database for as little as $3 … even if they are off by a factor of 10, it sounds cheap). I have not used this service because I started using OpenEMR on AWS. However, the reviews I have read are very positive. Together with tech support, you should be able to migrate your database successfully. To say that the AWS systems with their unique networking, security, and required settings are technically complex is an understatement. Unless you have an in-house AWS database expert, there are going to be problems that you will have no clue about – problems that Amazon tech support can help you sort out with relative ease because they deal with them on a frequent basis.
–Ralf
One more thing … In MySQL, by default, the username is root and there’s no password. Consider trying that if you haven’t already.