mysql remote connection - mysql CL client works, C++ app doesn't

Question

Strange problem here. I have a C++ application that I've written using the MySQL C library. It does the following:

• connects to a remote MySQL server
• retrieve some data
• analyze data
• push the results back to DB server
• close db

The MySQL server is v5.1.61 on CentOS 6 The client runs fine from the command line on various linux distros - CentOS 6, Ubuntu, Scientific Linux 6.4

It works fine on all of these machines, except one specific Scientific Linux 6.3 box. Which happens to be the cluster head node - ie. where I need to go to get to the real horsepower!

When I try to run it here I get the following:

2003    :       Can't connect to MySQL server on 'hostname' (111)

AFAIK, this typically indicates a permission or networking issue.

However, I can connect to the server via the mysql command line client with the identical credentials.

What I've tried:

• Connected to server from the problem machine with command line mysql client. Rules out permission issues or bad credentials.
• recompiled the the C++ exe (g++ 4.4.7) on this box to make sure it isn't some sort of library incompatibility. No change.
• I've tried this with both the server host name and IP address with the same behavior. Rules out name resolution problem.
• Tried connecting to a different MySQL server with both my exe and the CL client - same behavior. Indicates a client issue not the specific mysql server

The C++ code uses mysql_real_connect() from the mysql C library. I've used this same connection code for years in many different programs and many different databases - it works. Until now.

int MakeDbConnect(MYSQL **db, const string &dbserver, const string &dbname,
                  const string &dbuser, const string &dbpasswd)
{
   *db = mysql_init(NULL);

   if (mysql_real_connect(*db, dbserver.c_str(), dbuser.c_str(),
                          dbpasswd.c_str(), dbname.c_str(), 0, NULL, 0) == NULL)
   {
      cout << "ERROR: Call to mysql_real_connect() failed:\t"
           << mysql_errno(*db) << "\t:\t" << mysql_error(*db) << endl;
      cout << "\tServer:  \t" << dbserver << endl;
      cout << "\tDatabase:\t" << dbname << endl;
      cout << "\tUser:    \t" << dbuser << endl;

      return -1;
   }

   return 0;
}

So again,

• this executable works fine from several other linux boxes.
• It doesn't connect from one particular box
• The linux command line mysql client can connect from this same box using identical credentials

All of these systems are inside our corporate firewall and run by the same group. So they should all have a very similar configuration.

Any ideas? Thanks

Answer 1

Well, I had similar problem like CoAstroGeek.

I could connect to MySQL database in localhost but NOT in a remote host as I did not specify the port explicitly. When I specified _iPort = 3306, it worked! Thanks to CoAstroGeek and Alexis!

bool DBConnectorMySQL::connect(bool bDebug)
{
    _pMySQL_Conn = mysql_init(NULL);
    my_bool myb = true;
    mysql_options(_pMySQL_Conn, MYSQL_OPT_RECONNECT, &myb);
    if (_bEnableSetEncodingUTF8)
    {
        mysql_options(_pMySQL_Conn, MYSQL_SET_CHARSET_NAME, "utf8");
        mysql_options(_pMySQL_Conn, MYSQL_INIT_COMMAND, "SET NAMES `utf8`");
    }
    if (mysql_real_connect(_pMySQL_Conn, _sHost.c_str(), _sUsername.c_str(),
            _sPwd.c_str(), _sDatabase.c_str(), _iPort, NULL, 0) == NULL)
    {
        if (bDebug) cerr << "Problem encountered connecting to the "
                << _sDatabase << " database on " << _sHost << endl;
        cout << "mysql_error: #" << mysql_errno(_pMySQL_Conn) << " - "
                << mysql_error(_pMySQL_Conn) << endl;
        _pErrorHandler->setErrorCode((int)DBCONNECTORMYSQL_CANNOT_CONNECT_DB);
        return false;
    }
    else
    {
        if (bDebug) cout << "Connected to the " << _sDatabase << " database on "
                << _sHost << " as user " << _sUsername << endl;
        return true;
    }
}

Answer 2

Ok, Alexis Wilke got it - kind of. It is a port issue, though I'm still not sure how this is happening. The 6th argument to mysql_real_connect() is the port number. 0 indicates that the default port (3306) should be used: http://dev.mysql.com/doc/refman/5.7/en/mysql-real-connect.html

If port is not 0, the value is used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.

The default port is 3306. If I put 3306 in there explicitly, the connection works. No idea why it's not defaulting to that port on this box or what port it's trying to use. Again, this same executable works on several other boxes of very similar configuration with 0 in the 6th argument. And I've been using that same connection code for years and have never seen this before. Very odd.

Maybe a bug in this version of the library? Though that seems pretty unlikely for something this big and unsubtle.