Openemr standard cloud EC2 instance not communicating with RDS

Situation
I have the standard edition install and had it up and running with ssl being applied through the use of a load balancer. Unfortunately, while trying to be able to access RDS through MySQL Workbench, modifying the security group inbound rules, I managed to alter communication between EC2 and RDS though they are in the same virtual private cloud. When attempting to access the webpage, I now receive a 504 Gateway Timeout error, often requesting that I ping the RDS (db).

OpenEMR Version
I’m using OpenEMR version Standard Cloud

Browser:
I’m using: Google Chrome

Operating System
I’m using: Using Ubuntu

  1. Created the recovery stack, but not sure how to launch it. Any instructions (general steps) beyond what is documented at
    https://github.com/openemr/openemr-devops/tree/master/packages/standard#openemr-standard

would be greatly appreciated as I am lost with how to proceed.

  1. What should the RDS security group inbound settings be to allow the EC2 instance to reach it. I tried the public IP4 address, now the elastic IP address, but was not successful. Also, tried feeding it the webserver security group, but still failed.

any thoughts?

Without knowing what you changed it’s hard to tell you what to fix. Consider starting a second instance of Standard to review the security groups and networking choices. I can tell you that the RDS instance has no public IP and it was never intended to be accessible outside the VPC without some sort of tunnel.

1 Like

Thank you for your input. This was definitely user error on my part. I ended up deleting and relaunching the instance. After having started a second instance in a different region and comparing all of the security groups {inbound/outbound rules}, ACLs, ENIs, route tables, subnets, etc., I concluded that the damage was not going to be repaired. I think while trying to access the RDS, though in the same VPC, I must have changed some setting because in looking at some error logs it appeared that the db had no association with my account whatsoever. I still could access the db remotely, but my EC2 instance could not longer communicate ( even with modifying security groups, load balancer idle times, and keep alive settings, etc). Lesson learned!

Glad to hear it worked out. Standard was a bit of a bear to put together. If you’re really curious, you might consider reviewing https://github.com/openemr/openemr-devops/blob/master/packages/standard/cfn/stack.py , a Python script that produces the AWS CloudFormation script that powers Standard. This script (plus magic from the AWS Marketplace staff) with a pre-prepared EC2 image makes up everything going on with a deployment.

Thank you. I will look into it.

You will need to create additional permissions in the security groups and possibly the routing tables as well. By the way, AWS Support can often help you resolve this if you get a support plan. You need to make sure the communications are allowed.

Here is an example session with AWS Support to repair the kind of issue that you describe:

EC2 unable to connect to RDS instance:

xxxxxxxxxxxxx

ubuntu@ip-10-0-2-246:~$ telnet xxxxxxxxxxxxxx.us-east-2.rds.amazonaws.com 3306
Trying 10.0.4.123…
telnet: Unable to connect to remote host: Connection timed out
Source: i-xxxxxxx (OpenEMR WebServer)
Destination: xxxxxxxxxx.amazonaws.com

