We are using Hibernate 2.1 and have some rising concerns regarding its behaviour in error cases.

Problem 1: Impact of aliases on database problem identification


Hibernate is systematically using generated aliases, for every table and column.
Everything works fine if the mappings files and the database structure are in line, but if there is a difference of type 'column added in the xml file but not in the database table' the only error we get from the database is something like ' CTRSUPPO5_49_ not found'. The alias is pointing to a missing column, but we've lost the information regarding the real column name. I agree with you that the database should return a more verbose error, but this is not the case. So, according to our DBAs, when an error occurs, the only information they have access to are the aliases. Since the aliases are not unique across requests, they are unable to go back to the tables or columns that caused the issue.

Do you see any solution to this problem ?
We thought about logging the generated SQL statements, but only in error cases (we don't want to slow the execution of normal cases, which account for the large majority of time). So we have a second problem : tracing SQL queries on error


Problem 2 :
Tracing SQL queries on error

In order to ease the analysis of errors that may arise in production, we would like to have the ability to trace the generated SQL queries when a SQLException is raised. Is there such an option available with Hibernate ?
If not, could such a feature be included in a future version ?
Note that we plan to do this in a production environment, so P6Spy-like solutions are deemed unacceptable for us.



We are using Hibernate 2.1. We think we have two possible solutions to implement this feature rapidly :
Solution 1 : create a special logger which stores the queries in memory until the Session is closed (then it discards everything). If a JDBCException occurs in Hibernate, all queries are written in a file. We have several problems with this approach :
1. there is no unique Hibernate logger for all the generated SQL statements. We think that there is a logger net.sf.hibernate.SQL in BatcherImpl and net.sf.hibernate.impl.SessionImpl in SessionImpl. Is this correct ? With these loggers are we able to catch all the generated queries ?

2. we are in a J2EE environment and we'll have to define a logger which stores the queries in memory in a per thread manner (so a ThreadLocal variable). We do not like very much the thread local logger definition

3. with a logger like SessionImpl we'll log more than necessary (not only the queries)


Solution 2 : Modify Hibernate to add a log every time an SQL statement is executed and an SQL exception is raised. We have found 95 'catch (SQLException" in Hibernate, but we suppose that only a smaller part of this are interesting for us (the errors at statement, result set or connection closing are of no interest).
Is this statement "every time an SQLException on an SQL statement happens, a JDBCException is raised" true ? There are 50 occurrences of throw new JDBCException in Hibernate. Are all of these corresponding to errors in SQL statements ?

It could be possible to add the SQL statement which caused an error to the JDBCException in a future version of Hibernate ?


Are there any other possibilities (for example using the cache of queries, if any) ?


Question 3 : Are all these aliases useful ? If not, is it possible to add an optimisation step after the SQL statement generation to remove all the unneeded aliases ? The result set getString call is made using the alias or using the number ? Our DBA told us that using aliases in a query slows its execution, so maybe in a future version calling getString(int) should be considered instead of getString(String) (if it is not the case already).

Is it possible to modify the Loader.generateAlias function to read the predefined aliases from the mappings file ? Is this function used for columns and tables ? What happens for the subclasses of entities ?

Thank you for your help.

Florin Cremenescu

hmm - the dba's i normally work with is able to activate sql tracing on the db side to hunt down details in an error situation ...like SGA trace in oracle.

You do know that you can enable/disable logging temporarily with log4j in a running system - allowing you to track it down ?

But besides that - "CTRSUPO5' sounds like a very unique column name to me - why can't you use that ? (the _49_ is just what is done to make it unique in the case you are going to join the table with it self)


and to your suggested solutions: net.sf.hibernate.SQL should catch all executed sql AFAIK.

i don't get what you mean by having predefined aliases in the mappings file ? there are only columns.

uniqification is done to ensure no collision when you start combining and outerjoin queries.

Hi,

Thank you for your answer.

Your suggestion to enable/disable the log is not usable for us. We are talking about a production system, so the Java log and Oracle log are reduced to a minimum : we log only the error cases. There is no enabling/disabling logging temporarily (beyond the fact that we could have a non repetitive error, like one depending on the number of users and concurrency). So we are interested in a solution which logs a maximum of information in case of error, but which does not slows down the system during normal operation.

The aliases given for the columns are not so unique as you said. Here is an example of a query generated by Hibernate (I've put a comment LOOK HERE for the interesting part):




In this query you can see that if you eliminate the numbered suffix you get a prefix CTRSUPPO which is the same for a lot of columns. It seems to us that Hibernate does not take the whole column name but only the beginning. So, given an error on the CTRSUPPO alias, we are unable to identify the offending column and the Java object. That's way I asked if the alias definition could be done in the mapping file, we could provide a different alias prefix for each column. Or could I tell Hibernate to use the entire column name for the alias ? If not, where should I modify the code to force it to use the whole column name as prefix ?

I agree with you that net.sf.hibernate.SQL catches all the generated SQL.

Thank you,
Florin Cremenescu

In a production system you should not be running broken SQL queries.

You need a proper test environment, not changes to Hibernate.

Hi,

The missing column in a table was only an example. It could be anything wrong on a column, from a bug in our code showing up in very special cases, something wrong entered by a web user and not enough checked by the java code to a bug in Hibernate. Very rare events, but it is still possible (with a higher probability in our code than in Hibernate :) ). That's way we want to catch only the error cases.

Thanks,
Florin Cremenescu

ok - got you ,)

The reason hibernate generate these aliases is mostly because of DB2's limitations (10-12 characters on all identifiers!!!) ANd to keep the generated sql short.

We could add a dialect controlled or maybe just globally controlled setting on how long these aliases are - this would at least help you in this case.

Please file a jira issue for it and link back to this discussion so we wont forget it.