jTDS: connection object is closed (SQL state = HY010) -> SQL Server writes stack dump (SqlDumpException)

[Josef Hopfgartner]

JDBC Connection via jTDS driver to SQL Server 2000
tested versions: 1.2, 1.2.5, 1.3.0
tested java runtimes: 1.6.23, 1.7.0_4, 1.7.0_15 Openjdk), 1.7.0_7

With version 106 of sql workbench there seems not to be this problem
- Use DbExplorer without error
- use: desc <tablename> without error

With version 114 (not testet any older version)
- one click on DbExplorer's table object will throw the error
- desc <tablename> throws the error
- other queries do work...

[Thomas Kellerer]

Hi,

this works fine for me (using jTDS 1.3.0 and Java7, SQL Server 08.00.2055)

can you send me the workbench.log so that I can see what the offending code is?
(And maybe the SQL Server stackdump - although I doubt that I can extract anything useful from it)

Regards
Thomas


Josef Hopfgartner, 27.03.2013 13:59:

[Josef Hopfgartner]

Hi again,

here is the workbench.log

2013-03-27 13:49 INFO  =================== Log started ===================
2013-03-27 13:49 INFO  Using configdir: /home/NMG/hopfgartner/samba/marcher02/sqlworkbench
2013-03-27 13:49 INFO  Starting SQL Workbench/J, Build 114.6 (2013-03-24 21:18)
2013-03-27 13:49 INFO  Java version=1.7.0_15, java.home=/usr/lib/jvm/java-7-openjdk-amd64/jre, vendor=Oracle Corporation, name=OpenJDK 64-Bit Server VM
2013-03-27 13:49 INFO  Operating System=Linux, version=3.5.0-27-generic, platform=amd64
2013-03-27 13:49 INFO  Starting DeadlockMonitor
2013-03-27 13:49 INFO  Creating new connection for [{Graz Klagenfurt}/mds_klu] for driver=net.sourceforge.jtds.jdbc.Driver
2013-03-27 13:49 INFO  Adding ClassLoader URL=file:/usr/share/java/jtds.jar
2013-03-27 13:49 INFO  Using DBID=microsoft_sql_server
2013-03-27 13:49 INFO  Using quote escape character: "
2013-03-27 13:49 INFO  Using search string escape character: \
2013-03-27 13:49 INFO  Connected to: [Microsoft SQL Server], Database version: [08.00.0760], Driver version: [1.3.0], JDBC Version: [3.0], ID: [Wb1-WbExp-1]
2013-03-27 13:49 INFO  Setting lock timeout: 2500ms
2013-03-27 13:49 ERROR Could not read domains Ungültiger Objektname 'sys.all_objects'. [SQL State=S0002, DB Errorcode=208]
java.sql.SQLException: Ungültiger Objektname 'sys.all_objects'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2893)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2335)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
        at workbench.db.mssql.SqlServerRuleReader.getRuleList(SqlServerRuleReader.java:95)
        at workbench.db.mssql.SqlServerRuleReader.extendObjectList(SqlServerRuleReader.java:139)
        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1448)
        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1273)
        at workbench.gui.dbobjects.TableListPanel.retrieve(TableListPanel.java:964)
        at workbench.gui.dbobjects.TableListPanel.setCatalogAndSchema(TableListPanel.java:869)
        at workbench.gui.dbobjects.DbExplorerPanel$5.run(DbExplorerPanel.java:887)

2013-03-27 13:49 ERROR Ungültiger Objektname 'sys.schemas'. [SQL State=S0002, DB Errorcode=208]
2013-03-27 13:49 ERROR Error retrieving remarks I/O Error: Connection reset [SQL State=08S01]
java.sql.SQLException: I/O Error: Connection reset
        at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1059)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:493)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
        at workbench.db.mssql.SqlServerObjectListEnhancer.readRemarks(SqlServerObjectListEnhancer.java:147)
        at workbench.db.mssql.SqlServerObjectListEnhancer.updateObjectRemarks(SqlServerObjectListEnhancer.java:73)
        at workbench.db.mssql.SqlServerObjectListEnhancer.updateObjectList(SqlServerObjectListEnhancer.java:56)
        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1454)
        at workbench.db.DbMetadata.getObjects(DbMetadata.java:1273)
        at workbench.gui.dbobjects.TableListPanel.retrieve(TableListPanel.java:964)
        at workbench.gui.dbobjects.TableListPanel.setCatalogAndSchema(TableListPanel.java:869)
        at workbench.gui.dbobjects.DbExplorerPanel$5.run(DbExplorerPanel.java:887)
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
        at java.io.DataOutputStream.write(DataOutputStream.java:107)
        at net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket(SharedSocket.java:717)
        at net.sourceforge.jtds.jdbc.RequestStream.putPacket(RequestStream.java:570)
        at net.sourceforge.jtds.jdbc.RequestStream.flush(RequestStream.java:518)
        at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1046)
        ... 10 more

2013-03-27 13:49 ERROR Error retrieving table types Invalid state, the Connection object is closed. [SQL State=HY010]
java.sql.SQLException: Invalid state, the Connection object is closed.
        at net.sourceforge.jtds.jdbc.JtdsConnection.checkOpen(JtdsConnection.java:1733)
        at net.sourceforge.jtds.jdbc.JtdsConnection.createStatement(JtdsConnection.java:2341)
        at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getTableTypes(JtdsDatabaseMetaData.java:1874)
        at workbench.db.DbMetadata.retrieveTableTypes(DbMetadata.java:2375)
        at workbench.db.DbMetadata.getObjectsWithData(DbMetadata.java:608)
        at workbench.db.objectcache.ObjectCache.addTableList(ObjectCache.java:383)
        at workbench.db.objectcache.DbObjectCache.addTableList(DbObjectCache.java:68)
        at workbench.gui.dbobjects.TableListPanel.retrieve(TableListPanel.java:965)
        at workbench.gui.dbobjects.TableListPanel.setCatalogAndSchema(TableListPanel.java:869)
        at workbench.gui.dbobjects.DbExplorerPanel$5.run(DbExplorerPanel.java:887)

[Thomas Kellerer]

Hi,

the error that causes the connection to be closed, is the retrieval of the column remarks through the fn_listextendedproperty() function.

This is using the following SQL Statement:

SELECT objname,
cast(value as varchar(8000)) as value
FROM ::fn_listextendedproperty ('MS_DESCRIPTION','user', 'dbo', 'TABLE', null, null, null);

I'm surprised this doesn't work on your installation (as it does work for me).

However, that can be disabled by running

WbSetConfig workbench.db.microsoft_sql_server.remarks.column.retrieve=false
WbSetConfig workbench.db.microsoft_sql_server.remarks.object.retrieve=false

(That will disable retrieving column remarks for columns and tables)


Unfortunately the cause of the other error can not be disabled, because the code (incorrectly) assumes that retrieving rule definitions through sys.all_objects works for SQL Server 2005. But that should not cause the connection problems it is simply reported in the logfile. I will fix that.

Regards
Thomas



Josef Hopfgartner, 27.03.2013 15:26:

> Hi again,
>
> here is the workbench.log
>
> 2013-03-27 13:49 INFO =================== Log started ===================
> 2013-03-27 13:49 INFO Using configdir: /home/NMG/hopfgartner/samba/marcher02/sqlworkbench
> 2013-03-27 13:49 INFO Starting SQL Workbench/J, Build 114.6 (2013-03-24 21:18)
> 2013-03-27 13:49 INFO Java version=1.7.0_15, java.home=/usr/lib/jvm/java-7-openjdk-amd64/jre, vendor=Oracle Corporation, name=OpenJDK 64-Bit Server VM
> 2013-03-27 13:49 INFO Operating System=Linux, version=3.5.0-27-generic, platform=amd64
> 2013-03-27 13:49 INFO Starting DeadlockMonitor
> 2013-03-27 13:49 INFO Creating new connection for [{Graz Klagenfurt}/mds_klu] for driver=net.sourceforge.jtds.jdbc.Driver
> 2013-03-27 13:49 INFO Adding ClassLoader URL=file:/usr/share/java/jtds.jar
> 2013-03-27 13:49 INFO Using DBID=microsoft_sql_server
> 2013-03-27 13:49 INFO Using quote escape character: "
> 2013-03-27 13:49 INFO Using search string escape character: \

> 2013-03-27 13:49 INFO *Connected to: [Microsoft SQL Server], Database version: [08.00.0760], Driver version: [1.3.0], JDBC Version: [3.0], ID: [Wb1-WbExp-1] *

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Josef Hopfgartner]

Your "WbSetConfig" statements worked like a charm.

Cheers!