KB: Database Connection Issues, Solutions and Workarounds

Database Connection Issues, Solutions and Workarounds

Background

You may encounter issues when trying to connect to databases using JDBC. There are several variants. This document outlines the well known issues and introduces solutions and workarounds to them.

There are several forms of error:

"TypeLoadException" exception
"No Suitable Driver" exception
"Error setting up the static cursor cache" exception
"Error establishing socket" exception
"Login has timed out" exception
Slow connection establishment

"TypeLoadException" exception

The form will usually by a TypeLoadException thrown in a call to Connection.Open().

An example stack trace is shown here:

Exception in thread "main" System.TypeLoadException: 
      com.mysql.jdbc.Driver
at system.Data.Common.AbstractDBConnection.
  ActivateJdbcDriver(AbstractDBConnection.cs:1139)
at system.Data.Common.
  AbstractDBConnection.GetConnectionFromJdbcDriver(AbstractDBConnection.cs:994)
at system.Data.Common.
  AbstractDBConnection.Open(AbstractDBConnection.cs:786)

Cause: JDBC driver class is missing from your application classpath.

Resolution:

If you're running a console application: add the corresponding JDBC driver as a Java reference to your project.
If you're running a web application or a web service: copy the JDBC driver files to your application server lib directory.
If you're running a web application or a web service and your connection string resides in Web.config: make sure the connection string is loaded properly from the Web.config file during application execution.
If you're using a third-party JDBC driver - make sure you specified the correct JDBC driver class name in JdbcDriverClassName connection string parameter. Refer to your JDBC driver documentation to find the desired driver class name.

"No suitable driver" exception

Symptom: Exception with "No suitable driver" message is thrown in calls to Connection.Open().

An Example stack trace is shown here:

Exception in thread "main" System.Data.OleDb.OleDbException: 
      No suitable driver 
at java.sql.DriverManager.getConnection(DriverManager.java:532)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at system.Data.Common.AbstractDBConnection.
  GetConnectionFromJdbcDriver(AbstractDBConnection.cs:999)
at system.Data.Common.AbstractDBConnection.
  Open(AbstractDBConnection.cs:786)

Cause: The JDBC URL supplied to underlying JDBC layer is wrong.

Resolution:

If you're using a third-party JDBC driver - make sure you specified the correct JDBC URL in the JdbcURL connection string parameter. Refer to your JDBC driver documentation to find the way for creating a correct JDBC URL.
- If you're using one of the supported providers, it may be a Grasshopper bug - in which case, we ask you to please report a bug.

"Error setting up static cursor cache" exception

Symptom: Exception with "Error setting up static cursor cache" message is thrown in Tomcat web application in calls to Connection.Open().

An Example stack trace is shown here:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
      Error setting up static cursor cache.

Cause: Microsoft JDBC driver for Sql Server makes an attempt to create temporary files in directory assumed to exist.

Resolution: Create directory named 'Temp' (with no quotes) in your Tomcat root directory.

"Error establishing socket" exception while connecting to the database

Symptom: while running a windows application that includes massive opening and closing of a connections to database server, "Error establishing socket" exception appears after a short time of execution.

Cause: This problem is caused by a lack of a reusable sockets on client machine, since each TCP/IP connection stays in the TIME_WAIT state when the connection is being closed. While a connection is in this state, the socket cannot be reused.

Resolution: do one of the following:

Increase the port range that is used for anonymous ports to approximately 20,000 ports (for example) by modifying the MaxUserPort registry key (this parameter controls the maximum port number that is used when an application requests any available user port from the system). Windows uses the conventional BSD range of 1024 to 5000 for its anonymous (ephemeral) port range. You can set only the upper bound of the ephemeral port range. To modify the MaxUserPort registry key, follow these steps:
- Start Registry Editor (Regedt32.exe). Please note that you should backup your registry and any important files on your computer before editing the registry.
- Locate the MaxUserPort key in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
- On the Edit menu, click Add Value, and then add the following registry value:
  - Value Name: MaxUserPort
  - Data Type: REG_DWORD
  - Value: 65534 (for example)
  - Valid Range: 5000-65534 (decimal)
  - Default: 0x1388 (5000 decimal)
- Quit Registry Editor.
Change the timeout on the connections from 240 seconds (the default) to any length from 30 seconds to 300 seconds. Use the TcpTimedWaitDelay registry parameter to change this value. To do this, start Registry Editor (Regedt32.exe), locate the following key in the registry, and then modify the value HKEY_LOCAL_MACHINE\System\CurrectControlSet\services\Tcpip\Parameters (this parameter determines the length of time that a connection stays in the TIME_WAIT state when the connection is being closed. While a connection is in the TIME_WAIT state, the socket pair cannot be reused. For more information, see RFC 793):
- Value Name:TcpTimedWaitDelay
- Value Type: REG_DWORD-time in seconds
- Valid Range: 30-300 (decimal)
- Default: 0xF0 (240 decimal)

More information available at http://support.microsoft.com/default.aspx?scid=kb;EN-US;319502.

"Login has timed out" exception

Symptom: Exception with "Login has timed out" message is thrown in call to Connection.Open().

An Example stack trace is shown here:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
      Login has timed out.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BaseConnectionStartup.(Unknown Source)
at com.microsoft.jdbc.base.BaseConnection.getNewImplConnection(Unknown Source)
at com.microsoft.jdbc.base.BaseConnection.open(Unknown Source)
at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at system.Data.Common.AbstractDBConnection.
  GetConnectionFromProvider(AbstractDBConnection.cs:990)
at system.Data.Common.AbstractDBConnection.Open(AbstractDBConnection.cs:815)

Cause: The target database is not listening on the port that the application is trying to use to connect to it. If you are working with Microsoft SQL Server and did not specify the port number in the connection string, Visual MainWin tries to resolve the port your database is listening to via UDP connection querying. If Visual MainWin fails to do so, it tries to connect to the default port 1433. If this fails too, you will receive the "Login has timed out" exception.

Resolution:

If you have specified a port number in the connection string - check that the port number is correct – i.e. that it is the one that the database is listening on.
Specify the port number in the connection string by using the Port parameter.

To find out on which port your SQL server named instance is running, read Detecting SQL server port number below.

Connection establishment is slow

Symptom: Establishing a Connection with Microsoft SQL Server is very slow

Cause: Since System.Data implementation uses UDP querying of SQL Server for naming instance port resolution (see http://support.microsoft.com/default.aspx?scid=kb;EN-US;823938) when the port number was not specified in the connection string, if UDP connection to the server can not be established, Grasshopper uses default SQL Server port (1433). However, the attempt to establish a UDP connection takes a long time since in most cases it fails with a connection timeout.

Resolution:

If it is possible, enable UDP connectivity on port 1434 between your SQL Server and client application.
Specify the port number in the connection string by using the Port parameter. To find out on which port your SQL server named instance is running, read Detecting SQL server port number below.
You can also decrease connection timeout period (by using the ConnectionTimeout connection string parameter), so the interaction described above will still occur, but will take less time. However, at the end of the interaction Grasshopper will use default port (1433) for connection to your SQL Server, so this option is only acceptable if you're planning future network changes in your application environment.

Detecting SQL server port number

To detect the port number of your Microsoft SQL Server instance, run regedit.

If you installed the default Microsoft SQL instance, the port number is located in the following registry subkey:
```
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\TcpPort
```

If you installed MSDE as a named instance, the port number is located in the following registry subkey:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\
                                    <Instance_Name>\MSSQLServer\SuperSocketNetLib\Tcp\TcpPort