Railo, MySQL and Column Aliases

On January 11, 2012, in SQL, Railo, by Anuj Gakhar

Today, I encountered an error with one of my Railo powered websites (with MySQL as database) – it started throwing an error wherever column aliases were being used in the queries. On a cfdump, the column aliases were just not there in the resultset. The original column name was, though. Running the same query in my SQL client (Sequel Pro) was working fine. So, obviously, it had something to do with the MySQL JDBC driver which Railo uses under the hood. However, this has been working fine for ages. The only thing that changed today was that I downloaded the latest and greatest Railo Server with Tomcat 7 from Railo’s website. This is an installer that installs Railo to /Library/Railo if you use the default installation values. Prior to this, I was using my manual installation of Railo/Tomcat and I was using Tomcat 6 instead of Tomcat 7.

Apparently, on reading about it more, I found that the MySQL JDBC driver was indeed changed to ignore the aliases, since version 5.0.x

The JDBC specification states that a column name is not changed by “AS”, it’s always the actual name of the column (if such a name exists), so that clients can build a query based on metadata alone. One can only find the name used in the ‘AS’ through ResultSetMetaData’s “getColumnLabel()” method.

So that explains it. And it looks like Railo is using ResultSetMetaData.getColumnName() instead of ResultSetMetaData.getColumnLabel(). Obviously, this is not acceptable as this breaks existing apps. However, you can fix this easily by changing the Alias Handling behaviour of the datasource in question.

Change the datasource

In Railo Admin, edit your Datasource and enable “Alias Handling” – which will basically add a “useOldAliasMetadataBehavior=true” to the connection string. Once you update the datasource, you will notice that you DNS connection string will look like below :-

Class: org.gjt.mm.mysql.Driver
DNS: jdbc:mysql://localhost:3306/YOUR_DSN?characterEncoding=UTF-8&useUnicode=true&useOldAliasMetadataBehavior=true

It is basically adding an additional parameter to the connection string useOldAliasMetadataBehavior=true which does the trick. On reading more about what this does, here is a little explanation :-

useOldAliasMetadataBehavior – Should the driver use the legacy behavior for “AS” clauses on columns and tables, and only return aliases (if any) for ResultSetMetaData.getColumnName() or ResultSetMetaData.getTableName() rather than the original column/table name? In 5.0.x, the default value was true.

I also found another parameter that, according to the documentation of the connector, should be preferred over useOldAliasMetadataBehavior.

useColumnNamesInFindColumn – Prior to JDBC-4.0, the JDBC specification had a bug related to what could be given as a “column name” to ResultSet methods like findColumn(), or getters that took a String property. JDBC-4.0 clarified “column name” to mean the label, as given in an “AS” clause and returned by ResultSetMetaData.getColumnLabel(), and if no AS clause, the column name. Setting this property to “true” will give behavior that is congruent to JDBC-3.0 and earlier versions of the JDBC specification, but which because of the specification bug could give unexpected results. This property is preferred over “useOldAliasMetadataBehavior” unless you need the specific behavior that it provides with respect to ResultSetMetadata.

But, I guess since this is only valid since version 5.1.7 of the connector, it may not be valid as I am not really sure what version of Mysql connector does Railo use. But surely, something changed in one of the recent versions that caused the alias handling to break.

Tagged with:  

5 Responses to Railo, MySQL and Column Aliases

  1. Raj says:

    Thanks for sharing this, I was going nuts because of this. Have you ever faced an issue with escaping the ‘#’ (bass) sign in Railo?.

  2. Jimmy says:

    Hi Anuj, I’m evaluating Railo for conversion of an existing CF application. Version 4.0 (beta) doesn’t seem to offer the ‘Alias Handling’ option for any type of datasource. I tried adding ‘useOldAliasMetadataBehavior=true’ to the connection string, but it made no difference – aliases aren’t recognised. Any ideas?

  3. Abid says:

    I have tried using useOldAliasMetadataBehavior=”true” for my Java webservice. But it is not working as expected.

    Can anyone help please?

    Thank you.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar