cancel
Showing results for 
Search instead for 
Did you mean: 
Go to solution

Connecting to a Remote MySQL Database

Scenario: I have software running on my PC, Unicenta oPOS (Point of Sale). It is using a MySQL database.

 

I am using the Magento E-commerce software on my website, which will also work off of a MySQL database. I have a plugin for Magento called ASULPUNTO that will remotely connect to a database and sync my online store to my local POS software, allowing me to keep both my local sales and my online sales managed in one database and track inventory, etc.

 

However, the website refuses to connect to the database. I know it is open and the ports are correctly forwarded, as I can connect to it from my home PC using MySQL Workbench with no issues.

Here is all of the configuration the website software allows me to enter (I have made it generic, but this is the formatting I am using... should I format the host name some other way???):

 

Hostname: 123.456.78.90:3306
Database Name: unicenta

Database Login:unicenta

Database Pass: ************

 

There are no other options here. Perhaps there are some other things I need to do in order to allow my GoDaddy hosting to connect to a remote database? Perhaps I am formatting the Hostname wrong (I entered it the same way on MySQL Workbench, does it fix it behind the scenes?)....

Any and all advise would be awesome. This is the last major step to make things work together... I put a lot of time into updating my database from Apache to MySQL and all the ground work for this setup... Kinda bummed that it's not working!

 

Thanks in advance!

21 REPLIES 21
Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

You need allow unicena user from your PC to be accessed remotely from your magento IP

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com
Highlighted

Re: Connecting to a Remote MySQL Database

I have opened the Unicenta user to all incoming traffic for testing purposes, with intent to limit it once I got it working. As I said above, I am able to connect from my personal PC on a different IP address, but Magento will not connect.

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

Hi,

Sorry about that.  I didn't realize that PC and Home PC were 2 different computers

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

No worries!

 

Any other suggestions? I put a ton of work into getting this stuff set up, will really suck to fail at this point... haha

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

@overkill452,

 

Are you able to write your own PHP script and do a query?  I'm thinking of isolating the problem.  We know that it's not your home PC, we don't know if it's your whole server or just magento.

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

It's not something I have done before. I will see if I can find a tutorial online and give it a go though!

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

@overkill452,

If your Magento is hosted in GoDaddy,  I'll be willing to troubleshoot it with you.  Send me the email address that you use with GoDaddy via private message if you're interested.

 

 

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

 

I gave this script a try and was able to connect and return a valid result "There are 51 tables"... So, it would seem that I can connect to the database with GoDaddy, but not with Magento.... How strange.

 

I can't reply with the code.... so I will try without to see if that's what's giving me the error... lol

Edit: Yea, I get some crazy error when I try to add the code I used... I guess it's not important, I formatted the hostname the same (123.456.78.90:3306) and it works with the basic script....

Re: Connecting to a Remote MySQL Database

 

As far as I can tell, this is the code that is making the connection through Magento. I tried posting the code... but GoDaddy wont let me... so I took a screenshot lol.

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

Yeah,

 

It's really annoying and it makes it hard to help people to code if you can't post codes.  Anyway, good job on narrowing it down.  I'm not not familiar with Magento innards but it is PHP none the less.  It looks like your database connection is saved in a database.  You can test if Magento is actually using the correct connection by adding:

echo $url.' '.$login.' '.$password;

Above:

return $db;

 

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com
Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

Or, you can try hijacking the object:

try{
      $db = new PDO("mysql:host='123.456.789.0:1234'; dbname='yourDatabase'...

 Look for the try catch block of your connection and hard code your database credentials.

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

It does print the results...

 

I haven't been able to find this magical log file it speaks of though :\

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

That is really odd.  With regards to the log... you can always make your own or print it on the browser instead.

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

It's been years since I have done this stuff... haha. I'm not really sure how to go about doing that.

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

OMG, I tried responding to this post multiple times... I guess I cant post PHP codes

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

Re: Connecting to a Remote MySQL Database

It's so frustrating! lol
I have errors now!

 

 

Sorry for my delay... this page keeps breaking... lol... Perhaps we could email or use another communication method?

 

 

 

I am formatting my hostname in two different ways: 123.456.78.90:3306 With this configuration, I get this set of errors:

2017-03-22T12:55:57+00:00 DEBUG (7): Asulpunto_Unicentaopos_Model_Unicentaoposapi::_checkApplicationTest Connection Failed
2017-03-22T13:03:23+00:00 DEBUG (7): Asulpunto_Unicentaopos_Helper_Data::getUnicentaOposConnection ERROR Getting Connetion URL:123.456.78.90:3306 DBNAME:unicenta Login:unicenta Password:mypassword
2017-03-22T13:03:23+00:00 DEBUG (7): Asulpunto_Unicentaopos_Helper_Data::getUnicentaOposConnectionSQLSTATE[HY000] [2005] Unknown MySQL server host '123.456.78.90:3306' (2)
2017-03-22T13:03:27+00:00 DEBUG (7): Asulpunto_Unicentaopos_Model_Unicentaoposapi::checkActivateSHOW TABLES: IS EMPTY
2017-03-22T13:04:35+00:00 DEBUG (7): Asulpunto_Unicentaopos_Model_Unicentaoposapi::checkActivateSHOW TABLES: Array

123.456.78.90
With this configuration, I get this error:

2017-03-22T13:06:21+00:00 DEBUG (7): Asulpunto_Unicentaopos_Model_Unicentaoposapi::_checkApplicationTest Connection Failed

 

Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

geeze... Still cant post codes.

 

put the port on a separate parameter.

port=3306 

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com
Helper VI Helper VI
Helper VI

Re: Connecting to a Remote MySQL Database

mysqlCOLONhostEQUALSipSEMICOLONportEQUALS3306SEMICOLONdbnameEQUALSyourDbName

This might help:

Google this pdo() mysql full dsn

~Jan Mykhail Hasselbring Web Administrator @ fullstackwebsolution.com

MySQL Remote Connections

How do I allow another IP to connect to a database to create a table?

Cant connect to mysql database

I have created a database using the database Wizard and added the users with all credentials, when I try to get a connection to my database it fails my domain is swaziprocurement.com and I have a single page for now swaziprocurement.com/connect.php but I am failing to connect to my database when I test this connection.

Here is my code:

<?php
$db_name="mbuso81";
$db_user="redsey";
$db_pass="redsey";
$db_host="swaziprocurement.com";
$conn=mysqli_connect("swaziprocurement.com","redsey","redsey","mbuso81");
if (!$conn){

echo "Fail!";
}
else {

echo "Success!";
}
?>

I have a connection to a database that I just created using the wizard but I cant seem to connect to it when I test the connection using PHP

Employee
Employee
Solution

Re: Cant connect to mysql database


@speed_daemonwrote:

I have created a database using the database Wizard and added the users with all credentials, when I try to get a connection to my database it fails my domain is swaziprocurement.com and I have a single page for now swaziprocurement.com/connect.php but I am failing to connect to my database when I test this connection.

Here is my code:

<?php
$db_name="mbuso81";
$db_user="redsey";
$db_pass="redsey";
$db_host="swaziprocurement.com";
$conn=mysqli_connect("swaziprocurement.com","redsey","redsey","mbuso81");
if (!$conn){

echo "Fail!";
}
else {

echo "Success!";
}
?>

I have a connection to a database that I just created using the wizard but I cant seem to connect to it when I test the connection using PHP


Hi @speed_daemon,
While normally we do not provide support to code, your answer seems to potentially have the wrong host information. If the database is hosted on the same service most people can use
$db_host="localhost";

Best regards,

Michael A. - GoDaddy | Employee
24/7 support available at x.co/247support