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!
Technorati Tags:No Tags
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
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?)
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.
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.