Agile India.

After switching to the DBCP (Database Connection Pool) drivers that comes bundled with Tomcat 5+, we started seeing a weird exception on our web app. If we leave our server idle for a long time (5-6 hrs) or if we put our laptop to sleep and 5-6+ hrs later when we bring up the laptop and try to access any page on our web app, we get the following error on the web page:

(The error was “could not inspect JDBC autocommit mode”)

When we see our logs, we find the following exception:

18:26:34,845 ERROR JDBCExceptionReporter:72 - Connection com.mysql.jdbc.JDBC4Connection@36fbe6ab is closed.
SEVERE: could not inspect JDBC autocommit mode
org.hibernate.exception.GenericJDBCException: could not inspect JDBC autocommit mode
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
	at org.hibernate.jdbc.JDBCContext.afterNontransactionalQuery(JDBCContext.java:248)
	at org.hibernate.impl.SessionImpl.afterOperation(SessionImpl.java:417)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1577)
	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
	...
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.ha.tcp.ReplicationValve.invoke(ReplicationValve.java:347)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Thread.java:637)
Caused by: java.sql.SQLException: Connection com.mysql.jdbc.JDBC4Connection@36fbe6ab is closed.
	at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.checkOpen(DelegatingConnection.java:354)
	at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.getAutoCommit(DelegatingConnection.java:304)
	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.getAutoCommit(PoolingDataSource.java:224)
	at org.hibernate.jdbc.ConnectionManager.isAutoCommit(ConnectionManager.java:185)
	at org.hibernate.jdbc.JDBCContext.afterNontransactionalQuery(JDBCContext.java:239)
	... 29 more

On carefully looking at the exception, we find:

Caused by: java.sql.SQLException: Connection com.mysql.jdbc.JDBC4Connection@36fbe6ab is closed.

From the exception its clear that the reason for this exception is that the db connection is closed. Which is in sync with our finding so far, idle server causes this problem.

What happens is, the Database closes the connections since they are inactive. But DBCP & hence hibernate still thinks those connections are active and tries to execute some command on them. This is when an exception is thrown.

We can easily simulate this exception. When everything is running fine, restart your DB and you’ll see the same exception when you try to access any dynamic page on your site.

On reading DBCP configuration, I found:

Basically testOnBorrow is true by default, which means DBCP will test if the connection is valid (alive) before returning. But to test it, it needs a query using which it would validate the connection. Since in our case we did not specify any value, when hibernate would ask DBCP for a connection, it would just return a connection without testing if its a valid connection before returning. And then the stale connection throws an exception when we try to perform any operation on it. But if the validation query is specified, then DBCP will drop the connection and give us another valid connection. This avoiding this problem.

So the simple solution to this problem is to add a validationQuery to the connection pooling configuration (in our case it was the context.xml file).

validationQuery="select version();"

Q.E.D

Today all of a sudden, one of my MsSQLExpress servers refused to start. On looking at the Windows Event Log, I discovered the following errors:

FCB::Open failed: Could not open file c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQ\DATA\mastlog.ldf for file number 2. OS error: 2(The system cannot find the file specified.)/

FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf’. Diagnose and correct the operating system error, and retry the operation.

I wanted to find a way to quickly restore the Master database. Unfortunately I did not have any backup. (This was a playground server, not a production box). Since I had nothing changed on the master db, restoring a fresh copy would not make any difference.

Luckily I found out that there is a directory called “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data” and as the name suggests it contains a template DB. So I simply replaced master.mdf and mastlog.ldf files in “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” directory with those found under the Template Data directory.

Hurray! My MsSQLExpress server started fine without any further complains.

I’m using TikiWiki with MySQL database for SDTConf and AgileCoachCamp wiki. Recently both these wikis were down with the following error:

On googling about it, I found: “One common cause of this is the disk hitting 100%, so use df and du -sk / to try and figure out where your clogs are.” In my case, I’m pretty sure that I have enough disk space. I’m still not sure what caused this. But I found a work around to solve the problem at hand. If any one has ideas about this, I was be eager to hear about it.

