MySQL sucks at prepared statements

A while back, I was experimenting with an alpha or beta version of MySQL’s “new” prepared-statement API.

MySQL has a convenience that, if you get disconnected from the server, it will attempt automatic reconnection for you on the fly. You can force a reconnection attempt with mysql_ping() and our code leverages this.

I have a test harness for database code, and I plugged in the new statement API to it and blam straight into a brick wall. The prepared statements don’t persist across connections. And if you have auto-reconnect on, there was no obvious way to tell that your statements had all just been blown away.

Infact, calling a prepared statement might trigger a reconnect, blow away all the statements, and then try to finish what it had started and cause an application crash. I put in a ticket and got a fix. I was happy with the turn around on that, but I ran into brick wall #2.

After walking thru the prepared statement code, I realized that they had a dilema. The prepared statement system doesn’t retain its construction data. In some random act of resource conservation, MySQL throws away the information used to construct prepared statements. So if it loses its connection to the server, it can’t reconstruct the statement and proceed.

I suggested that they retain the data, we’re talking about short strings here – especially when you consider that they contain data markers rather than actual data; or that they provided a way to register a callback or hook of some kind to say “hey, call me if the server dumps you, honey” so you could put all your preps into a function.

I found the negative response to this a little confusing, concluding that prepared statements was some crap they’d had to support because other databases did and whiney users had asked for it.

Although, what really solidified that opinion is when a few revisions later they went ahead and made auto-reconnect no-longer the default.

So MySQL’s prepared statements are, kinda, OK if what you are doing a tight loop on a query that would otherwise generate a silly amount of time sprintf()ing a query and then parsing it. But it really doesn’t provide the larger advantages of a real server-side prepared-statement system  where an application can prepare all its statements at the get-go and henceforth just assume that the statement handles will be valid until the database handle isn’t.

3 Comments

i’ve seen the mysql folks react similarly in the past. they have ‘attitude’ towards new features (or features the community asked for). it’s odd to me but whatever. they have always tried to be the little DB that could … and could faster than other DBs.

:shrug: it’s a nice DB for what it is. best o luck to you ;)

Hi,

We faced this problem and were able to solve it as follows :
This works for autoreconnect = true
1. on stmt.exec() error, check mysql_error() code
2. while ( error is disconnect/gone away && count < MAX_ERR)
3. stmt.init
4. prepare
5. count++

Trackbacks and Pingbacks

[…] previously bitched about MySQL prepared statemement handling on auto reconnect – they store everything you need to know about a statement … except the SQL itself. If they […]

Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: