Can't connect to Remote MySQL server

Connecting to SQL
1

Hi guys, love your application but there’s one issue that really drives me crazy.
For some reason I just can’t connect to a remote MySQL server I have.
This is what I am doing:

And this is the error I see in the Dreamfactory app:

[2014-06-14 09:47:53] app.ERROR: REST Exception #500 > Failed to launch service “dwasoft”: CDbConnection failed to open the DB connection. {“host”:“ec2-54-200-30-27.us-west-2.compute.amazonaws.com”,“request_uri”:"/rest/DWASoft",“source_ip”:“79.179.71.22”,“sapi_name”:“apache2handler”}
[2014-06-14 09:47:53] app.ERROR: SQLSTATE[HY000] [2003] Can’t connect to MySQL server on ‘95.154.203.39’ (111)

I checked on my MySQL machine for any access attempts and I see none.
I think I configured something wrong in the Dreamfactory Admin screens.
Please help!
Many thanks, Pavel

2

OK, found the problem… The Amazon EC2 machine that hosts the MySQL server didn’t allow incoming MySQL requests… Had to change under security group

3

Good stuff. Thanks for the insight!

-Mark

4

Hi Pavel,

Did you have to include DreamFactory’s CIDR/IP to the security group? If you did, where would I get the CIDR/IP of my DreamFactory DSP instance? Would that be the DSP instance?

I have an RDS MySQL instance, and I’m trying to get my DSP instance to connect to it. Right now, I have the same failure message.
“Failed to launch service ‘database’: CDbConnection failed to open the DB connection.”,
error code: 500

5

Hi,

Yes, you will have to open ports and allow specific IPs under your security group.

Please refer to these guides:

Be sure to read guides

http://aws.amazon.com/documentation/gettingstarted/

EC2 Security

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EC2_Network_and_Security.html

RDS

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.html

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.html

After going through these, especially the network and security one, under EC2 Security as the concepts and ideas grasped here have a ripple effect, meaning…if you can figure out and understand how to open ports in EC2, then this knowledge dynamically applies throughout the AWS platform.

Please let me know your status.

  • Mark
6

Hi Mark,

Thanks for replying right away, but I have that part figured out for the most part.

What I’m trying to get information for is the actual CIDR/IP or IP range for the DSP instance, so I can securely set my EC2 instance to only allow incoming communication from my DSP hosted application/service.

Is this possible, or would I need to open it up to the public? meaning 0.0.0.0/0 which I would rather not want to do. As it is wide open then.

Thanks…
Har

7

I ended up just creating a DSP EC2 instance and including my internal IP to the RDS security group. That took care of my issue. But I couldn’t get a free hosted DSP service to connect to my secure RDS.

8

Okay - will a EC2 instance work to start off with? And as your application gets bigger, you can scale as need be, all within AWS. Or, did was the RDS something that you’ve had all along and wanted to add DSP?

Thank you,

  • Mark
9

Yes, the EC2 instance at least from the perspective of auto-scaling within AWS is beneficial. I wanted to see if having it hosted on DSP could possibly be the other avenue of doing the same thing in regards to scaling and not having to worry about maintenance of it. But, this works well too. I do have more control of it in this regard. I didn’t want to accrue any unwanted expenses currently until the app was actually up and running and making some revenue.

I had the RDS instance all along, even a PHP service layer… but didn’t want to deal with creating my own authentication model, etc… and DSP got me up an running quickly out of the gate. I wanted to get DSP to connect to my php layer at first, now I just have it connected to my RDS… I may still use the PHP layer…but I can also port a lot of the logic out and maybe utilize stored procs.
Still tinkering a bit more… DSP is pretty awesome… and the more I peel back the layers… the more it gives me a lot of flexibility.

10

Great. Another great thing about AWS is that they’re making the EC2 servers eligible for free for a length of time. I would recommend keeping DSP on your side, then you can customize it as you wish - whereas, if you’re on our server, everything has basically been pre-configured. It should not be too terribly difficult to get connected to your RDS. Give me some time and I will take a more detailed look into making some sort of connection between the two, if possible.

Thanks,

  • Mark
11

@Har,

I am facing the opposite of your situation. I am able to connect using DSP but not using my amazon EC2 linux based instance (built using bitnami installer). Do i need a MS Sql server driver for my EC2 linux instance?

12

Hi @jayp, I’m not sure @Har is around to answer your question. Plus, you posted in a thread related to MySQL–but it seems you’re using MS SQL.

Yes, you do need drivers to connect to MS SQL. Since you are using a Bitnami instance installed in Linux, the pdo_dblib.so driver should already be present in your /php/lib/php/extensions/ directory. You should ensure the proper line (extension=pdo_dblib.so) is uncommented in your php.ini file and then restart your Bitnami servers.

If you still receive errors, I recommend starting a thread related to MS SQL rather than using this thread, and posting the error(s) from your log files there. You may also contact me directly at support@dreamfactory.com.

13

@jayp,

I’m not quite sure what your question is. Can you elaborate more.
If your question pertains to MS Sql Server, then I don’t think I would be able to help you, as I have not worked with that and DSP. It could be as simple as what JeffreyStables suggests with the driver.

But if I assume your question is related to a DSP instance that you spawned on an EC2 instance and you can’t get that DSP to connect to your MS SQL server. My first question would be, are both your DSP and MS SQL Server on the same AWS network (RDS or an EC2) or is your MS SQL Server on a separate network (or even on your local network)?

If they are on the same AWS network, you should be able to configure your RDS Security Group. Set the CIDR/IP of your DSP EC2 instance (make sure you grab the internal IP) in that group. That should allow your DSP EC2 instance communication access to your AWS RDS MS SQL Server.

If your MS SQL Server is not on AWS RDS or EC2, then you would need to check out other variables. For instance, your firewall settings for your MS SQL Server, other network configurations you have for it. Whether you have a static IP for it - that your DSP can connect to, etc. From the DSP config side, I would think it would be more straight forward if you have all the other network configs for your MS SQL Server set up correctly.

HTH,
Let me know how you turn out…
Harry

14

Check your remote access configuration. It should look something like this:

<?php

function getdb(){

$servername = "46.101.5.233"; // put your cloudways server IP here
$username = "qxxfumxxxbd";
$password = "xxxxbQxxmM";
$db = "qxxfumxxxbd";

try {

   $conn = mysqli_connect($servername, $username, $password, $db);

    //echo "Connected successfully";

   }

catch(exception $e)

   {

   echo "Connection failed: " . $e->getMessage();

   }

   return $conn;

}

Try using some mysql client to connect to the database, like SQLYOG.

Source: remote mysql connection setting