10:34:12 PM (AWS Support): I am reading your case now
10:34:31 PM (AWS Support): Can you please provide your RDS endpoint?
10:34:36 PM (AWS Support): And EC2 instance id please?
10:34:50 PM (Ralf Lukner) : i-xxxxxxxxxxx
10:37:02 PM (AWS Support): So I can see they both reside in same VPC: vpc-xxxxxxxxxxxxxx
10:37:06 PM (Ralf Lukner) : Yes
10:37:22 PM (AWS Support): EC2 has egress rule to ALL, and NACL and Route Table looks good there
10:37:28 PM (Ralf Lukner) : Okay
10:37:47 PM (AWS Support): For RDS security group, there is no rule to allow connection from EC2
10:37:58 PM (AWS Support): So you can add ingress rule to its security group: sg-xxxxxxxxxxxxxx
10:37:58 PM (Ralf Lukner) : Okay. Let me look.
10:38:09 PM (AWS Support): For port 3306
…
10:40:46 PM (Ralf Lukner) : What type do I use?
10:41:05 PM (Ralf Lukner) : For the inbound rule
10:41:23 PM (AWS Support): TCP 3306
10:42:04 PM (AWS Support): There is also one called MySQL as well which automatically gives you TCP 3306 for you
10:42:44 PM (AWS Support): MySQL/Aurora
10:43:21 PM (Ralf Lukner) : Is source “Anywhere”?
10:43:30 PM (AWS Support): Custom
10:43:45 PM (Ralf Lukner) : Okay
10:43:58 PM (AWS Support): In the text box: sg-xxxxxxxxxxx
10:46:24 PM (AWS Support): Go to RDS instance on RDS Console
10:46:42 PM (AWS Support): select security group
10:46:59 PM (AWS Support): Sorry give me a moment…
10:47:31 PM (AWS Support): sg-xxxxxxxxxxxxxx -> I am only seeing one ingress rule for this security group
10:47:43 PM (AWS Support): And it is attached to RDS instance
10:47:58 PM (AWS Support): ok good
10:48:02 PM (AWS Support): edit inbound rules
10:48:26 PM (AWS Support): sg-xxxxxxxxxxxxxxxx
10:48:30 PM (AWS Support): This for source
10:48:49 PM (Ralf Lukner) : That worked. Thank you!
10:48:51 PM (AWS Support): great no worries
10:48:58 PM (AWS Support): do you have any more questions or can I close case?
10:49:09 PM (Ralf Lukner) : Yes. Thank you!

There are also cases where I have had to modify the routing tables to allow certain connections. In any case, I hope the above is helpful. To work with standard, you or someone who works with you will eventually need to learn how to navigate the above.

Another key pointer is always to make sure that the permissions on your EC2 docker directory are correct. That can cause a lot of grief after doing a restore or system migration.
–RBL

1 Like

Hi @Ralf_Lukner, you can connect to the RDS from the OpenEMR docker without touching any of the rules in the EC2. Here’s a snippet of firing up a mysql docker and then using it’s mysql client to connect to the RDS database:

  1. ssh into aws and go super user
sudo bash
  1. fire up a mysql 5.7 docker
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.7
  1. add image to standard’s docker network
docker network connect standard_default some-mysql
  1. get inside docker for mysql client access
docker exec -it $(docker ps | grep some-mysql | cut -f 1 -d " ") /bin/sh
  1. finally connect to standard RDS, execute any needed mysql commands, like upgrading ICD10 supported_external_dataloads
mysql -u openemr -p'password!' -h 'crazy-host-name.us-east-1.rds.amazonaws.com'
use openemr;
INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES ('ICD10', 'CMS', '2021-10-01', '2022-Code Descriptions.zip', '11d1d725c84e55d52ef6633da88aa137');
INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES ('ICD10', 'CMS', '2021-10-01', 'Zip File 3 2022 ICD-10-PCS Codes File.zip', 'a432177acbdaf9908aa528078ae72176');
2 Likes

Thank you for sharing. It is very helpful. I am up and running on new install. I also made sure to maintain a development instance on my own server to test out forms and templates that I have been developing. I had too many parts changing too quickly for someone new to AWS and relatively new to working with openemr codebase. I do have a support package and am now looking for the best way to back up the EC2 instance and RDS. Do you typically save a snapshot/automated back-up (RDS), snapshot (EC2 instance), and/or save AMIs (images)?

Of note, I am now running mysql:8.0 for the RDS, Ubuntu 20.04 as the operating system, and installing patch 2 in openemr. So far, everything is working well.

Thank you for this. I was trying to figure out how to access the RDS instance from either command line or MySQL workbench when I caused RDS to no longer communicate with the EC2 instance. Although I was able to reach RDS from MySQL Workbench, I never figured out how to reach it from command line and ultimately was not able to re-establish connection between the EC2 instance and the RDS. Ultimately, I had to start over. I appreciate the value add!

Superb. I only create development instances and databases when I need them to save money. I automatically do daily database backups and retain them for 15 days. I do manual backups from time to time, including MYSQLDUMPs and store those in S3 along with a backup of all the “documents” and other key files from the EC2 OpenEMR docker volume. I also make AMIs occasionally.

I’m glad to hear about Ubuntu 20.04 and MySQL:8.0!

RBL

1 Like