Can't connect to remote MySQL instance
-
Here's a breakdown explanation of what Sifu gave. It did the trick for me.
-
it is a simple mysql privilege issue . You may give the db user privilege from that external ip see an example query below,
mysql> grant all privileges on yourdb.* to 'db_user'@'CONNECTING - IP' identified by password('ENETR-PASSWORD');
@Sifu Thanks for the response. Thing is that "%" in the permissions table above should capture all incoming IPs, and I actually can connect to my mysql instance from another computer - just not with my Qt application.
I tried your suggestion regardless and set up another rule specifically for my local IP, but it didn't work, either - bummer!
-
@Sifu Thanks for the response. Thing is that "%" in the permissions table above should capture all incoming IPs, and I actually can connect to my mysql instance from another computer - just not with my Qt application.
I tried your suggestion regardless and set up another rule specifically for my local IP, but it didn't work, either - bummer!
@ChrisVT said in Can't connect to remote MySQL instance:
@Sifu Thanks for the response. Thing is that "%" in the permissions table above should capture all incoming IPs, and I actually can connect to my mysql instance from another computer - just not with my Qt application.
I tried your suggestion regardless and set up another rule specifically for my local IP, but it didn't work, either - bummer!
You may be trying to connect from your LAN to an external mysql server IP and , you may be using the lan IP as hostname. You should use your public IP address, you can see it from https://whatismyipaddress.com .
PS: some times you may need to flush the privilage too in mysql prompt.
May be this will help -
@ChrisVT said in Can't connect to remote MySQL instance:
@Sifu Thanks for the response. Thing is that "%" in the permissions table above should capture all incoming IPs, and I actually can connect to my mysql instance from another computer - just not with my Qt application.
I tried your suggestion regardless and set up another rule specifically for my local IP, but it didn't work, either - bummer!
You may be trying to connect from your LAN to an external mysql server IP and , you may be using the lan IP as hostname. You should use your public IP address, you can see it from https://whatismyipaddress.com .
PS: some times you may need to flush the privilage too in mysql prompt.
May be this will help -
@Sifu Will try. But shouldn't the entry with the ip "%" in the access list allow external connection regardless of the IP?
@ChrisVT
I could be wrong [ :) ], but your error looks to me more like a failure to find the IP address/port listening than getting as far as connecting and then getting a "don't like your client address" rejection.Suggestion: if you have permission, stop the MySQL server daemon on the server, and/or use a rubbish IP address for your client to try to connect to. Do you get the same behaviour/error messages or something different?
-
@Sifu Will try. But shouldn't the entry with the ip "%" in the access list allow external connection regardless of the IP?
-
@ChrisVT
I could be wrong [ :) ], but your error looks to me more like a failure to find the IP address/port listening than getting as far as connecting and then getting a "don't like your client address" rejection.Suggestion: if you have permission, stop the MySQL server daemon on the server, and/or use a rubbish IP address for your client to try to connect to. Do you get the same behaviour/error messages or something different?
@JonB That was a good idea to further diagnose the problem - the error messages are different, in fact:
When no MySQL is running: Can't connect to MySQL server on '192.168.100.29' (61)
When MySQL is running: Lost connection to MySQL server at 'reading initial communication packet', system error: 2
The latter takes longer to fail, the former fails without delay.
Does that help to further the analysis?
Thanks,
Chris -
@JonB That was a good idea to further diagnose the problem - the error messages are different, in fact:
When no MySQL is running: Can't connect to MySQL server on '192.168.100.29' (61)
When MySQL is running: Lost connection to MySQL server at 'reading initial communication packet', system error: 2
The latter takes longer to fail, the former fails without delay.
Does that help to further the analysis?
Thanks,
Chris@ChrisVT
It just seems to indicate that your error is not the same as "can't get to IP address/port" after all.I do not know whether @Sifu 's comments help you. But it sounds like you're still stuck.
Are you in charge of the MySQL server? Next thing to try, I think, is (temporarily) replace that
%with the actual external IP?Otherwise I think some judicious Googling! Your error seems to have nothing to do with Qt code. I tried:
mysql reading initial communication packet system error 2and I think there's a lot of food for thought out there....
-
@ChrisVT
It just seems to indicate that your error is not the same as "can't get to IP address/port" after all.I do not know whether @Sifu 's comments help you. But it sounds like you're still stuck.
Are you in charge of the MySQL server? Next thing to try, I think, is (temporarily) replace that
%with the actual external IP?Otherwise I think some judicious Googling! Your error seems to have nothing to do with Qt code. I tried:
mysql reading initial communication packet system error 2and I think there's a lot of food for thought out there....
@JonB I just made a fascinating observation: I deployed the application and installed it on another machine, set up to connect to my development database which runs on my iMac. The connection worked fine which was a surprise to me!
So, from what I can tell, most everything works as intended:
Connecting to the development database using the mysql command
- on the local machine, connecting to localhost/127.0.0.1
- on the local machine, connecting to my local IP (192.168.100.29)
- from a remote machine to my local IP
Connecting to the development database using my application
- on the local machine, connecting to localhost/127.0.0.1
- from a remote machine to my local IP
The one thing that does not work is that I cannot convince my application to connect to a database on the local machine, connecting to my local IP (192.168.100.29).
No idea why that is. I'm still thinking that Qt in this particular configuration messes with something that makes mysqld reject the connection.
I think at this stage, while I'd really like to understand the issue and resolve it, I will just associate my development database with 127.0.0.1 and that will do.
Oh, and of course I will minimize my mysql privileges!
-
Hi,
Is the port of your database opened on your local machine ?
-
I may have misunderstood the case that doesn’t work.
Can you maybe do a small picture with the setup ?
