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.

Null pointer constants

I personally use the literal 0 instead of NULL for null pointers, although this usually quickly degrades into a religious discussion (like any coding standard discussion). However, I was recently asked whether

  const int null = 0;
  char* p = null;

was valid or not. Visual C++ 2005 was accepting that code, but since we need to port this code under many different platforms, we had to try it on gcc 2.96, which barfed about converting an int to a char*. My first guess was that the literal 0 could be converted to a pointer, but not a constant int variable and that Visual C++ was overly permissive. Of course, I was wrong. Here's from §4.10:

A null pointer constant is an integral constant expression (§5.19) rvalue of integer type that evaluates to zero. [...]

And §5.19 reads:

[...] An integral constant-expression can involve only literals, enumerators, const variables or static data members of integral or enumeration types initialized with constant expressions, non-type template parameters of integral or enumeration types, and sizeof expressions.

Therefore,

  const int null = 0;

is a constant expression, which means

  char* p = null;

is perfectly well defined. gcc 3.3.1 had a bug reported about this (I suspect the bug had always been present). It was fixed in 4.0.0. It was also quickly discussed by the standard commitee. In conclusion, gcc is wrong and the code is right. However, we're stuck with gcc, so we changed the code. Sigh.

WINVER

We recently had a problem after migrating from Visual C++ 6.0 to 2005 where the message

WINVER not defined. Defaulting to 0x0502 (Windows Server 2003)

would appear while building MFC projects. After investigating this, here are my findings.

Prior to Visual C++ 2003, WINVER was defined in winres.h as the following:

  #ifndef WINVER
  #define WINVER 0x0400   // default to Windows Version 4.0
  #endif

In this case, WINVER was automatically defined to support Windows 95/98. With Windows 2000, a new platform sdk came out and in order to use it, it had to be installed and WINVER had to be manually changed to 0x0500 in stdafx.h.

Starting with 2003 (.NET), the definition of WINVER moved to afxv_w32.h and looked like this:

  #ifndef WINVER
  #  ifdef _WIN32_WINNT
  #    define WINVER _WIN32_WINNT
  #  else
  #    pragma message("WINVER not defined. Defaulting to 0x0501 (Windows XP and Windows .NET Server)")
  #    define WINVER 0x0501
  #  endif
  #else
  #  if WINVER < 0x0400
  #    error MFC requires WINVER to be #defined to 0x0400 or greater
  #  endif
  #endif

From that point on, WINVER stopped being silently defined. On Visual C++ 2005, afxv_w32.h looks like:

  #ifndef WINVER
  #  ifdef _WIN32_WINNT
  #    define WINVER _WIN32_WINNT
  #  else
  #    pragma message("WINVER not defined. Defaulting to 0x0502 (Windows Server 2003)")
  #    define WINVER 0x0502
  #  endif
  #else
  #  if WINVER < 0x0400
  #    error MFC requires WINVER to be #defined to 0x0400 or greater
  #  endif
  #endif

Now, the wizards were updated so that stdafx.h is correctly generated for new projects. The default file contains this part:

  // Allow use of features specific to Windows XP or later.
  #ifndef WINVER  
     // Change this to the appropriate value to target other
     // versions of Windows.
  #  define WINVER 0x0501 
  #endif

The problem is, the conversion tool that comes with 2005 modifies the project files, but (for obvious reasons) not the code. Which means we now get the message about WINVER being undefined.

Two solutions are possible:

1. Add a define for WINVER in the project settings, in the proprecessor section. This works, but I don't usually like putting things in the project settings. It makes it harder to port the source code, since part of it depends on compiler/ide-specific stuff.

2. Add a define in stdafx.h before anything is included (to reproduce what the default stdafx.h now looks like).

As for its value, it depends on what you want to support. If you want your application to run on Windows XP and later (including original version and SP1), WINVER should be 0x501. To use features from SP2, it should be 0x502.

As for the other related macros (_WIN32_WINNT, _WIN32_WINDOWS and _WIN32_IE), they should be set automatically, so no problem here. The generated stdafx.h does set these macros to specific values, but MFC headers will reproduce these values anyways. Define them if you need specific (and earlier) versions.

On Windows Vista, a new macro named NTDDI_VERSION is available. It should make this versioning thing a little bit easier.

Here are a few references:

• What's the difference between WINVER, _WIN32_WINNT, _WIN32_WINDOWS, and _WIN32_IE?
• Modifying WINVER
• How to: Modify WINVER and _WIN32_WINNT
• Using the Windows Headers
• Porting MFC projects to Visual C++ .NET or to Visual C++ 2005 changes in the target operating system