Solution: execute the following command:
mysqlcheck –auto-repair -u{username} -p{password} {database_name} {database_table_name}

Three types of concurrency control:

Pessimistic concurrency control: a record is unavailable to users while somebody else is accessing it. i.e. from the time the record is fetched until it is updated in the database. The way this is implemented is, the moment someone fetches a record, a lock is acquired on that table and no other user is allowed to access the same table until the lock is released.

Optimistic concurrency control: a record is unavailable to other users only while the data is actually being updated. The way this is implemented is, the update examines the record in the database and determines whether any changes have been made. Attempting to update a changed record results in a concurrency violation and hence an exception.

Last in Wins: a record is never unavailable to the users. Every time the user updates the record is simply saved without checking for updates on the record. This can potentially overwrite any changes made by other users since you last refreshed the records. This is the easiest to implement and the most dangerous to use.

Implementation details:

Pessimistic concurrency control (PCC):
It can be implemented with or without using database locks. It‘s usually implemented on the application side. Database locks are available in very limited DBMS and hence it‘s a good idea not to use database locks.

Most of the applications today have a multi-layered architecture and have a separate layer for database access. PCC is mostly implemented in the data access layer or the service layer. We can maintain a static list of tables, which are currently been accessed. Before processing any request for a record, we check in this list. If the table is present in the list, then we throw an exception, else add it to the table and fetch the data. There is an alternative to the static table. One can use lock-bits at the table record level and maintain them in a session pool.

The static list or the lock-bits can be cleared when the user leaves the page under program control. This is done to free the locks as soon as possible, but there is no guarantee that this will occur.
As the user can leave the browser or the site at any moment, we have to clear the list or lock-bits stored are the session pool on Session End. And as a final countermeasure, we might have a demon running on the server cleaning old locks.

We have to keep in mind that an application that holds locks for long periods is not scalable.

Last in Wins
This concurrency control can be implemented without having to do anything. This is the default behavior. But this might be unacceptable in some circumstances if different users start to access the same records frequently.

Optimistic concurrency control (OCC)
In OCC, several users can access a screen for the same record, but only the first one to save it will succeed. The rest would get concurrent change exception.

OCC is implemented based on the record version. The record version could mean all the columns of the record or a unique state code. For the unique code purpose we can use a GUID string or a date-time value. I‘m not too sure which one to use. But these are some points:

The GUID is not portable if we want to port the code to other platforms.
The GUID uses 40 bytes of binary data instead of the 8 bytes used by the date.
The date-time tells us when the record was last updated.

Example:
User A fetches a record from the database along with its version, more on this later.
User B fetches the same record from the database, also with its version and writes the updated record back to the database, updating the version number.
User A “tries” to write the record to the database, but as the version on the database is different from the version hold by user A, the write fails, leaving user B‘s changes intact.

It‘s always favorable to use OCC. But there might be situations where we have a long transaction, which spans over several screens. The user might not want to get concurrent change exception at the end of the updation. In these cases, it‘s better to use PCC and let the user know upfront about any possible locks.

Requirement: You have a portal which display a huge amount of data stored in the database. The web page essentially looks like an ancient VB app with all the winform kind of a look. The user wants to page the data that is retrieved from the database. Also at…

Recently I had a situation where I had to create a ‘Global Temp Table‘ inside a ‘T-SQL Stored Procedure‘. Though I was deleting the temp table at the end of the stored proc, there were some issues. The temp table was not deleted if…

Of late, we have been getting the following error every now and then. We are using MS SQL Server 2000 and it‘s JDBC drivers.

DATABASE ERROR CODE : 257
11:58:18 [ ] WARN WEB DATABASE ERROR CODE : 257
11:58:18 [ ] ERROR …

What happens when you try the following inside a SQL Server Strored Procedure?

EXEC (‘CREATE TABLE #TEMPTABLE‘+‘123‘+‘(firstColumn varchar(10), secondColumn varchar(20), thridColumn int)‘)
INSERT INTO …

What does the transaction log contain?
Transaction log contains log records which are necessary for recovering or restoring a database.

What happens if these log records were never deleted from the transaction log?
Every transaction log has a fixed size….