getting java.nio.BufferUnderflowException error

[tpwu]

Hi, I wonder if anyone has an experience on error
BufferUnderflowException connecting to Vertica doing insert and
update. However, doing select is fine with no errors.

I am running SQL Workbench (Build 111, 2011-09-18 12:21) and (Java
version 1.6.0_22-b04) on a 64-bit Windows 7.

Any suggestions or ideas are appreciated.

Thanks

TP

[Thomas Kellerer]

Hi,

this seems rather strange and I would suspect a problem in the JDBC driver.
Can you send the workbench.log file to the support email, then I'll have a look at this.

Regards
Thomas

tpwu, 08.11.2011 20:49:

[tpwu]

I have sent the log file to the support email. Please let me know if
you didn't get it.

Thanks

On Nov 9, 3:15 am, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

OK, it seems this is indeed a driver problem.

As far as I can tell, the driver "lies" about what it is able to return after running a DML statement. I can implement a workaround for this.

If you have the possibilitiy, you should also contact Vertica about their driver. It seems that driver is lacking basic JDBC compliance.
(I also got a report from another user reporting that the driver wouldn't return column information for views in the DbExplorer)

Unfortunately they do not offer a free download (not even a trial), so I'm limited in what I can do...

Regards
Thomas

tpwu, 15.11.2011 16:23:

[tpwu]

Just checked their website, they seem offering free trial at
http://www.vertica.com/evaluate/. Interestingly, other jdbc tools,
e.g. execute query, and our internal applications do not experience
the issue.


On Nov 15, 11:03 am, Thomas Kellerer <google-gro...@sql-workbench.net>

[Thomas Kellerer]

Hi,

> Just checked their website, they seem offering free trial at
> http://www.vertica.com/evaluate/

There is no direct download there. When you submit the contact form you get an email from a Sales person which in turn wants to contact you via phone (most probably to give you all that sales gibberish). As much as I can understand this from a commercial point of view, I do not want to go through a "sales pitch" just to test it.

> Interestingly, other jdbc tools, e.g. execute query, and our internal applications do not experience the issue.

It is a combination of things.

SQL Workbench supports DML statements that return a result set (e.g. in PostgreSQL "DELETE FROM foo WHERE bar = 1 RETURN *), therefor it does not use Statement.executeUpdate() to run them, but the more generic Statement.execute().

In order to process any result set returned by the driver I'm relying on three different things: the boolean value returned by Statement.execute(), and the values returned by Statement.getUpdateCount() and Statement.getMoreResults().

The logic I'm applying is basically the one that is described in the Javadocs for Statement.getMoreResults:
http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#getMoreResults%28%29

There are no more results when the following is true:
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

So what I'm doing (more or less) is the following:

boolean hasResult = stmt.execute("delete from foo where id = 1");
if (hasResult)
{
ResultSet rs = stmt.getResultSet();
while (rs.next())
{
// process row data
}
rs.close();
}
int updateCount = stmt.getUpdateCount();
boolean moreResults = stmt.getMoreResults();
if (moreResults || updateCount != -1)
{
if (updateCount != -1)
{
// increase counter for updates
}
else
{
// does have another result
ResultSet rs = stmt.getResultSet();
while (rs.next())
{
// process row data, this is where the Vertica driver bombs out.
}
rs.close();
}
updateCount = stmt.getUpdateCount();
moreResults = stmt.getMoreResults();
}

as far as I can tell, the Vertica driver claims that there are more results (returning true from the getMoreResults call) but than bombs out when I try to process the result set that is obtained using getResultSet()

If you can compile and run Java programs, I'd be glad if you can run a sample program that works like the above and verify that this combination of calls does not work.
This would not be the first driver which does not comply with the contract for getMoreResults() and getUpdateCount()...

I could implement a workaround to use executeUpdate() instead, but that only makes sense if this is really the cause (actually if that is the case, Vertica should fix their driver ;) )

Regards
Thomas

[Thomas Kellerer]

I'm getting old, I keep forgetting things....

> I could implement a workaround to use executeUpdate() instead

That option is already there ;)

Can you please set the following property in workbench.settings

workbench.db.vertica_database.dml.supports.results=false

and try to run the statement again?

Please close the SQL Workbench before editing the .settings file. You can add the line anywhere in the file.

Regards
Thomas

