How to write MySQL conditional comments
MySQL’s version-specific conditional comment syntax confused me for the longest time. Then I learned about printf formatting rules, and it all became clear. Read on if you don’t already know what I mean.
First I’ll explain what I’m talking about. MySQL allows specially formatted comments with a server version number embedded in them. Servers newer than the version number will execute the commented-out code, and older servers won’t. This is really useful sometimes. For example, if you want a SQL script only to run on versions where that feature is supported, you can write it in a conditional comment. The output of mysqldump usually contains a lot of them.
They look like this: /*!32358 ... CODE ...*/;. One useful example is SHOW /*!50002 GLOBAL */ STATUS, which will show global status on all MySQL versions, instead of showing global status on older versions and session status on newer versions.
You can find examples in many places, but for a long time, I didn’t know how to write my own. I didn’t know which numbers should go where. For example, if I wanted to write something to run in 4.1.22 and greater, how should I write the version number? Is it /*!4122*/ or /*!41022*/ or /*!40122*/… I just got lost.
Once I figured it out, of course, it’s very simple. The version number always has five digits. The first number is the major version. The next two are the minor version, left-padded with zeros, and the last are the revision, also left-padded. In terms of sprintf, it looks like sprintf('%d%02d%02d', major, minor, revision). So the number I needed above was 40122.
The advantage to this formatting is that you can do a string comparison on the formatted version numbers to determine whether one is greater than the other; you don’t have to compare each part of the version number separately.
I remembered my long period of confusion recently when I was writing some conditional comments for a new tool. It felt almost too simple to write about, but maybe I’m not the only one who was in the dark for a long time!
Hi Baron!
yeah, these comments can be pretty useful sometimes. I use them to fool the mysql command line client to preserve comments in stored procedures:
delimiter $$
create procedure p()
begin
/*!99999
Comments go here
*/
— code goes here
end;
$
see:
http://rpbouman.blogspot.com/2006/12/mysql-stored-routines-and-command-line.html
Roland Bouman
23 May 07 at 6:29 am
[...] How to write MySQL conditional comments at Xaprb MySQL’s version-specific conditional comment syntax confused me [...]
Rdb Notes · How to write MySQL conditional comments at Xaprb
23 May 07 at 12:46 pm
So, um…what happens when MySQL gets to version 10?
(and why is it telling me I didn’t pass math when 1 7=8?)
Sheeri
24 May 07 at 7:50 am
I almost made a Y2K reference about version 10 myself :-)
I don’t know what’s up with the pass-math plugin. Some people complain about it, but I haven’t seen problems. I upgraded WP and of course lost my custom anti-captcha, so I decided to try this one. I’ll write more about this later; the upgrade has been educational. In any event I’ll probably get into the code and try to fix the math thing. I suspect it’s a cookie problem.
Xaprb
24 May 07 at 8:34 am
Version 10 won’t matter if the values are compared as integers, rather than strings. 100101 > 9999, and it’s backwards compatible too. The only problem occurs if there’s a minor version or revision that gets over 100.
Scott Noyes
1 Jun 07 at 9:45 am
Thank you so much for this article Baron. I was searching for a while for the exact syntax for version specific comments as I have commands that need to be executed across a large range of MySQL versions (the MySQL manual was less than helpful) and this was exactly what I needed.
Mark Stoecker
12 Jul 10 at 4:25 pm