-
Mar 30th, 2021, 02:06 PM
#1
Thread Starter
Junior Member
[RESOLVED] Unable to connect to any of the specified MySQL hosts
Hi Everyone
I am writing visual basic on VS2017
I have an app which needs to connect to a MySql database on our website which we pay someone to host
I cannot connect to this database from my app at all, but I can when in the control panel for the website
I have googled this until my eyes bled and I have triple checked my connection string
I have referenced MySql in the app and I have tried MySqlConnector as well which does not work either (different error language though)
I have created a small test app just to get a connection so the "noise" of all the other code was not a distraction
Here is the code...
Code:
Imports System.Xml
Imports MySql.Data.MySqlClient
Public Class Form1
Private conn As MySqlConnection
Private Sub But1_Click(sender As Object, e As EventArgs) Handles But1.Click
Try
'***THIS LINE WHEN ENABLED RETURNS A SUCCESSFUL PING***
If My.Computer.Network.Ping("103.9.171.67") Then MsgBox("Server pinged successfully.") Else MsgBox("Ping request timed out.")
conn = New MySqlConnection
'connection string to use
Dim ConnString As String = "server=103.9.171.67; uid=appkmgs1_appkmgs; pwd=kelcol2601; database=appkmgs1_test"
'create but DONT open the connection
conn = New MySqlConnection(ConnString)
'check the connection settings to be sure no typos
Debug.Print(conn.Ping) '<-----THIS RETURNS FALSE...SHOULDN'T IT PING OK EVEN WHEN NOT CONNECTED TO DB???
Debug.Print(conn.Database) '<-----THIS RETURNS appkmgs1_test
Debug.Print(conn.DataSource) '<-----THIS RETURNS 103.9.171.67
Debug.Print(conn.ConnectionString) '<-----THIS RETURNS server=103.9.171.67;user id=appkmgs1_appkmgs;password=kelcol2601;database=appkmgs1_test
Debug.Print(conn.ConnectionTimeout) '<-----THIS RETURNS 15
Debug.Print("")
'open the connection
conn.Open() '<-----THIS IS WHERE THE ERROR OCCURS
This is the error...
Code:
UDesign.exe Error: 0 : Unable to connect to any of the specified MySQL hosts.
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll
TARGET SITE : Void Open()
SOURCE : MySql.Data
MESSAGE :
Unable to connect to any of the specified MySQL hosts.
STACK TRACE :
at MySql.Data.MySqlClient.NativeDriver.Open()
at MySql.Data.MySqlClient.Driver.Open()
at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
at MySql.Data.MySqlClient.MySqlPool.GetConnection()
at MySql.Data.MySqlClient.MySqlConnection.Open()
at UDesign.Form1.But1_Click(Object sender, EventArgs e) in C:\Visual Studio 2017 Projects\UDesign\UDesign\Form1.vb:line 29
END ERROR
I have spoken to my hosting company who assure me everything is right at their end, yet my code seems fine to me.
The IP address is listed under Remote MySql in the control panel
The user name is definitely attached to the database and the password is definitely correct for that user
The DB being used here is a temporary one with just one record in it, same for the user and password. They will all be deleted when the problem is solved.
Feel free to try and connect directly yourselves to help me solve this issue
I feel there is some setting or privilege's or firewall issue at the hosting end but do not know enough to point the finger there!
Any help with this would be much appreciated.
Cheers
-
Mar 30th, 2021, 02:30 PM
#2
Re: Unable to connect to any of the specified MySQL hosts
I imagine that the database is only directly accessible from the server itself. It works via the web interface because presumably the web interface you are connecting to is on that same server.
It would be unusual and dangerous to allow remote connections directly into the database from the Internet. That being said, it might be possible to configure it that way, but I wouldn't recommend it. It would be up to you to navigate through your hosted server's control panel to see if this is possible.
The most common setup would be that your app would be configured to talk to a web component on the server, and then that web component interacts with the database.
Good luck.
-
Mar 30th, 2021, 02:43 PM
#3
Re: Unable to connect to any of the specified MySQL hosts
One other possibility is that your server is set up to allow connections in to the database from the Internet, but your ISP (from the device running your VB.NET) application is blocking outbound traffic on the port that MySQL uses for communication. That seems much more unlikely, but I thought I would mention it since it is possible.
-
Mar 31st, 2021, 01:13 PM
#4
Thread Starter
Junior Member
Re: Unable to connect to any of the specified MySQL hosts
OK, thanks optionbase1.
That makes a lot of sense now I think on it. I was trying to cut out a step without understanding the proper methodology.
So what you are saying is that I need to write an app that resides on the website and will extract the required data from the website database after I request it to from my remote app.
What language would that be in? Can it be VB or does it have to be ASP or something?
I have zero experience with this. Can someone point me to some reading material or help me get started?
I would really appreciate it...
Thanks again for your response and help
-
Mar 31st, 2021, 03:24 PM
#5
Re: Unable to connect to any of the specified MySQL hosts
-
Apr 1st, 2021, 01:18 AM
#6
Re: Unable to connect to any of the specified MySQL hosts
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Apr 1st, 2021, 09:58 AM
#7
Re: Unable to connect to any of the specified MySQL hosts
Originally Posted by Aussie
OK, thanks optionbase1.
That makes a lot of sense now I think on it. I was trying to cut out a step without understanding the proper methodology.
So what you are saying is that I need to write an app that resides on the website and will extract the required data from the website database after I request it to from my remote app.
What language would that be in? Can it be VB or does it have to be ASP or something?
I have zero experience with this. Can someone point me to some reading material or help me get started?
I would really appreciate it...
Thanks again for your response and help
That would depend on what your host supports. It could be ASP, ASP.NET, PHP, Java, etc.
-tg
-
Apr 1st, 2021, 11:27 AM
#8
Thread Starter
Junior Member
Re: Unable to connect to any of the specified MySQL hosts
Thanks everyone. I will read up and see how I go.
-
Apr 2nd, 2021, 10:34 AM
#9
Hyperactive Member
Re: Unable to connect to any of the specified MySQL hosts
Is there any chance the port is not the default 3306 and you need to specify it in the connection string?
-
Apr 2nd, 2021, 01:26 PM
#10
Thread Starter
Junior Member
Re: Unable to connect to any of the specified MySQL hosts
No, I tried that. I also tried pooling true and false to no avail.
I have written a PHP file on the server and am using that to get data. This works OK for the moment until I learn how to do it properly!!!
-
Apr 2nd, 2021, 01:52 PM
#11
Re: Unable to connect to any of the specified MySQL hosts
That IS how to do it properly. A right and proper host will only allow their databases to be hit from inside their firewalls... so you need to create a web API of some kind that will run on the server to act as the go-in-between. Your app then makes calls to the web api, which then in turns, makes the calls to the database. The data is returned, the api formats the data so your app can understand it, and returns it. You can return the data as a stream of what ever you like. The two most prevailing forms are XML and JSON, other forms include CSV and binary streams. The nice thing about it is that your app now no longer cares what the database is. It's simply interacting with a web call. It makes a request, it gets data back. As long as the format of the data is what it is expecting, it'll be happy.
-tg
-
Apr 2nd, 2021, 01:55 PM
#12
Hyperactive Member
Re: Unable to connect to any of the specified MySQL hosts
The only other option I have had to work with is when upgrading from an older MySQL version I had to add
But I think the error message indicated this as a problem. Sorry, I have never tried to connect to a database on a website.
-
Apr 3rd, 2021, 10:17 AM
#13
Thread Starter
Junior Member
Re: Unable to connect to any of the specified MySQL hosts
Thanks techgnome.
Yes, I have since discovered this! I just had it in my head that I should be able to retrieve data as a datatable or datarow as with SQL Server. No good reason for thinking that, just tunnel vision.
Anyhow, I have taught myself a little about PHP and web requests etc and it is working fine for me.
I can request data OK, but am struggling a little with INSERT and UPDATE. Can someone point me to some code examples somewhere? Both PHP and HTTP requests?
I'll get there eventually but some extra help would be really appreciated at this point.
Thanks for all of your help with this.
Cheers
-
Apr 3rd, 2021, 09:51 PM
#14
Re: Unable to connect to any of the specified MySQL hosts
Originally Posted by Aussie
I just had it in my head that I should be able to retrieve data as a datatable or datarow as with SQL Server.
You can. The database is irrelevant. It's the location of the database that matters. If you have SQL Server or MySQL or any other database on your local network then you can access it directly securely. If you're trying to access the same database over the internet then doing so directly is a security risk. Regardless of the database, exposing data over the internet is usually done through a web service to enable much more control over who can access what.
-
Apr 8th, 2021, 03:14 PM
#15
Thread Starter
Junior Member
Re: Unable to connect to any of the specified MySQL hosts
Thanks all. This is resolved.
-
Nov 8th, 2022, 02:28 PM
#16
Hyperactive Member
Re: Unable to connect to any of the specified MySQL hosts
Aussie,
I've come across this exact problem today after using SQL Server on our network, the MySQL code examples looked like once reference I could just replace things like SqlConnection, SqlCommand with MySqlConnection, MySqlCommand annoyed that it's proving not to be this simple.
Would you mind detailing what you have set up to achieve this please, I've searched APIs, connectors, web service it's all going over my head at the minute, there seems a lot of different things coming up.
If you'd prefer feel free to private message me, I'd really appreciate your input.
Thank you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|