How to prevent open-file too much in c++ using MySQL connector

Question

It show error code : Can't create socket(24) , after I survey I know that is reach the open_files_limit,I checked the show global variables like 'open%'; in MySQL and value is 5000000,so my code must some problem in it.

here's my simple code:

class DB {
    public:
    double query1();
    double query2();
    double query3();
};

main() {
    DB handler;
    for(int i=0;i<100000;i++) {
        handler.query1();
        handler.query2();
        handler.query3();
    }
}

I wrote a class handle the 3 query and run it in the loop, how can I prevent open-file limit problem in this class

here's query code :

double query1(string pair) { 
    double get_prob;
    try {
        /* Create a connection */
        driver = get_driver_instance();
        con = driver->connect("localhost", "root", "nlpgroup");
        /* Connect to the MySQL test database */
        con->setSchema("em_im");
        stmt = con->createStatement();
        stringstream stmvar;
        stmvar << "select prob from em where pair='" << pair << "'";
        string stmvarstr = stmvar.str();
        cout << stmvarstr << endl;
        res = stmt->executeQuery(stmvarstr); // replace with your statement
        while (res->next()) {
            get_prob = atof(res->getString(1).c_str());
        }
        res->close();
        stmt->close();
        con->close();

        delete res;
        delete stmt;
        delete con;
    } catch (sql::SQLException &e) {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
        cout << "# ERR: " << e.what();
        cout << " (MySQL error code: " << e.getErrorCode();
        cout << ", SQLState: " << e.getSQLState() << " )" << endl;
    }
    return get_prob;
}

Have a look at how to write a [mcve](http://stackoverflow.com/help/mcve) (tip: write it in your IDE until it compiles and copy-paste the code here then). Keep an eye on correct formatting, too - I did it this time for you, you might want to compare the versions to see how to do better. Prefer not using tabs for indentation here, I continue seeing indentation problems doing so... — Aconcagua, Jun 13 '16 at 06:42
Be aware that you are not initializing get_prob, if you run into an exception - *or* if the result set is empty. You'd be returning an unitialized value then, and if using it, you run into undefined behaviour! — Aconcagua, Jun 13 '16 at 08:11

score 1 · Accepted Answer · edited May 23 '17 at 10:29

show global variables like 'open%'; in MySQL

Apart from MySQL, your OS might impose limits, too. For linux, have a look at /etc/security/limits.conf, on Windows, this answer might help you out.

However, if you need one and the same connection that often one after another time, it might be a better choice to open it once and keep it open until your program terminates. This will additionally give you better performance - and you can improve performance even more using a prepared statement instead. I added this to the example below already...

class DB
{
    std::unique_ptr <sql::Connection> con;
    std::unique_ptr <sql::PreparedStatement> stmt;
public:
    DB();
    double query1(std::string const& pair);
};

DB::DB()
    : con(get_driver_instance()->connect("localhost", "root", "nlpgroup"))
{
    con->setSchema("em_im");
    // you might prefer a prepared statement
    stmt.reset(con->prepareStatement("SELECT prob FROM em WHERE pair = ?"));
}

double DB::query1(std::string const& pair)
{
    double get_prob = 0.0;
    try
    {
        stmt->setString(1, pair);
        std::unique_ptr < sql::ResultSet > res(stmt->execute());
        while (res->next())
        {
            get_prob = atof(res->getString(1).c_str());
        }
    }
    catch(sql::SQLException& e)
    {
        /* ... */
    }
    return get_prob;
}

Usage of std::unique_ptr assures that all objects are deleted correctly even in case of an exception - which, by the way, your code did not. I did not call close explicitely - it will be called in the objects' destructors anyway, so this is fine.

Be aware that now the constructor can throw an exception, too, so you need a try - catch in the main function, too. Depending on your needs, you then could leave out the try - catch in the query functions. This changes behaviour, however: Leaving as is results in all the queries being executed, even if one fails in between, whereas dropping it results in aborting the loop.

If query1() is not complete yet then running query2() will occur some error?? or It will run query2() until query1() over? — yihang hwang, Jun 13 '16 at 07:50
If you run query1 as above and anything fails with exception, the exception is caught, and for query2 everything starts right from the beginning again (except for connecting and creating statement, of course, if implemented analogously). It is not unlikely that the same exception occurs again in query2 - but not necessarily. It depends on your specific requirements, if more appropriate to continue with query2, even if query1 failed, or if not making any sense (in the latter case, you won't catch the exception in the queries, but only in the main function, where the loop will be exited then). — Aconcagua, Jun 13 '16 at 08:07
one more question, how can I using c_stmt = con->createStatement(); with unique_ptr c_stmt; declare ,It will happen mismatched types ‘std::unique_ptr<_Tp, _Dp>’ and ‘sql::Statement*’ — yihang hwang, Jun 13 '16 at 13:38
Problem is [`std::unique_ptr::operator=`](http://www.cplusplus.com/reference/memory/unique_ptr/operator=/) accepting only another std::unique_ptr (and nullptr). Use reset() instead - see my code example, the constructor. — Aconcagua, Jun 13 '16 at 18:34

How to prevent open-file too much in c++ using MySQL connector

1 Answers1