question Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager] in Support Questions

Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Mon, 05 Jul 2021 19:08:30 GMT

I'm connecting to Hive using Cloudera's ODBC driver version 2.6.9 and I'm getting a weird behavior that ruins the user experience. Any help to solve or debug this problem would be greatly appreciated.

I'm using DBeaver to connect to it. All my databases just display a few of it's tables. Here is an example. When I open DBeaver, The dabase just shows the table `localizacao`:

This database has 3 tables. If I run the command show tables from raw_aneel, DBeaver interface will display:

The error message in english would be:




    org.jkiss.dbeaver.model.exec.DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

Here is the complete stack trace displayed in the Error Log. The error message above is in Brazilian Portuguese:

org.jkiss.dbeaver.model.exec.DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager] Comprimento inválido de cadeia de caracteres ou de buffer
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:55)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:125)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:718)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:541)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:441)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:428)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:813)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3280)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4624)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: [Microsoft][ODBC Driver Manager] Comprimento inválido de cadeia de caracteres ou de buffer
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3914)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5697)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:353)
at sun.jdbc.odbc.JdbcOdbcResultSet.getObject(JdbcOdbcResultSet.java:1677)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCResultSetImpl.getObject(JDBCResultSetImpl.java:627)
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCStringValueHandler.fetchColumnValue(JDBCStringValueHandler.java:52)
at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49)
... 14 more

Before this stack trace there is a message `Can't read column 'tab_name' value`:

Any Idea how to debug this?

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Mon, 05 Jul 2021 19:52:41 GMT

Weird. If I select a table some of the values come with the S1090 error:

same query above return some valid values:

But if I wait some minutes and try the query again. It works!

Should I try some driver configuration?

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

asish — Tue, 06 Jul 2021 05:56:40 GMT

Please provide driver DEBUG

To enable logging in the driver please modify the odbc.ini file and under the [Driver] section set LogLevel to 6 and LogPath to point to a writeable directory where you would like to driver to create the log files in. For example:
[Driver]
LogLevel=6
LogPath=/tmp

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Tue, 06 Jul 2021 20:34:27 GMT

Thanks, I'm trying to do it. I believe it is the file

C:\Windows\ODBC.INI

and that I must add these lines at the end of it:

[Driver]
LogLevel=6
LogPath=/tmp

I don't have write permission to this file, but I'll ask someone to do it.

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Wed, 07 Jul 2021 23:31:27 GMT

This is very weird.

If I turn the log trace on, it works.

If I turn the log at debug level or higher, it fails.

Before sending the log files, here is some context.

We don't have admin rights to our Windows Machines. The odbc drivers are installed as Admin by a script from a internal "App Store". After installing it, a bat file is executed to create the odbc data sources. The bat file basically executes this command:

%windir%\syswow64\odbcconf configdsn "Cloudera ODBC Driver for Apache Hive" "DSN=Datalake|DESCRIPTION=Driver datalake|HiveServerType=2|ServiceDiscoveryMode=ZooKeeper|ZKNamespace=hiveserver2|Host=zookeeper01:2181,zookeeper02:2181,zookeeper03:2181|Port=10000|Schema=default|AuthMech=1|KrbRealm=S.NET|KrbServiceName=hive|ServicePrincipalCanonicalization=0|KrbHostFQDN=_HOST|GetTablesWithQuery=1|InvalidSessionAutoRecover=0|AutoReconnect=1"

After the install, I open the Windows ODBC Admin and try to change the log level in "my datasource" -> Configure button -> Logging Options the option isn't persisted. If I try to configure it again the log level is off.

I also can't edit the file "C:\Windows\odbc.ini".

The sysadm gave admin permissions to my user. I edited the odbc.ini to add the recommended log config:

[Driver]
LogLevel=6
LogPath=C:\temp

but it didn't saved any log info.

So, with admin powers, I went to the ODBC admin, confligured the log level to trace at the "Logging Options" above and it worked.

After editing the log level, these lines were automatically added to the ODBC.INI file:

[Datalake]
Driver32=C:\Program Files\Cloudera ODBC Driver for Apache Hive\lib\ClouderaHiveODBC64.dll

These lines were not present before.

The admin powers were removed from my user, but now I can change the "Logging Options" and they are persisted. ¯\_(ツ)_/¯

And now the weird behavior happens. If the log level is TRACE it works fine, if it is DEBUG or higher, it fails.

Here how I test it:

I open dbeaver
I expand the Datalake connection so it connects. It opens a tree with a node HIVE below it
I expand the HIVE tree and it lists all my databases names. So far, so fine. This always work.
Now if I open a specific database, expand it and click to expand the table names, the debug version expands just the first table, the trace version display all the 3 tables.

Here are the logs. The one with DEBUG ON and the one with TRACE ON. Each has 2 connections, since Dbeaver opens a separate connection for the metadata.

Any help is greatly appreciated.

I'll try to attach the log files using another browser in a reply.

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Wed, 07 Jul 2021 23:36:09 GMT

Sorry, I don't know how to add files to this thread. The following types were refused: log, txt, and zip. I'd send it by email if someone asks.

Re: Hive ODBC fails to list all tables and returns errors: DBCException: SQL Error [S1090]: [Microsoft][ODBC Driver Manager]

PauloNeves — Mon, 15 Aug 2022 17:19:29 GMT

Te problem improved upgrading the driver version. It still persists, but is rarer.