SQL is a great language for human query writers, but that has given it a bias toward a query->generic error. It assumes that when you run a query and it returns an error, you are smart enough to know what query caused the error. Easy if you are a human using a commandline. Most SQL connection libraries just log the error, not the SQL that caused the error. A simple production problem caused by a missing table becomes a two hour snipe hunt through the code to identify the SQL statement, and from there figure out the issue.
The easiest solution is to have a DB connection layer that adds some additional exception handling and logs what was being run, or a stack trace that actually links to the portion of your code that is the problem. In JBoss/Java I think you could subclass the JDBC Driver (ie com.mysql.jdbc.Driver) put your own wrapper methods with better exception handling and then use the new class in your datasources. I haven't tried this yet, but it seems like a convenient solution.
More directly its better to log too much and have to turn the log level down than too little and have no way to turn it up.
As another option, why can't the SQL server have a setting to log failed queries? (I understand Oracle does via connection tracing, to some extent) Have to be careful about the security implications, but it could make some troubleshooting a lot easier.
2010-01-09 11:15:50,935 ERROR [org.mindbent.audit-test] (ajp-0.0.0.0-8009-12) error performing auditThe error above is useless.
javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not get next sequence value
The easiest solution is to have a DB connection layer that adds some additional exception handling and logs what was being run, or a stack trace that actually links to the portion of your code that is the problem. In JBoss/Java I think you could subclass the JDBC Driver (ie com.mysql.jdbc.Driver) put your own wrapper methods with better exception handling and then use the new class in your datasources. I haven't tried this yet, but it seems like a convenient solution.
More directly its better to log too much and have to turn the log level down than too little and have no way to turn it up.
As another option, why can't the SQL server have a setting to log failed queries? (I understand Oracle does via connection tracing, to some extent) Have to be careful about the security implications, but it could make some troubleshooting a lot easier.
Leave a comment