Topics that have MySQL connector code in C ++ do not end there

Question

On the XMLRPC server I'm working on (based on xmlrpc-c), threads may want to connect to MySQL to get some data using the following function:

Distribution getEntitySetFromMysql( int id ) { Distribution result; try { sql::Driver *driver = get_driver_instance(); sql::Connection *con = driver->connect( (std::string)DBHOST, (std::string)USER, (std::string)PASSWORD); con->setSchema( (std::string)DATABASE ); sql::Statement *stmt = con->createStatement(); std::stringstream query; query << "SELECT concept_id, weight FROM entity_set_lines WHERE entity_set_id = " << id; sql::ResultSet *res = stmt->executeQuery ( query.str() ); while (res->next()) { result[ res->getInt("concept_id") ] = res->getDouble("weight"); } delete res; delete stmt; con->close(); delete con; } catch (sql::SQLException &e) { std::cout << "ERROR: SQLException in " << __FILE__; std::cout << " (" << __func__<< ") on line " << __LINE__ << std::endl; std::cout << "ERROR: " << e.what(); std::cout << " (MySQL error code: " << e.getErrorCode(); std::cout << ", SQLState: " << e.getSQLState() << ")" << std::endl; if (e.getErrorCode() == 1047) { std::cout << "\nYour server does not seem to support Prepared Statements at all. "; std::cout << "Perhaps MYSQL < 4.1?" << std::endl; } } catch (std::runtime_error &e) { std::cout << "ERROR: runtime_error in " << __FILE__; std::cout << " (" << __func__ << ") on line " << __LINE__ << std::endl; std::cout << "ERROR: " << e.what() << std::endl; } return result; } 

Everything works fine, but after the thread runs this code and successfully returns its result, the thread remains hanging and does not exit. What is wrong with this approach? How fundamentally wrong is this? Is MySQL Connector Stream Safe?

Answer 1

While searching for solutions to solve problems, I came across references to sql::Driver::threadInit() and sql::Driver::threadEnd() . However, since I was in version 1.0.5 of the C ++ Connector, these functions were not available to me. By adding driver->threadInit(); after receiving the driver instance and driver->threadEnd(); At the end of my function, this problem was resolved.

The following is a mention of this initialization function and the end of the thread in MySQL 1.1.0 Change History :

Added methods Driver :: threadInit () and Driver :: threadEnd (). each thread client thread must call Driver :: threadInit () at the very beginning of the thread before it does anything else with the / C ++ connector and each thread must call Driver :: threadEnd () when it ends. You can find an example demonstrating the use of /pthreads.cpp in the examples. It is highly recommended to share connections between threads. this is theoretically possible if you install specific (undocumented) mutexes, but it is not supported at all. Use one connection per thread. Do not have two threads using the same connection at the same time. please check the C API notes for streaming processing in the MySQL manual. Connectors / C ++ wrapping C API. (Lawrin, Andrey, Ulf)

TL DR: if you run into this problem, make sure your C ++ version of MySQL Connector> = 1.1.0 and use the sql::Driver::threadInit() and sql::Driver::threadEnd() to surround your connection code.

Answer 2

Two thoughts:

• libmysql is not completely thread safe .
• As your code is structured, you will leak memory if an exception occurs. you might be better off declaring variables outside of try / catch and using finally (or the local equivalent) to ensure that you clean up correctly or use smart pointers (if available).

Since you are not showing any of the calling or surrounding code, it is difficult to say what is actually happening. Do you check the stream exit code when it was allegedly made? Can you pin it in the debugger to see what it does, and not close it?

Answer 3

Actually:

DO NOT USE: sql::Driver::threadInit() and sql::Driver::threadEnd()

BECAUSE: you are already using try()

YOU FORGOT:

 res->close(); stmt->close(); con->close(); delete res; delete stmt; delete con; 

EXAMPLE:

 int connection_and_query_func() { /*connection and query variables*/ sql::Driver *driver; sql::Connection *con; sql::Statement *stmt; sql::ResultSet *res; int err_exception_getErrorCode=0; /*results variables*/ int my_int_from_column_1 = 0; double my_double_from_column_2 = 0; .... std:string my_string_from_column_p = ""; try { /* Create a connection */ driver = get_driver_instance(); con = driver->connect("address_name", "user_name", "password"); /* Connect to the MySQL database */ con->setSchema("schema_name"); /* Execute MySQL Query*/ stmt = con->createStatement(); res = stmt->executeQuery("your query statement here"); /* Read MySQL Query results per column*/ my_int_from_column_1 = res->getInt(1); my_double_from_column_2 = res->getDouble(2); .... my_string_from_column_p = res->getString(p); /* Close MySQL Connection*/ res->close(); stmt->close(); con->close(); delete res; delete stmt; delete con; }; /* Get last error*/ catch (sql::SQLException &exception) { err_exception_getErrorCode = exception.getErrorCode(); }; return(0); }; 

CONCLUSION: this can be done as many times as you want. An example function (connection_and_query_func ()) will close the MySQL connection properly after it is completed - without adding processes to your MySQL server !!!

FURTHERMORE: read the official guide https://docs.oracle.com/cd/E17952_01/connector-cpp-en/connector-cpp-en.pdf

ALTERNATIVE: if you cannot properly close your connection and request a program / function from your side (thus compiling processes to your MySQL server), consider the following two options:

1 / Set all MySQL timeout parameters to 10 seconds. or less (for example); 2 / write a script that says SHOW PROCESSLIST and delete processes that are in SLEEP for too long.

Greetings.