Description:
We've found that executing a multi-thread application using MySql Embedded, where multiple threads execute queries on the same table, after a while causes table corruption.
How to repeat:
We've written a test program using Visual C++ 6; we'll attach all the files to this post; however here is the main code; as you can see, there is a number of threads executing INSERT queries on the table, a number of threads executing DELETE queries on the same table, and the main thread executing UPDATE queries on the same table.
After the program runs for a while, the table gets corrupted and the queries fail.
---- begin -----------------------------------------------------------------
#include "stdafx.h"
// This function creates the database and the test table.
void createDatabaseAndTable(void);
// This function connects to the database.
MYSQL* dbConnect(void);
// This function terminates the program, priting an error message.
void die(const char* errorMsg, const char* mysqlErrorMsg = NULL);
// How many threads per type are created.
#define threadCount 50
// Handles of threads.
HANDLE inserterThreadList[threadCount], removerThreadList[threadCount];
// Main procedure of the inserter threads.
DWORD WINAPI inserterThreadProc(LPVOID lpParameter);
// Main procedure of the remover threads.
DWORD WINAPI removerThreadProc(LPVOID lpParameter);
int main(int argc, char* argv[])
{
printf("Starting test program\n");
// Initializes the server.
if (mysql_server_init(0, NULL, NULL))
die("mysql_server_init error");
printf("mysql_server_init OK\n");
// Creates the dabase and the table.
createDatabaseAndTable();
printf("Database and table created.\n");
// Creates the remover threads.
for (int i = 0; i < threadCount; i++)
removerThreadList[i] = CreateThread(NULL, 0, *removerThreadProc, NULL, 0, NULL);
printf("Remover threads created.\n");
// Creates the inserter threads.
for (i = 0; i < threadCount; i++)
inserterThreadList[i] = CreateThread(NULL, 0, *inserterThreadProc, NULL, 0, NULL);
printf("Inserter threads created.\n");
// Repeatedly changes the value of the "passed" flag into the table.
MYSQL *mySql = dbConnect();
for (;;) {
if (mysql_query(mySql, "UPDATE testTable SET passed = 1 WHERE passed = 0"))
die("UPDATE query error", mysql_error(mySql));
}
mysql_close(mySql);
mysql_thread_end();
// Closes the server.
mysql_server_end();
return 0;
}
DWORD WINAPI inserterThreadProc(LPVOID lpParameter)
{
// Open a connection.
MYSQL *mySql = dbConnect();
// Repeatedly insert rows.
for (;;) {
if (mysql_query(mySql,
"INSERT INTO testTable (name, surname, passed) VALUES (\"Mark\", \"Twain\", 0)"))
die("inserterThread error", mysql_error(mySql));
}
mysql_close(mySql);
mysql_thread_end();
}
DWORD WINAPI removerThreadProc(LPVOID lpParameter)
{
// Open a connection.
MYSQL *mySql = dbConnect();
// Repeatedly insert rows.
for (;;) {
if (mysql_query(mySql,
"DELETE FROM testTable WHERE passed = 1"))
die("inserterThread error", mysql_error(mySql));
}
mysql_close(mySql);
mysql_thread_end();
}
MYSQL* dbConnect(void)
{
MYSQL *mySql = mysql_init(NULL);
if (!mySql)
die("mysql_init error");
if (!mysql_real_connect(mySql, NULL, NULL, NULL, "testDB", 0, NULL, 0))
die("mysql_real_connect error", mysql_error(mySql));
return mySql;
}
void createDatabaseAndTable(void)
{
MYSQL *mySql = mysql_init(NULL);
if (!mySql)
die("mysql_init error");
if (!mysql_real_connect(mySql, NULL, NULL, NULL, NULL, 0, NULL, 0))
die("mysql_real_connect error");
if (mysql_query(mySql, "CREATE DATABASE IF NOT EXISTS testDB"))
die("mysql_query error creating the database.", mysql_error(mySql));
mysql_close(mySql);
mySql = dbConnect();
/*
// Defining the table with an index will arise a "Duplicate entry "Mark" for key 2" error.
if (mysql_query(mySql,
"CREATE TABLE IF NOT EXISTS testTable (\
id INTEGER NOT NULL AUTO_INCREMENT, \
name TEXT NOT NULL, \
surname TEXT NOT NULL, \
passed TINYINT, \
PRIMARY KEY(id), \
INDEX nameIndex (name(255)))" ))
die("mysql_query error creating table.", mysql_error(mySql));
*/
// Defininf the table without indices will arise other types of error.
if (mysql_query(mySql,
"CREATE TABLE IF NOT EXISTS testTable (\
id INTEGER NOT NULL AUTO_INCREMENT, \
name TEXT NOT NULL, \
surname TEXT NOT NULL, \
passed TINYINT, \
PRIMARY KEY(id))" ))
die("mysql_query error creating table.", mysql_error(mySql));
mysql_close(mySql);
}
void die(const char* errorMsg, const char* mysqlErrorMsg)
{
if (mysqlErrorMsg)
printf("### DIE\n%s\n%s\n", errorMsg, mysqlErrorMsg);
else
printf("### DIE\n%s\n", errorMsg);
exit(EXIT_FAILURE);
}
---- end --------------------------------------------------------------------
Description: We've found that executing a multi-thread application using MySql Embedded, where multiple threads execute queries on the same table, after a while causes table corruption. How to repeat: We've written a test program using Visual C++ 6; we'll attach all the files to this post; however here is the main code; as you can see, there is a number of threads executing INSERT queries on the table, a number of threads executing DELETE queries on the same table, and the main thread executing UPDATE queries on the same table. After the program runs for a while, the table gets corrupted and the queries fail. ---- begin ----------------------------------------------------------------- #include "stdafx.h" // This function creates the database and the test table. void createDatabaseAndTable(void); // This function connects to the database. MYSQL* dbConnect(void); // This function terminates the program, priting an error message. void die(const char* errorMsg, const char* mysqlErrorMsg = NULL); // How many threads per type are created. #define threadCount 50 // Handles of threads. HANDLE inserterThreadList[threadCount], removerThreadList[threadCount]; // Main procedure of the inserter threads. DWORD WINAPI inserterThreadProc(LPVOID lpParameter); // Main procedure of the remover threads. DWORD WINAPI removerThreadProc(LPVOID lpParameter); int main(int argc, char* argv[]) { printf("Starting test program\n"); // Initializes the server. if (mysql_server_init(0, NULL, NULL)) die("mysql_server_init error"); printf("mysql_server_init OK\n"); // Creates the dabase and the table. createDatabaseAndTable(); printf("Database and table created.\n"); // Creates the remover threads. for (int i = 0; i < threadCount; i++) removerThreadList[i] = CreateThread(NULL, 0, *removerThreadProc, NULL, 0, NULL); printf("Remover threads created.\n"); // Creates the inserter threads. for (i = 0; i < threadCount; i++) inserterThreadList[i] = CreateThread(NULL, 0, *inserterThreadProc, NULL, 0, NULL); printf("Inserter threads created.\n"); // Repeatedly changes the value of the "passed" flag into the table. MYSQL *mySql = dbConnect(); for (;;) { if (mysql_query(mySql, "UPDATE testTable SET passed = 1 WHERE passed = 0")) die("UPDATE query error", mysql_error(mySql)); } mysql_close(mySql); mysql_thread_end(); // Closes the server. mysql_server_end(); return 0; } DWORD WINAPI inserterThreadProc(LPVOID lpParameter) { // Open a connection. MYSQL *mySql = dbConnect(); // Repeatedly insert rows. for (;;) { if (mysql_query(mySql, "INSERT INTO testTable (name, surname, passed) VALUES (\"Mark\", \"Twain\", 0)")) die("inserterThread error", mysql_error(mySql)); } mysql_close(mySql); mysql_thread_end(); } DWORD WINAPI removerThreadProc(LPVOID lpParameter) { // Open a connection. MYSQL *mySql = dbConnect(); // Repeatedly insert rows. for (;;) { if (mysql_query(mySql, "DELETE FROM testTable WHERE passed = 1")) die("inserterThread error", mysql_error(mySql)); } mysql_close(mySql); mysql_thread_end(); } MYSQL* dbConnect(void) { MYSQL *mySql = mysql_init(NULL); if (!mySql) die("mysql_init error"); if (!mysql_real_connect(mySql, NULL, NULL, NULL, "testDB", 0, NULL, 0)) die("mysql_real_connect error", mysql_error(mySql)); return mySql; } void createDatabaseAndTable(void) { MYSQL *mySql = mysql_init(NULL); if (!mySql) die("mysql_init error"); if (!mysql_real_connect(mySql, NULL, NULL, NULL, NULL, 0, NULL, 0)) die("mysql_real_connect error"); if (mysql_query(mySql, "CREATE DATABASE IF NOT EXISTS testDB")) die("mysql_query error creating the database.", mysql_error(mySql)); mysql_close(mySql); mySql = dbConnect(); /* // Defining the table with an index will arise a "Duplicate entry "Mark" for key 2" error. if (mysql_query(mySql, "CREATE TABLE IF NOT EXISTS testTable (\ id INTEGER NOT NULL AUTO_INCREMENT, \ name TEXT NOT NULL, \ surname TEXT NOT NULL, \ passed TINYINT, \ PRIMARY KEY(id), \ INDEX nameIndex (name(255)))" )) die("mysql_query error creating table.", mysql_error(mySql)); */ // Defininf the table without indices will arise other types of error. if (mysql_query(mySql, "CREATE TABLE IF NOT EXISTS testTable (\ id INTEGER NOT NULL AUTO_INCREMENT, \ name TEXT NOT NULL, \ surname TEXT NOT NULL, \ passed TINYINT, \ PRIMARY KEY(id))" )) die("mysql_query error creating table.", mysql_error(mySql)); mysql_close(mySql); } void die(const char* errorMsg, const char* mysqlErrorMsg) { if (mysqlErrorMsg) printf("### DIE\n%s\n%s\n", errorMsg, mysqlErrorMsg); else printf("### DIE\n%s\n", errorMsg); exit(EXIT_FAILURE); } ---- end --------------------------------------------------------------------