Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Can't connect to remote MySQL instance
QtWS25 Last Chance

Can't connect to remote MySQL instance

Scheduled Pinned Locked Moved Unsolved General and Desktop
mysqlconnectremoteqt5.10lost connection
14 Posts 5 Posters 4.8k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • C Offline
    C Offline
    ChrisVT
    wrote on 19 Jun 2018, 12:29 last edited by
    #1

    I'm trying to connect to a remote mysql instance. Working with MySQL 5.7.22 and Qt5.10 on a Mac. I've currently set up MySQL on my local machine for testing but want to to move the database to the production environment on a dedicated server.

    First, some good news (stuff that works):

    The MySQL server is set up with users and passwords that allow me to connect to the remote server (mysql -u some_user -p -h remote_ip). After logging in, I can also access the databases that I'm interested in. That means, I don't believe that I messed up the privileges, firewall, bind_address, or the like.

    User table:
    +---------------+---------------+-------------------------------------------+
    | user | host | authentication_string |
    +---------------+---------------+-------------------------------------------+
    ...
    | some_user | % | *XXX |
    | some_user | localhost | *XXX |
    +---------------+---------------+-------------------------------------------+

    ~# cat /etc/my.cnf
    [mysqld]
    max_allowed_packet=67000000
    wait_timeout=300
    bind_address=*
    skip_name_resolve

    My Qt application can connect to the database if I connect to 127.0.0.1, so I don't believe I have issues with the drivers and such.

    What doesn't work is if I try and connect to the same server per its external IP, the connection fails:

    Lost connection to MySQL server at 'reading initial communication packet', system error: 2

    It takes some time (a few seconds) for the connection to fail, so I'm assuming this is some kind of timeout that's happening.

    The mysql error log (verbosity 3) is silent about the issue.

    Any ideas?

    Thanks,
    Chris

    1 Reply Last reply
    0
    • S Offline
      S Offline
      Sifu
      wrote on 19 Jun 2018, 16:48 last edited by
      #2

      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');

      C 1 Reply Last reply 20 Jun 2018, 05:48
      0
      • ? Offline
        ? Offline
        A Former User
        wrote on 19 Jun 2018, 16:52 last edited by
        #3

        Here's a breakdown explanation of what Sifu gave. It did the trick for me.

        1 Reply Last reply
        0
        • S Sifu
          19 Jun 2018, 16:48

          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');

          C Offline
          C Offline
          ChrisVT
          wrote on 20 Jun 2018, 05:48 last edited by
          #4

          @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!

          S 1 Reply Last reply 20 Jun 2018, 16:09
          0
          • C ChrisVT
            20 Jun 2018, 05:48

            @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!

            S Offline
            S Offline
            Sifu
            wrote on 20 Jun 2018, 16:09 last edited by
            #5

            @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

            C 1 Reply Last reply 20 Jun 2018, 23:56
            2
            • S Sifu
              20 Jun 2018, 16:09

              @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

              C Offline
              C Offline
              ChrisVT
              wrote on 20 Jun 2018, 23:56 last edited by
              #6

              @Sifu Will try. But shouldn't the entry with the ip "%" in the access list allow external connection regardless of the IP?

              J S 2 Replies Last reply 21 Jun 2018, 06:02
              0
              • C ChrisVT
                20 Jun 2018, 23:56

                @Sifu Will try. But shouldn't the entry with the ip "%" in the access list allow external connection regardless of the IP?

                J Offline
                J Offline
                JonB
                wrote on 21 Jun 2018, 06:02 last edited by JonB
                #7

                @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?

                C 1 Reply Last reply 22 Jun 2018, 04:50
                1
                • C ChrisVT
                  20 Jun 2018, 23:56

                  @Sifu Will try. But shouldn't the entry with the ip "%" in the access list allow external connection regardless of the IP?

                  S Offline
                  S Offline
                  Sifu
                  wrote on 21 Jun 2018, 14:15 last edited by
                  #8

                  @ChrisVT

                  Yes you are right . Never use % in a production server.

                  1 Reply Last reply
                  1
                  • J JonB
                    21 Jun 2018, 06:02

                    @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?

                    C Offline
                    C Offline
                    ChrisVT
                    wrote on 22 Jun 2018, 04:50 last edited by
                    #9

                    @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

                    J 1 Reply Last reply 22 Jun 2018, 06:00
                    0
                    • C ChrisVT
                      22 Jun 2018, 04:50

                      @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

                      J Offline
                      J Offline
                      JonB
                      wrote on 22 Jun 2018, 06:00 last edited by
                      #10

                      @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 2
                      

                      and I think there's a lot of food for thought out there....

                      C 1 Reply Last reply 22 Jun 2018, 12:56
                      1
                      • J JonB
                        22 Jun 2018, 06:00

                        @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 2
                        

                        and I think there's a lot of food for thought out there....

                        C Offline
                        C Offline
                        ChrisVT
                        wrote on 22 Jun 2018, 12:56 last edited by
                        #11

                        @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!

                        1 Reply Last reply
                        1
                        • SGaistS Offline
                          SGaistS Offline
                          SGaist
                          Lifetime Qt Champion
                          wrote on 22 Jun 2018, 21:48 last edited by
                          #12

                          Hi,

                          Is the port of your database opened on your local machine ?

                          Interested in AI ? www.idiap.ch
                          Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                          C 1 Reply Last reply 22 Jun 2018, 21:56
                          0
                          • SGaistS SGaist
                            22 Jun 2018, 21:48

                            Hi,

                            Is the port of your database opened on your local machine ?

                            C Offline
                            C Offline
                            ChrisVT
                            wrote on 22 Jun 2018, 21:56 last edited by
                            #13

                            @SGaist I can connect from a different computer - so yeah.

                            1 Reply Last reply
                            0
                            • SGaistS Offline
                              SGaistS Offline
                              SGaist
                              Lifetime Qt Champion
                              wrote on 23 Jun 2018, 15:00 last edited by
                              #14

                              I may have misunderstood the case that doesn’t work.

                              Can you maybe do a small picture with the setup ?

                              Interested in AI ? www.idiap.ch
                              Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                              1 Reply Last reply
                              0

                              1/14

                              19 Jun 2018, 12:29

                              • Login

                              • Login or register to search.
                              1 out of 14
                              • First post
                                1/14
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved