Embedded mysql in C++ under windows

Embedding mysql into a C++ application is not that complicated, it's just badly documented. A few things are needed:

1. The embedded library, libmysqld.lib. This should be residing in %mysql_dir%/lib/opt. Do not confuse it with libmysql.lib, those are two different things. Most probably, you won't see a libmysqld.lib in there. I'm not entirely clear on the details, but it seems that it was removed from recent installations. I think 4.1 has it, but I'm positive both 5.0 and 5.1rc don't. You can try scavenging the web to find it somewhere, but it is a lot easier to build it from source. If you happen to have it bundled with your installation, skip this part.
   • Download the source as a tgz archive for the current version (5.0.51b as of this writing) and extract it.
   • Follow the instructions in INSTALL-WIN-SOURCE, they are quite thorough. Note that you will need to install CMake, but it is painless.
   • After building mysql, look in libmysqld\debug and you will find libmysqld.lib and libmysqld.dll.
   You need to link your application against libmysqld.lib and copy libmysqld.dll to your application's folder (or make it accessible in other ways).
2. Try this sample:

   static char *server_options[] =
   { "mysql_test", "--datadir=c:/somewhere/data", "--language=c:/somewhere/english", 0};
   int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;
   
   static char *server_groups[] = { "libmysqld_server", NULL };
   
   int main()
   {
     mysql_library_init(num_elements, server_options, server_groups);
     MYSQL* handle = mysql_init(0);
     mysql_options(handle, MYSQL_OPT_USE_EMBEDDED_CONNECTION, 0);
     mysql_real_connect(handle, 0, 0, 0, 0, 0, 0, 0);
   
     // from this point, use 'handle' as if you were using
     // it before
   
     mysql_close(handle);
     mysql_library_end();
   }

   Be careful though: as this bug is saying, passing the wrong options to mysql_library_init() will probably crash your application. Concerning the hardcoded paths, I'm still working on that. Obviously, even if the server is embedded in the application, the database needs to be stored somewhere and the path needs to be specified. I guess the best way is, under Windows, to add the install path in the registry and use it. You could also use the current working directory (might be dangerous) or lookup the executable's path (GetModuleFileName()).
3. I've also had problems with creating stored procedure. Mysql was complaining that it couldn't find the table mysql.proc. The mysql database is created automatically when you install the server. Because your embedded server starts completely empty, it seems that there are things you cannot do. I temporarily solved this problem by copying the mysql folder from my server installation to the data folder of the embedded server.

lost connection to mysql server during query

I am using MySQL for various personal projects and, because I have little experience with databases (except for very simple stuff), I recently decided to learn about stored procedures. The fact that all my queries were hardcoded made me feel uncomfortable. Since then, I started getting errors randomly:

Lost connection to MySQL server during query

followed by

MySQL server has gone away.

I looked at MySQL server has gone away in the documentation:

• [..] the server timed out and closed the connection. [..] By default, the server closes the connection after eight hours if nothing has happened. Not the case. The error happened after a few seconds or minutes of connecting to the server.
• You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command. No, the server is still running. I can ping it, connect to it and issue commands without any problems. In fact, restarting my application works (only to fail again after a few queries).
• You tried to run a query after closing the connection to the server. No.
• A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.No, privileges didn't change while my program was running.
• You got a timeout from the TCP/IP connection on the client side. Again, no. All suggestions about timeouts do not make sense. Actually, it looked like I was getting this error if I executed a bunch of stored procedure calls rapidly.
• Skipping more stuff about timeouts
• You can also get these errors if you send a query to the server that is incorrect or too large.This is getting interesting: a query [..] that is incorrect. Googling reveals posts such as this:

  Right! Solved it... [...] There are 2 queries which make up a navigation panel on this chat system, one shows public chatrooms, the other private. The query which made up the private chatrooms was: select distinct chatroom_label as label, chatroom_id as id from chatroom where chatroom_date_deleted_timestamp is null and chatroom_is_permanent_chatroom=1 order by chatroom_label desc, chatroom.chatroom_label asc which is obviously wrong since it's ordering by the same column twice. This didn't produce an error but instead crashed the MySQL connection.

  After double checking my queries, I found no problems. Indeed, very simple queries called in a loop would eventually fail.

Finally, after several hours of research, I stumbled upon this thread and especially this post:

Stored procedures always return one extra result set for the error status of the procedure so you need to set the CLIENT_MULTI_RESULTS option and need to iterate over the returned result sets using the mysql_more_results() and mysql_next_result() API functions.

Looking back at my code, I have a stored procedure that looks like:

create procedure get_something(sid int)
begin
  select * from somewhere where id=sid;
end

and I was calling it like this:

void f(MYSQL* handle)
{
  mysql_query(handle, "select get_something(0)");
  MYSQL_RES* r = mysql_store_result(handle);

  MYSQL_ROW row;
  for (;;)
  {
    row = mysql_fetch_row(r);
    if (!row)
       break;

    use(row);
  }

  mysql_free_result(r);
}

The problem is that the query returned more than one result set. After leaking these result sets for a few times (random), the connection was dropped. There are two bugs here: mine (leaking result sets) and mysql's (shitty error messages). The solution is to make sure all result sets are processed before issuing another query:

while(mysql_more_results(handle))
{
  mysql_next_result(handle);
  MYSQL_RES* r = mysql_store_result(handle);
  if (res)
      mysql_free_result(r);
}

In my case, since the actual call to mysql_query is wrapped inside another function, I added this bit before doing any query. It solved the problem nicely. For more informations and lots of "me too" posts, google is always helpful.