Thomas Kellerer, 16.11.2011 17:24:

[tpwu]

Still got the same error after add the line in the setting file.

On Nov 16, 11:29 am, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

> I'm getting old, I keep forgetting things....
>
> > I could implement a workaround to use executeUpdate() instead
>
> That option is already there ;)
>
> Can you please set the following property in workbench.settings
>
>     workbench.db.vertica_database.dml.supports.results=false
>
> and try to run the statement again?
>
> Please close the SQL Workbench before editing the .settings file. You can add the line anywhere in the file.
>
> Regards
> Thomas
>
> Thomas Kellerer, 16.11.2011 17:24:
>
>
>
>
>
>
>
> > Hi,
>
> >> Just checked their website, they seem offering free trial at
> >>http://www.vertica.com/evaluate/
>
> > There is no direct download there. When you submit the contact form you get an email from a Sales person which in turn wants to contact you via phone (most probably to give you all that sales gibberish). As much as I can understand this from a commercial point of view, I do not want to go through a "sales pitch" just to test it.
>
> >> Interestingly, other jdbc tools, e.g. execute query, and our internal applications do not experience the issue.
>
> > It is a combination of things.
>
> > SQL Workbench supports DML statements that return a result set (e.g. in PostgreSQL "DELETE FROM foo WHERE bar = 1 RETURN *), therefor it does not use Statement.executeUpdate() to run them, but the more generic Statement.execute().
>
> > In order to process any result set returned by the driver I'm relying on three different things: the boolean value returned by Statement.execute(), and the values returned by Statement.getUpdateCount() and Statement.getMoreResults().
>
> > The logic I'm applying is basically the one that is described in the Javadocs for Statement.getMoreResults:

> >http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#...

[Thomas Kellerer]

Could you send me the logfile from that try?

Regards
Thomas

tpwu, 16.11.2011 22:24:

[tpwu]

Please see below log. I was running a delete statement. Please let
me know if you need more.

Thanks

2011-11-17 14:52 INFO =================== Log started
===================
2011-11-17 14:52 INFO Using configdir: C:\Users\tpwu\.sqlworkbench
2011-11-17 14:52 INFO Starting SQL Workbench/J, Build 111 (2011-09-18
12:21)
2011-11-17 14:52 INFO Java version=1.6.0_22, java.home=C:\Program
Files\Java\jre6, vendor=Sun Microsystems Inc.
2011-11-17 14:52 INFO Operating System=Windows 7, version=6.1,
platform=amd64
2011-11-17 14:52 INFO Creating new connection for [{vertica}/vertica
dev/test] for driver=com.vertica.Driver
2011-11-17 14:52 INFO Adding ClassLoader URL=file:/C:/Users/tpwu/
Documents/Driver/vertica_4.1.7_jdk_5.jar
2011-11-17 14:52 INFO Using DBID=vertica_database
2011-11-17 14:52 INFO Connected to: [Vertica Database], Database
version: [v4.1.9-0], Driver version: [Vertica JDBC Driver 4.1.7], JDBC
Version: [3.0], ID: [Wb1-1]
2011-11-17 14:52 ERROR Error during retrieve
java.nio.BufferUnderflowException
java.nio.BufferUnderflowException
at java.nio.Buffer.nextGetIndex(Unknown Source)
at java.nio.HeapByteBuffer.getInt(Unknown Source)
at com.vertica.core.VectorTuple.getTupleFromBuff(Unknown Source)
at com.vertica.core.VectorTuple.elementAt(Unknown Source)
at com.vertica.jdbc2.AbstractJdbc2ResultSet.next(Unknown Source)
at workbench.storage.DataStore.initData(DataStore.java:1150)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:431)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:315)
at
workbench.sql.commands.UpdatingCommand.execute(UpdatingCommand.java:
103)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
387)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2882)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1853)
at workbench.gui.sql.SqlPanel$12.run(SqlPanel.java:1820)

2011-11-17 14:52 ERROR delete from dev.stg_data_stage
java.nio.BufferUnderflowException
java.sql.SQLException: java.nio.BufferUnderflowException
at workbench.storage.DataStore.initData(DataStore.java:1195)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:431)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:315)
at
workbench.sql.commands.UpdatingCommand.execute(UpdatingCommand.java:
103)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
387)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2882)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1853)
at workbench.gui.sql.SqlPanel$12.run(SqlPanel.java:1820)



