Skip to main content

Fixing "MySQL server has gone away" Errors in C

I ran across an old question on Stack Overflow the other day in which a user was having issues maintaining his connection to MySQL from C. I left a brief answer there for anyone else who might stumble across the same problem in the future, but I felt it was worth expanding on a bit more.

The error "MySQL server has gone away" means the client's connection to the MySQL server was lost. This could be because of many reasons; perhaps MySQL isn't running, perhaps there's network problems, or perhaps there was no activity after a certain amount of time and the server closed the connection. Detailed information on the error is available in the MySQL documentation.

It's possible for the client to attempt to re-connect to the server when it's "gone away" although it won't try to by default. To enable the reconnecting behavior, you need to set the MYSQL_OPT_RECONNECT option to 1 using the mysql_options() function. It should be set after mysql_init() is called and before calling mysql_real_connect(). This should solve the problem if the connection was closed by the server because of a time-out.

The MySQL documentation that discusses the reconnect behavior points out that only one re-connect attempt will be made, which means the query can still fail if the server is stopped or inaccessible. I ran across this problem myself while writing a daemon in C that would periodically pull data from MySQL. The daemon was polling at set intervals far less than the time-out period, so any such errors were the result of an unreachable or stopped server. I simply jumped execution to just prior to my work loop's sleep() call and the daemon would periodically try to re-connect until the server came back up.

#define DBHOSTNAME localhost
#define DBHOSTNAME dbuser
...

MYSQL *db = mysql_init(NULL);
if (db == NULL) {
    fprintf(stderr, "Insufficient memory to allocate MYSQL object.");
    exit(EXIT_FAILURE);
}

/* enable re-connect behavior */
my_bool reconnect = 1;
int success = mysql_options(db, MYSQL_OPT_RECONNECT, &reconnect);
assert(success == 0);

if (mysql_real_connect(db, DBHOSTNAME, DBUSERNAME, DBPASSWORD, DBDATABASE,
    0, NULL, 0) == NULL) {
    fprintf(stderr, "Connection attempt failed: %s\n", mysql_error(db));
    exit(EXIT_FAILURE);
}

for (;;) {
    success = mysql_query(db, "<MYSQL QUERY HERE>");
    if (success != 0) {
        /* The error is most likely "gone away" since the query is
         * hard-coded, doesn't return much data, and the result is
         * managed properly. */
        fprintf(stderr, "Unable to query: %s\n", mysql_error(db));
        goto SLEEP;
    }

    /* call mysql_use_result() and do something with data */
    ...

    SLEEP:
    sleep(SLEEP_SECONDS);
}
Labels:

Comments

Post a Comment

Popular posts from this blog

Writing a Minimal PSR-0 Autoloader

An excellent overview of autoloading in PHP and the PSR-0 standard was written by Hari K T over at PHPMaster.com , and it's definitely worth the read. But maybe you don't like some of the bloated, heavier autoloader offerings provided by various PHP frameworks, or maybe you just like to roll your own solutions. Is it possible to roll your own minimal loader and still be compliant? First, let's look at what PSR-0 mandates, taken directly from the standards document on GitHub : A fully-qualified namespace and class must have the following structure \<Vendor Name>\(<Namespace>\)*<Class Name> Each namespace must have a top-level namespace ("Vendor Name"). Each namespace can have as many sub-namespaces as it wishes. Each namespace separator is converted to a DIRECTORY_SEPARATOR when loading from the file system. Each "_" character in the CLASS NAME is converted to a DIRECTORY_SEPARATOR . The "_" character has no special ...
6 comments
Read more

Safely Identify Dependencies for Chrooting

The most difficult part of setting up a chroot environment is identifying dependencies for the programs you want to copy to the jail. For example, to make cp available, not only do you need to copy its binary from /bin and any shared libraries it depends on, but the dependencies can have their own dependencies too that need to be copied. The internet suggests using ldd to list a binary’s dependencies, but that has its own problems. The man page for ldd warns not to use the script for untrusted programs because it works by setting a special environment variable and then executes the program. What’s a security-conscious systems administrator to do? The ldd man page recommends objdump as a safe alternative. objdump outputs information about an object file, including what shared libraries it links against. It doesn’t identify the dependencies’ dependencies, but it’s still a good start because it doesn’t try to execute the target file. We can overcome the dependencies of depende...
Post a Comment
Read more

A Unicode fgetc() in PHP

In preparation for a presentation I’m giving at this month’s Syracuse PHP Users Group meeting, I found the need to read in Unicode characters in PHP one at a time. Unicode is still second-class in PHP; PHP6 failed and we have to fallback to extensions like the mbstring extension and/or libraries like Portable UTF-8 . And even with those, I didn’t see a unicode-capable fgetc() so I wrote my own. Years ago, I wrote a post describing how to read Unicode characters in C , so the logic was already familiar. As a refresher, UTF-8 is a multi-byte encoding scheme capable of representing over 2 million characters using 4 bytes or less. The first 128 characters are encoded the same as 7-bit ASCII with 0 as the most-significant bit. The other characters are encoded using multiple bytes, each byte with 1 as the most-significant bit. The bit pattern in the first byte of a multi-byte sequence tells us how many bytes are needed to represent the character. Here’s what the function looks like: f...
1 comment
Read more