Posted on 3 Comments

MySQL reserved words, Google SHOW *_STATISTICS patch

In MySQL, not all keywords are reserved words, and because of the way function parentheses are handled by default, function names aren’t reserved words either.
Reserved words are nasty, as they can’t simply be used for identifiers: database, table, and column names.
I say “can’t simply” because you can of course backtick anything and use it as identifier, even stuff with spaces; but that doesn’t mean you should 😉

While working with the OurDelta (and earlier, Percona) builds, I found that the Google patch with the magic SHOW USER|CLIENT|TABLE|INDEX_STATISTICS commands actually produced all those *_STATISTICS keywords as reserved words (see this bug). It shows up easily in v2 of the patch that also makes this info available through INFORMATION_SCHEMA tables. You quickly find there that unless you backtick the tablenames, you get syntax errors. Not-so-nice.

Now, the MySQL parser is sometimes a bit of a voodoo monster, but I remember the above issue from back when I was in Docs – we fixed up a script to generate the table of reserved words in the manual. It first grabs all %token defines (sql/sql_yacc.yy) and then substracts anything under the keyword_sp: label. Well, way back then it wasn’t that label since that was introduced with 5.0 stored procedures. But the idea is still the same. So going by this old knowledge, the newly added keywords from the abovementioned patch were just missing from the second list. Just need to check it out.

I branched the ourdelta tree from Launchpad, had Quilt (more on that magic tool in another post!) only apply the patchlist up to this one, and made the modification I thought would do the trick. And behold, success! The updated patch is pushed in https://code.launchpad.net/~arjen-lentz/ourdelta/ourdelta-userstatsv2 and will be merged back into the OurDelta trunk for the next build.

So the above can now serve a) as a useful how-to for anybody adding keywords into the parser, but b) there’s also real-world sense for fixing up the userstats patch. With the original patch inside builds that are used by more people, someone could suddenly find their app broken if they happen to have a table named user_statistics or client_statistics; or, actually a table named slow. What, `slow` ?

Yep, along the way I found that the FLUSH SLOW QUERY LOGS functionality is actually also hiding in this same patch file 😉 We might split that into a separate patch: it’s useful, but doesn’t belong with userstats.

Posted on 3 Comments

3 thoughts on “MySQL reserved words, Google SHOW *_STATISTICS patch

  1. I have a patch that splits up all of the flush logs functionality into separate flush commands. It may be more complete to include it instead of one that only splits up flush slow query logs. See:

    http://ebergen.net/wordpress/2008/05/19/splitting-flush-logs-command/

  2. Looking at it, thanks!
    It’ll require a few modifications to blend with the Google userstatsv2 patch as they both introduce new REFRESH_ macros. No big deal.
    And we’ll want to port it back to 5.0, also shouldn’t be a problem by the looks of it.

  3. […] discussed. It’s nothing major but, for instance, we don’t want patches to create any new reserved words as that can break existing […]

Comments are closed.