On Nov 17, 2:45 am, Thomas Kellerer <google-gro...@sql-workbench.net>

[Thomas Kellerer]

Hmm, that stacktrace would be generated with

workbench.db.vertica_database.dml.supports.results=true

but not if you added

workbench.db.vertica_database.dml.supports.results=false

Can you please verify that that property is in your workbench.settings

The exact location of the file is shown in the options and about dialog

Please make sure to stop the application before editing the file

Regards
Thomas

[tpwu]

Please see a section of my settings below. I quit the workbench and
add the line afterwards. Is that right?

====================workbench.settings====================
.....
workbench.datapumper.window.1080x1920.x=-1820
workbench.datapumper.window.1080x1920.y=547

workbench.db.createpkname=true
workbench.db.oracle.fixdatetype=false
workbench.db.vertica_database.dml.supports.results=false

workbench.dbexplorer.allow.alter=false
workbench.dbexplorer.instantfilter=false
workbench.dbexplorer.mainwindow=true
workbench.dbexplorer.remember.columnorder=false
....
==========================================================
On Nov 17, 3:30 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

Sorry, my fault. You need the current dev build (111.6) for this.

Regards
Thomas

[tpwu]

Please see below log with error. I upgraded to 111.6 build and also
the vetica jdbc driver to 4.1.19.

2011-11-17 17:00 INFO =================== Log started
===================
2011-11-17 17:00 INFO Using configdir: C:\Users\tpwu\.sqlworkbench
2011-11-17 17:00 INFO Starting SQL Workbench/J, Build 111.6
(2011-11-14 21:42)
2011-11-17 17:00 INFO Java version=1.6.0_22, java.home=C:\Program

Files\Java\jre6, vendor=Sun Microsystems Inc.

2011-11-17 17:00 INFO Operating System=Windows 7, version=6.1,
platform=amd64
2011-11-17 17:01 INFO Creating new connection for [{vertica}/vertica
dev/test] for driver=com.vertica.Driver
2011-11-17 17:01 INFO Adding ClassLoader URL=file:/C:/Users/tpwu/
Documents/Driver/vertica_4.1.19_jdk_5.jar
2011-11-17 17:01 INFO Using DBID=vertica_database
2011-11-17 17:01 DEBUG Identifier quote character obtained from
driver: "
2011-11-17 17:01 DEBUG Using configured tabletypes: [TABLE]
2011-11-17 17:01 INFO Connected to: [Vertica Database], Database
version: [v4.1.9-0], Driver version: [Vertica JDBC Driver 4.1.19],

JDBC Version: [3.0], ID: [Wb1-1]

2011-11-17 17:01 ERROR Error during retrieve

java.nio.BufferUnderflowException
java.nio.BufferUnderflowException
at java.nio.Buffer.nextGetIndex(Unknown Source)
at java.nio.HeapByteBuffer.getInt(Unknown Source)
at com.vertica.core.VectorTuple.getTupleFromBuff(Unknown Source)
at com.vertica.core.VectorTuple.elementAt(Unknown Source)
at com.vertica.jdbc2.AbstractJdbc2ResultSet.next(Unknown Source)

at workbench.storage.DataStore.initData(DataStore.java:1162)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:465)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:335)

at
workbench.sql.commands.UpdatingCommand.execute(UpdatingCommand.java:
103)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
387)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2883)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1854)
at workbench.gui.sql.SqlPanel$12.run(SqlPanel.java:1821)

2011-11-17 17:01 ERROR delete from dev.stg_data_stage
java.nio.BufferUnderflowException
java.sql.SQLException: java.nio.BufferUnderflowException
at workbench.storage.DataStore.initData(DataStore.java:1207)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:465)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:335)

at
workbench.sql.commands.UpdatingCommand.execute(UpdatingCommand.java:
103)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
387)

at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2883)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1854)
at workbench.gui.sql.SqlPanel$12.run(SqlPanel.java:1821)


On Nov 17, 4:51 pm, Thomas Kellerer <google-gro...@sql-workbench.net>

[Thomas Kellerer]

OK, could you try 111.7 please?

Regards
Thomas

[tpwu]

Wonderful!!! It works with 111.7. Both delete and insert works
beautifully. Many thanks on this.


On Nov 17, 5:10 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote: