Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5952

Many CONNECT table types do not work if server uses skip-networking or bind-address

Details

    Description

      CONNECT table types that use PROXY do not work if the server is running with skip-networking or bind-address set to something other than the loopback interface.

      With bind-address set (to something other than 127.0.0.1):

      mysql 10.0.10-MariaDB (root) [test]> select * from fruit_pivot;
      		 
      ERROR 1296 (HY000): Got error 174 '(2003) Can't connect to MySQL server on 'localhost' (61 "Connection refused")' from CONNECT

      With skip-networking:

      mysql 10.0.10-MariaDB (root) [test]> select * from fruit_pivot;
      		 
      ERROR 1296 (HY000): Got error 174 '(2003) Can't connect to MySQL server on 'localhost' (49 "Can't assign requested address")' from CONNECT

      It would be neat if the CONNECT engine tried by default to connect using the socket file. It would also be good if there was some way to specify an IP to use, I guess, in the case that the server is running with bind-address set.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bertrandop Olivier Bertrand added a comment -

          In all PROXY based tables, you can specify the connection parameters (as for MYSQL tables)
          For instance, the default host is 'localhost' but can be changed to an IP address. You can alternatively use an URL or even give the name of a pre-defined connection server.
          Perhaps this enables you to solve your probem.

          bertrandop Olivier Bertrand added a comment - In all PROXY based tables, you can specify the connection parameters (as for MYSQL tables) For instance, the default host is 'localhost' but can be changed to an IP address. You can alternatively use an URL or even give the name of a pre-defined connection server. Perhaps this enables you to solve your probem.
          kolbe Kolbe Kegel (Inactive) added a comment -

          Olivier, yeah, adding "host" fixes it for the bind-address issue, but if you ever try binding mysqld to another interface or the IP address changes or something, all of your CONNECT/MYSQL tables will stop working! Maybe just something to document somewhere.

          And I think you can't work around the skip-networking issue unless CONNECT gets support for socket file connections.

          kolbe Kolbe Kegel (Inactive) added a comment - Olivier, yeah, adding "host" fixes it for the bind-address issue, but if you ever try binding mysqld to another interface or the IP address changes or something, all of your CONNECT/MYSQL tables will stop working! Maybe just something to document somewhere. And I think you can't work around the skip-networking issue unless CONNECT gets support for socket file connections.
          bertrandop Olivier Bertrand added a comment -

          I presume these problems also occur with FEDERATED. Did you try it? If they have a solution, I 'll look at it.

          bertrandop Olivier Bertrand added a comment - I presume these problems also occur with FEDERATED. Did you try it? If they have a solution, I 'll look at it.
          kolbe Kolbe Kegel (Inactive) added a comment -

          It seems only logical that Federated has a similar problem, but that is only logical since for every Federated table you must specify some remote name. Many Connect table types rely on implicit "localhost" tables and it's quite confusing in the first place for that to involve TCP. In other words, Federated tables pointing back to the same server must be much less common than Connect tables pointing to the same server.

          kolbe Kolbe Kegel (Inactive) added a comment - It seems only logical that Federated has a similar problem, but that is only logical since for every Federated table you must specify some remote name. Many Connect table types rely on implicit "localhost" tables and it's quite confusing in the first place for that to involve TCP. In other words, Federated tables pointing back to the same server must be much less common than Connect tables pointing to the same server.
          bertrandop Olivier Bertrand added a comment -

          Here is what I have done and pushed:

          • On Linux, connection specifying "localhost" (the default) will now use Unix socket.
          • On Windows, the default for local connection is to use shared memory. However, not all servers enable this and then TCP is used by default. What can be done then is to specify the host as "." and the connection will use named pipe.

          Kolbe, can you test that this works in all cases so I can close this issue? Thanks.

          When creating a proxy based table, CONNECT can test if the table point on itself. However, testing pointing back to the same table when several tables are chained cannot be done if one of the table in the chain is not a CONNECT table.

          bertrandop Olivier Bertrand added a comment - Here is what I have done and pushed: On Linux, connection specifying "localhost" (the default) will now use Unix socket. On Windows, the default for local connection is to use shared memory. However, not all servers enable this and then TCP is used by default. What can be done then is to specify the host as "." and the connection will use named pipe. Kolbe, can you test that this works in all cases so I can close this issue? Thanks. When creating a proxy based table, CONNECT can test if the table point on itself. However, testing pointing back to the same table when several tables are chained cannot be done if one of the table in the chain is not a CONNECT table.
          kolbe Kolbe Kegel (Inactive) added a comment -

          I can try to test this, but not right away. I have been building MariaDB 10 on OS X, I will need to set up a Linux build environment. I won't attempt to build or test on Windows.

          kolbe Kolbe Kegel (Inactive) added a comment - I can try to test this, but not right away. I have been building MariaDB 10 on OS X, I will need to set up a Linux build environment. I won't attempt to build or test on Windows.
          bertrandop Olivier Bertrand added a comment -

          I did test on Windows. On Linux, I just test on ubuntu with --skip-networking.
          Maybe that's enough.

          bertrandop Olivier Bertrand added a comment - I did test on Windows. On Linux, I just test on ubuntu with --skip-networking. Maybe that's enough.
          bertrandop Olivier Bertrand added a comment -

          Here is what is done now for the MYSQL and PROXY (via MySQL) table types:
          When the host is specified as “localhost”, the connection is established on Linux using Linux socket.
          On Windows, the connection is established by default using share memory if it is enabled. If not the TCP protocol is used. An alternative is to specify the host as “.” to use a named pipe connection (if it is enabled)
          This makes possible to use these table types with server skipping networking.

          bertrandop Olivier Bertrand added a comment - Here is what is done now for the MYSQL and PROXY (via MySQL) table types: When the host is specified as “localhost”, the connection is established on Linux using Linux socket. On Windows, the connection is established by default using share memory if it is enabled. If not the TCP protocol is used. An alternative is to specify the host as “.” to use a named pipe connection (if it is enabled) This makes possible to use these table types with server skipping networking.

          People

            bertrandop Olivier Bertrand
            kolbe Kolbe Kegel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.