Testing with MySQL embedded
by Roberto Polli
Integration Testing is not always common between C developers. MySQL API offer us a fast and
pratical way to do it using a full-featured, dummy sql embedded server.
Obviously an embedded server has its limitations, eg:

no replication;

one process at a time;

missing more logic (eg. mysqldump...) but you can implement it.
See mysql embedded documentation for further info!
The MySQL C API
To access a MySQL server from a C program, we can use the API provided by MySQL. Functions are
provided by the following library files.
Client:

/usr/lib/libmysqlclient.so.16.0.0
- standard version
/usr/lib/libmysqlclient_r.so.16.0.0
- reentrant version
Server embedded:

/usr/lib/mysql/libmysqld.a
You can use /usr/bin/mysql_config to set the build flags. That’s the standard output of
#mysql_config
Usage: /usr/bin/mysql_config [OPTIONS]
Options:
--cflags
aliasing
[-I/usr/include/mysql
-DBIG_JOINS=1
-fno-strict-
-DUNIV_LINUX -DUNIV_LINUX]
--include
--libs
[-I/usr/include/mysql]
[-Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -
lmysqlclient]
--libs_r
[-Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql -
lmysqlclient_r]
--plugindir
[/usr/lib/mysql/plugin]
--socket
[/var/run/mysqld/mysqld.sock]
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
--port
[0]
--version
[5.1.41]
--libmysqld-libs [-Wl,-Bsymbolic-functions -rdynamic -L/usr/lib/mysql lmysqld -ldl -lwrap -lrt]
Here is the anatomy of a MySQL client application: uses a shared library and the external server. My
test application occupy about 200KBytes.
An embedded app is bigger, because it’s statically linked with the mysqld library. It’s an almost
complete MySQL server instance! And it’s faster!
Basic working cycle with a MySQL Server
The simplest way to connect to MySQL consists to:
- initialize mysql library
- connect to server
- execute queries, eventually freeing result sets
- disconnect
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
Here is a sample program with some assertions, that show us the expected return values of the
functions.
#include<assert.h>
#include<stdlib.h>
#include<mysql/mysql.h>
int main(int argc, char**argv) {
int port = 0,
mysql_option_flags=0;
char *host = “localhost”,
*user = “mysql”,
*pass = “secret”,
*dbname = NULL,
*unix_sock = NULL;
// Initialize mysql library. This should be done *once*
// moreover this function is not thread-safe, nor reentrant
// so we should protect it with a global mutex
assert( mysql_library_init(0, NULL, NULL) == 0 );
MYSQL * mysql = NULL; // a pointer to a mysql database structure
//
Inizialize
mysql
database
structure,
containing
all
database
informations
// this function is not thread-safe too
assert(
(mysql = mysql_init(NULL))
!= NULL);
// connect to the RDBMS without specifying a database name
int error = mysql_real_connect(mysql,
host, user, pass, dbname, port,
unix_sock,
mysql_option_flags);
// eventually print errors
if (error) goto onerror;
// do our job
error = mysql_query(mysql, “CREATE DATABASE pluto;”);
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
if (error) goto onerror;
error = mysql_query(mysql, “USE pluto;”);
if (error) goto onerror;
error = mysql_query(mysql, “DROP DATABASE pluto;”);
if (error) goto onerror;
// disconnet and free memory and resources
mysql_close(mysql);
mysql_library_end();
exit(0);
onerror:
printf(“Error: %s\n”, mysql_error(mysql));
abort();
}
Going multithreads
Using multiple threads - and multiple connections - require some logic. In fact the following
functions must be called at once in the process - and must be protected by a global mutex:
1. mysql_library_init()
2. mysql_init() which may call mysql_library_init()
Other MySQL methods are thread safe “per-connection”: the MYSQL* pointer is associated to a
reference the connection. So every MYSQL* pointer can be used by a single thread at once,
requiring us to do use some locking. Let’s create the following database structure, a global mutex
and some helper methods.
// a locking structure for each connection
typedef struct my_db {
MYSQL * mysql;
pthread_mutex_t lock;
int lock_init;
char* user;
char* pass;
char* host;
char* dbhost;
char* unix_sock;
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
int port;
int options;
} mysql_db;
// a mutex for global calls
pthread_mutex_t db_global_mutex = PTHREAD_MUTEX_INITIALIZER;
The structure simply ties the database connection parameters, its MYSQL structure and a mutex that
makes it thread safe.
The “global mutex” avoid us to call mysql_init() in different threads.
Here are some methods, the missing ones are left as an exercise to the reader. We will add a
my_flush_result() method, *required* to clean resultset after queries.
Please see MySQL documentation for further info about it!.
Non connection-safe functions
There are two ways to protect the non thread-safe functions:

call them in the main thread, before spawning the others;

or use our global mutex.
The mysql_library_init() should be called once by EVERY thread using mysql - so it’s better to run it in
the main(), before spawning threads. Similarly you have to run mysq_library_end() at the end of the
process to free memory resources.
In the same way, we can allocate all our connection before running the client threads - creating a
connection pool and passing it to our threads.
/**
* creating a connection pool
*/
int main(int argc, char** argv) {
// some variables
int i=0, pool_size = 5;
mysql_db my_pool[pool_size];
// once in the process, so no mutexes
mysql_library_init(0, NULL, NULL);
// again...no locks
for (i=0; i<pool_size; i++) {
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
// fill the my_pool with credential, user, passwd....
...initialize_db_structure(&my_pool[i]);...
// initialize lock
pthread_mutex_init(&my_pool[i].lock);
&my_pool[i]->lock_initialized = TRUE;
//initialize db
&my_pool[i]->mysql = mysql_init(NULL);
}
… pass &my_pool[i] to threads and run them...
mysql_library_end();
}
Otherwise we just have to protect each call with our global mutex and run our sequence at every
connection...
mysql_db* my_init(mysql_db *db) {
/// initialize mutex, WARN can’t initialize it twice,
///
so check if you have already initialized it!
if (!db->lock_initialized) {
pthread_mutex_init(&db->lock, NULL);
db->lock_initialized=TRUE;
}
/// initialize mysql structure
pthread_mutex_lock(&db->lock); // don’t use this db while initializing
pthread_mutex_lock(&db_global_mutex);
//
don’t
initialize
two
databases
contemporarily
db->mysql = mysql_init(NULL);
pthread_mutex_unlock(&db_global_mutex);
pthread_mutex_unlock(&db->lock);
return db;
}
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
Connection-safe functions
Connection safe functions are now easy: just lock and unlock the MYSQL* pointer in use, to have
consistent values for results, counters and errors.
int my_connect(mysql_db *db) {
pthread_mutex_lock(&db->lock);
int error = mysql_real_connect(db->mysql,
db->host, db->user, db->pass, db->dbname, db->port,
db->unix_sock,
db->options);
if (error) {
printf(“Error: %s\n”, mysql_error(db->mysql));
}
/// release lock *after* mysql_error
pthread_mutex_unlock(&db->lock);
return error;
}
int my_query(mysql_db* db, char*query) {
pthread_mutex_lock(&db->lock);
error = mysql_query(db->mysql, query);
if (error) {
printf(“Error: %s\n”, mysql_error(db->mysql));
} else {
if (mysql_field_count(db->mysql) == 0) {
rows =
mysql_affected_rows(db->mysql);
printf("%lld rows affected\n",rows );
}
}
pthread_mutex_unlock(&db->lock);
return error;
}
/**
* clean result
*/
int my_flush_result(mysql_db *db) {
MYSQL *mysql = db->mysql;
MYSQL_RES* result;
int qstatus = 0;
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
pthread_mutex_lock(&db->lock);
do {
if ((result = mysql_store_result(mysql))) {
mysql_free_result(result);
}
if (mysql_field_count(mysql) == 0) {
printf("%lld rows affected\n",mysql_affected_rows(mysql));
}
else {
printf("Could not retrieve result set\n");
break;
}
if ((qstatus = mysql_next_result(mysql)) > 0)
printf("Could not execute statement\n");
} while(qstatus == 0);
pthread_mutex_unlock(&db->lock);
return qstatus;
}
So we’ve just create a simple MySQL client! Once you’ve understood the common pitfalls, it’s not
so hard!
Testing with a MySQL server
Once we created our simple MySQL library, we can write our client code issuing some queries and
printing reports.
Provided we’ve a working MySQL server, we can create some test cases. First of all we’ll create the
setup() and teardown() methods to prepare our environment.
void setup(mysql_db *db) {
mysql_library_init(0,NULL,NULL);
db = my_init(db);
my_connect(db);
my_query(db, “create database pippo;”);
my_query(db, “use pippo;”);
}
void teardown(mysql_db *db) {
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
my_query(db, “drop database pippo;”);
my_close(db);
mysql_library_end();
}
Then we create a simple test case, which issues a lot of insert. Note that this code is shortened for
better readability!
void test_sql_client(mysql_db *db) {
int i, error;
error = my_query(db, “create table t1(“ \
“id int not null auto_increment primary key”\
”, value varchar(25));”);
for (i=0; i<10000; i++) {
error
=
my_query(db,
“insert
into
t1(id,value)
values
(‘%d’,’prova%d’);”, i, i);
my_flush_result(db);
}
}
int main(int argc, char**argv) {
mysql_db *db = calloc(sizeof(mysql_db));
...set db values....
setup(db);
test_sql_client(db);
teardown(db);
free(db);
exit(EXIT_SUCCESS);
}
To build our program, mysql_test.c we issue
#gcc mysql_test.c -o mysql_test $(mysql_config --libs)
and with time we’ll see how long will it take to run 10k insert. Note that this will include setup and
teardown time. Writing a more complete test is left as an exercise to the reader ;)
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
# time ./mysql_test
What’s MySQL embedded
To run the previous file, named mysql_test.c, we need a working MySQL instance. Instead if we
don’t have it - or we like more isolation and speed, we could use the MySQL embedded. Using
MySQL embedded means you don’t need to connect to a server, but you’ll write directly into the
MySQL database files. And it’s fast!
To use MySQL embedded, you just need to link your file with the mysqld library:
#gcc mysql_test.c -o mysql_test $(mysql_config --libmysqld-libs)
The server instructions will be added to your program, which will increase its size.
While the core client code remains the same, you need to modify a bit your initialization structure,
passing some parameters to the mysql_library_init() function. Those parameters are the same that
you would pass to mysqld server process, eg. --datadir=path.
If you have never run MySQL server by command line, or you’re not familiar with my.conf please,
take some time to play with it before continuing.
In our case we should only modify the setup() function to reflect our changes. Before that we need
to sketch the MySQL database directory structure.
MySQL directory structure
MySQL stores databases and authentication information in the /var/lib/mysql directory. The
forementioned database would have the following structure:
/var/lib/mysql/
├── mysql <- the core database containing user/password credentials
├── pippo <- a newly created database
By default, MySQL embedded will use the /var/lib/mysql directory, so interfering with our server and
requiring RW access to /var/lib/mysql. This is usually not what we want. Instead we want to put
everything in another place, eg. a temporary directory under /tmp/. So when we run the
embedded server, we should create a new directory and tell it to bootstrap there a new MySQL
installation. Let’s do it.
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
Bootstrapping MySQL embedded
To run the embedded server we need to provide:

a directory where to store the new mysql databases;

the parametes to run the embedded server;

the server groups.
Server groups are the stanzas of my.conf file from which mysqld - and our embedded application read configuration options.
A typical my.conf files has the following structure:
[mysqld]
...options for server...
[embedded]
...options for the embedded server...
[server]
...options for server...
To specify custom parameters relative to our application, we could add the following sections to
our my.conf, or - with more isolation - use another my.conf containing the specified sections.
[embedded]
...options for *all* embedded applications...
key_buffer_size=32M
[xxx_SERVER]
...replace xxx with program name (eg. argv[0]) and
add options for this specific program …
datadir=/tmp/
[xxx_CLIENT]
...replace xxx with program name (eg. argv[0]) and
add options for this specific program...
datadir=/tmp
We can even specify those parameters as arguments to the mysql_server_init()
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
void embedded_mysql_library_init(char* progname) {
/// create a temporary directory where to store the db
char *datadir = strdup("/tmp/mysqlembedXXXXXX");
assert( mkdtemp(datadir)!=NULL );
/// prepare server parameters
const char *server_options[] = {
progname,
/* this string is not used */
"-h", datadir,
"--key_buffer_size=32M",
"--bootstrap", // create the required mysql files
NULL
};
int num_elements = sizeof(server_options) / sizeof(char *) -1;
/// create server groups for my.cnf
sprintf(progname_client, "%s_CLIENT", progname);
sprintf(progname_server, "%s_SERVER", progname);
char *server_groups[] = {
progname_server,
progname_client,
"embedded",
NULL // end of arguments
};
/// initialize the embedded server
assert(mysql_library_init(num_elements,
(char**)
server_options,
server_groups) == 0);
}
void embedded_my_init(mysql_db *db) {
pthread_mutex_lock(&db->lock, NULL);
/// initialize and set options
assert( (mysql = mysql_init(db->mysql)) != NULL );
assert(
mysql_options(db->mysql,
MYSQL_READ_DEFAULT_GROUP,
progname_client) == 0);
assert(
mysql_options(db->mysql,
MYSQL_OPT_USE_EMBEDDED_CONNECTION,
NULL) == 0);
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
pthread_mutex_unlock(&db->lock, NULL);
return db;
}
With our new methods, the setup function become:
void setup(mysql_db *db, char* progname) {
embedded_mysql_library_init(progname);
db = embeded_my_init(db);
// from here everything is the same
my_connect(db);
my_query(db, “create database pippo;”);
my_query(db, “use pippo;”);
}
And our new main():
int main(int argc, char**argv) {
mysql_db *db = calloc(sizeof(mysql_db));
...set db values....
setup(db, argv[0]);
test_sql_client(db);
teardown(db);
exit(EXIT_SUCCESS);
}
Replacing the old one with this one, everything will work nicely in both cases. When you link against
libmysqlclient, all the different initialization variables - only applicable to embedded server - are
ignored.
Duplicate test files with embedded sql
To run both tests you have to compile twice your software:
#gcc mysql_test.c -o mysql_server_test $(mysql_config --libs)
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
#gcc mysql_test.c -o mysql_embedded_test $(mysql_config --libmysqld-libs)
Now you have two executables, one using the client connection, one the embedded server.
Here are some speed results for 50k INSERTs run locally on my Laptop. You’re welcome to provide
further stats for SELECT, UPDATE, INSERT or UPDATE and TRANSACTIONS!
CPU: 2 x Intel(R) Core(TM)2 Duo CPU @2.1Ghz
RAM: 8Gb
MySQL Server InnoDB
21 sec
MySQL Server MemoryDB
7sec
MySQL Embedded InnoDB
5.7 sec
MySQL Embedded MemoryDB
5.6 sec
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015
Licenza d'uso “Attribuzione - Non commerciale - Non opere derivate”, secondo i criteri
internazionali Creative Commons (http://creativecommons.org/licenses/by-nc-nd/2.5/it/)
BABEL S.r.l. - P.zza S.Benedetto da Norcia 33 - 00040, Pomezia (RM) - Tel:. +39 06.9826.9600 - Fax. +39 06.9826.9680
E-Mail: [email protected] – PEC: [email protected] – WEB: http://www.babel.it
Res. Imprese di Roma N° 06062681009 - N.R.E.A. 953123 - P.I. e C.F. 06062681009 Cap. Soc. € 102.774,00 i.v.
Società soggetta alla direzione e coordinamento della Par-tec S.p.A - Società iscritta al registro delle Imprese di Milano al numero 1293820015