2

We have been using MySQL since its beginnings. Back then we thought that descriptive column names were a good idea. So we created columns like date, open, high, low, close, volume, signal and so on that reflected the terms we actually used in our work. Some of which were keywords or reserved words and some of which became reserved words, like signal. As the years rolled by many programs in a wide variety of languages were written to use those tables. I gather you can see where this is going. In the step up from 5.0 to 5.5 MySQL included signal as a reserved word, which effectively means that we can no longer upgrade as many programs access the column signal and recoding would be a HUGE effort. We'd really like to upgrade and, now that Oracle has its hands on MySQL, we would also like to consider a move to MariaDB. So, what's a poor boy to do? Any thoughts/ideas greatly appreciated.

  • 1
    Why don't you escape field names with backquotes? – akuzminsky Mar 8 '16 at 19:56
  • Yes, that would work, but there are literally hundreds of programs, many in compiled languages, so it would be a HUGE effort. I thought of trying to log what programs are using what tables... – John Mar 8 '16 at 20:16
  • 2
    Random thought - maybe some proxy that would rewrite queries? – akuzminsky Mar 8 '16 at 20:44
  • @akuzminksy hah, you posted that comment 3 minutes before I posted my answer. Though I can guarantee I was in the process of writing it for more than 3 minutes. – Derek Downey Mar 9 '16 at 1:06
  • @DerekDowney, you win! I upvoted your answer :) – akuzminsky Mar 12 '16 at 20:13
5

The best answer is what @akuzminksy mentioned: escape the fields.

Since you indicate doing so would be prohibitive in terms of amount of work required, there might be another solution: a pre-parse query rewrite plugin

A preparse query rewrite plugin has these characteristics:

  • The plugin enables rewriting of SQL statements arriving at the server before the server processes them.

  • The plugin receives a statement string and may return a different string.

Of course, that requires upgrading to 5.7! And the effort required to build this custom plugin might also be prohibitive.

Other tools that offer query rewriting capabilities are:

Since these tools are proxies, all rewriting is done 'preparse'.

Disclaimer: I make no claims that this will actually work, but is the only path forward I see besides code overhaul.

  • I wasn't aware pre-parse query rewriting. Thanks, that would seem to offer a solid path forward that will allow us to upgrade while continuing to back-tick our queries as we touch the code. – John Mar 9 '16 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.