We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


pyodbc HY000 error if commit() after complex SELECT — Vertica Forum

pyodbc HY000 error if commit() after complex SELECT

matthewcornellmatthewcornell
in General Discussion
Hi folks. I've been chasing down this error for a week:
> pyodbc.Error: ('HY000', '[HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution:  (20) (SQLFetch)')
After lots of trial and error, the key is a combination of this particular SELECT query (simpler ones don't error) plus a call to commit() after curs.execute(). I'm really curious to hear what you think is going on. Below (followed by configuration information) is the trimmed-down example.

Thanks in advance!


* python file:
import pyodbc

query = '''
SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val, relVarTable3.val, relVarTable4.val
FROM (SELECT id FROM B) relVarTable0
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.X) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.X IS NOT NULL
GROUP BY B1.id) relVarTable1
    ON relVarTable0.id = relVarTable1.id
LEFT JOIN
(SELECT B1.id AS id, B1.Y AS val
FROM B B1
WHERE B1.Y IS NOT NULL ) relVarTable2
    ON relVarTable0.id = relVarTable2.id
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.W) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.W IS NOT NULL
GROUP BY B1.id) relVarTable3
    ON relVarTable0.id = relVarTable3.id
LEFT JOIN
(SELECT B1.id AS id, AVG(C1.Z) AS val
FROM B B1, BC BC1, C C1
WHERE B1.id = BC1.B_id AND C1.id = BC1.C_id AND C1.Z IS NOT NULL
GROUP BY B1.id) relVarTable4
    ON relVarTable0.id = relVarTable4.id;
'''

conn = pyodbc.connect('DSN=vertica_kdl_dsn;UserName=__;Password=__)
curs = conn.cursor()
curs.execute('SET search_path TO "$user", rcd_test, public, v_catalog, v_monitor, v_internal;')
curs.execute(query)
curs.commit()           # comment out to get no error
rows = curs.fetchall()  # if above commit called: pyodbc.Error 'HY000'
curs.close()
* versions
Python: 3.3.2 [GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux
pyodbc: 3.0.7 (pyodbc.SQL_DBMS_VER: 18, pyodbc.SQL_DM_VER: 171, pyodbc.SQL_DRIVER_ODBC_VER: 77, pyodbc.SQL_DRIVER_VER: 7, pyodbc.SQL_ODBC_VER: 10)
unixODBC: 2.2.14
vertica: Vertica Analytic Database v7.0.0-0
OS: Linux version 2.6.32-279.14.1.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.6 20120305 (Red Hat 4.4.6-4) (GCC) )

Comments

  • matthewcornellmatthewcornell
    More info: odbc configuration files

    * /etc/odbcinst.ini
    [PostgreSQL]
    ...

    [MySQL]
    ...

    [vertica_driver]
    Description     = Vertica 7 ODBC driver
    Driver          = /opt/vertica/lib/libverticaodbc.so
    Driver64        = /opt/vertica/lib64/libverticaodbc.so
    FileUsage       = 1

    [ODBC]
    Threading       = 1
    Trace           = 1
    TraceFile       = /tmp/odbctrace.log
    Debug           = 1
    DebugFile       = /tmp/odbcdebug.log
    * /etc/odbc.ini
    [ODBC Data Sources]
    vertica_kdl_dsn = kdl database on HP Vertica 7


    [vertica_kdl_dsn]
    Driver = vertica_driver
    Description = ODBC Driver DSN for kdl master database
    Servername = compute-0-0
    Port = 5433
    Database = kdl
    Locale = en_US
    * /etc/vertica.ini
    [Driver]
    DriverManagerEncoding = UTF-16
    ODBCInstLib = /usr/lib64/libodbcinst.so
    ErrorMessagesPath = /opt/vertica/lib64
    LogLevel = 4
    LogPath = /tmp


  • matthewcornellmatthewcornell
    More info: logs:

    * odbctrace.log (just the error lines)
    [ODBC][26428][1391447285.717220][SQLGetTypeInfo.c][164]
            Entry:
                            Statement = 0x2073bb0
                    Data Type = SQL_VARCHAR
    [ODBC][26428][1391447285.717240][SQLGetTypeInfo.c][186]Error: 24000
    [ODBC][26428][1391447285.717270][SQLGetTypeInfo.c][164]
            Entry:
                            Statement = 0x2073bb0
                            Data Type = Unknown(-9)
    [ODBC][26428][1391447285.717289][SQLGetTypeInfo.c][186]Error: 24000
    [ODBC][26428][1391447285.717316][SQLGetTypeInfo.c][164]
                    Entry:
                    Statement = 0x2073bb0
                            Data Type = SQL_BINARY
    [ODBC][26428][1391447285.717335][SQLGetTypeInfo.c][186]Error: 24000

    ...

    [ODBC][26428][1391447286.101023][SQLFetch.c][158]
            Entry:
                            Statement = 0x2075a30
    [ODBC][26428][1391447286.101356][SQLFetch.c][340]
                    Exit:[SQL_ERROR]
                    DIAG [HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution:

    [ODBC][26428][1391447286.101436][SQLGetDiagRec.c][739]
                    Entry:
                            Statement = 0x2075a30
                            Rec Number = 1
                            SQLState = 0x7fffdfd8c600
                    Native = 0x7fffdfd8c618
                            Message Text = 0x7fffdfd8c200
                            Buffer Length = 1023
                            Text Len Ptr = 0x7fffdfd8c61e
    [ODBC][26428][1391447286.101473][SQLGetDiagRec.c][776]
                    Exit:[SQL_SUCCESS]
                    SQLState = HY000
                            Native = 0x7fffdfd8c618 -> 20
                    Message Text = [[Vertica][VerticaDSII] (20) An error occurred during query execution: ]
    * vertica_odbc_conn_1.log
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLSetConnectAttr: Attribute: 115
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Database:kdl, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Description:ODBC Driver DSN for kdl master database, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Driver:vertica_driver, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: DSN:vertica_kdl_dsn, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Locale:en_US, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: PASSWORD:********, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Port:5433, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: Servername:compute-0-0, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: in: USERNAME:test, type 0
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out ColumnsAsChar
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out ConnSettings
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out DirectBatchInsert
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out DriverStringConversions
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out Label
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out ResultBufferSize
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out SSLCertFile
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out SSLKeyFile
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out SSLMode
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: out TransactionIsolation
    Feb 03 12:15:26 INFO  4105717504 VConnection::UpdateConnectionSettings: type 0, value: ?
    Feb 03 12:15:26 INFO  4105717504 VConnection::Connect: Connect to host compute-0-0:5433, db kdl
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 77
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLSetConnectAttr: Attribute: 102
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 77
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 10002
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 111
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10010
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10011
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10012
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10013
    Feb 03 12:15:26 INFO  4105717504 Connection::ExecuteCatalogFunction: SQLGetTypeInfo
    Feb 03 12:15:26 INFO  4105717504 VMetadataSource::Execute: out_metadataQuery: select * from (select CASE WHEN (UPPER(type_name) = 'CHAR') THEN 'CHAR' WHEN (UPPER(type_name) = 'VARCHAR') THEN 'VARCHAR' ELSE type_name END as data_type_name, CASE WHEN (odbc_type = 1) THEN -8 WHEN (odbc_type = 12) THEN -9 WHEN ( odbc_subtype != 0 ) THEN odbc_subtype ELSE odbc_type END as data_type, column_size, case when type_id not in (5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 114, 115, 116, 117) then null when odbc_type in (-5, 2, 8) then null else E'\'' end as literal_prefix,  case when type_id not in (5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 114, 115, 116, 117) then null when odbc_type in (-5, 2, 8) then null else E'\'' end as literal_suffix, creation_parameters as create_param, 1 as nullable, 1 as case_sensitive, 3 as searchable, 0 as unsigned_attribute, 0 as fixed_prec_scale, 0 as auto_unique, CASE WHEN (UPPER(type_name) = 'CHAR') THEN 'CHAR' WHEN (UPPER(type_name) = 'VARCHAR') THEN 'VARCHAR' ELSE type_name END as local_type_name, min_scale as minimum_scale, max_scale as maximum_scale, CASE WHEN (odbc_type = 1) THEN -8 WHEN (odbc_type = 12) THEN -9 ELSE odbc_type END as sql_data_type, CASE WHEN (odbc_type = 10) THEN (odbc_subtype-100) WHEN (odbc_type = 9) THEN (odbc_subtype-90) ELSE 0 END as sql_datetime_sub, 10 as num_prec_radix, NULL as interval_precision from v_catalog.types order by odbc_type, type_name ) as vmd where data_type = E'93'
    Feb 03 12:15:26 INFO  4105717504 VMetadataSource::Execute: Command status: SELECT, 2 tuples 19 fields
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10010
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10011
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10012
    Feb 03 12:15:26 INFO  4105717504 Statement::SQLGetStmtAttrW: Attribute: 10013
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Original Query: SET search_path TO "$user", rcd_test, public, v_catalog, v_monitor, v_internal;
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Query is issued as ExecDirect
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Query: SET search_path TO "$user", rcd_test, public, v_catalog, v_monitor, v_internal;
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x1276b00_0
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Original Query:
    SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val, relVarTable3.val, relVarTable4.val
    FROM (SELECT id FROM B) relVarTable0
    LEFT JOIN
    (SELECT B1.id AS id, AVG(A1.X) AS val
    FROM B B1, AB AB1, A A1
    WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.X IS NOT NULL
    GROUP BY B1.id) relVarTable1
        ON relVarTable0.id = relVarTable1.id
    LEFT JOIN
    (SELECT B1.id AS id, B1.Y AS val
    FROM B B1
    WHERE B1.Y IS NOT NULL ) relVarTable2
        ON relVarTable0.id = relVarTable2.id
    LEFT JOIN
    (SELECT B1.id AS id, AVG(A1.W) AS val
    FROM B B1, AB AB1, A A1
    WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.W IS NOT NULL
    GROUP BY B1.id) relVarTable3
        ON relVarTable0.id = relVarTable3.id
    LEFT JOIN
    (SELECT B1.id AS id, AVG(C1.Z) AS val
    FROM B B1, BC BC1, C C1
    WHERE B1.id = BC1.B_id AND C1.id = BC1.C_id AND C1.Z IS NOT NULL
    GROUP BY B1.id) relVarTable4
        ON relVarTable0.id = relVarTable4.id;

    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Query is issued as ExecDirect
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Query: SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val, relVarTable3.val, relVarTable4.val FROM (SELECT id FROM B) relVarTable0 LEFT JOIN (SELECT B1.id AS id, AVG(A1.X) AS val FROM B B1, AB AB1, A A1 WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.X IS NOT NULL GROUP BY B1.id) relVarTable1 ON relVarTable0.id = relVarTable1.id LEFT JOIN (SELECT B1.id AS id, B1.Y AS val FROM B B1 WHERE B1.Y IS NOT NULL ) relVarTable2 ON relVarTable0.id = relVarTable2.id LEFT JOIN (SELECT B1.id AS id, AVG(A1.W) AS val FROM B B1, AB AB1, A A1 WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.W IS NOT NULL GROUP BY B1.id) relVarTable3 ON relVarTable0.id = relVarTable3.id LEFT JOIN (SELECT B1.id AS id, AVG(C1.Z) AS val FROM B B1, BC BC1, C C1 WHERE B1.id = BC1.B_id AND C1.id = BC1.C_id AND C1.Z IS NOT NULL GROUP BY B1.id) relVarTable4 ON relVarTable0.id = relVarTable4.id;
    Feb 03 12:15:26 INFO  4105717504 VDataEngine::Prepare: Initializing prepared statement: _PLAN0x1276c40_1
    Feb 03 12:15:26 INFO  4105717504 VPQResultSet::SetupColumns: column type is SQL_BIGINT
    Feb 03 12:15:26 INFO  4105717504 VPQResultSet::SetupColumns: column type is SQL_FLOAT
    Feb 03 12:15:26 INFO  4105717504 VPQResultSet::SetupColumns: column type is SQL_FLOAT
    Feb 03 12:15:26 INFO  4105717504 VPQResultSet::SetupColumns: column type is SQL_FLOAT
    Feb 03 12:15:26 INFO  4105717504 VPQResultSet::SetupColumns: column type is SQL_FLOAT
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 23
    Feb 03 12:15:26 INFO  4105717504 Connection::SQLGetInfoW: InfoType: 24
    Feb 03 12:15:26 ERROR 4105717504 Statement::SQLFetch: [Vertica][VerticaDSII] (20) An error occurred during query execution:
    * vertica_driver.log
    Feb 03 12:15:26 INFO  4105717504 Environment::SQLSetEnvAttr: Attribute: 200
    Feb 03 12:15:26 INFO  4105717504 Environment::SQLGetEnvAttr: Attribute: 200
    Feb 03 12:15:26 INFO  4105717504 VEnvironment::CreateConnection: ODBC Version is: 3
  • [Deleted User][Deleted User]
    Try:

    1) add ResultBufferSize=0 in odbc.ini under the DSN [vertica_kdl_dsn] section

    2) remove the Locale = en_US line  (not related to the error, but it is not right)
  • matthewcornellmatthewcornell
    That seems to have fixed it - thanks a ton. Q: How did you know that this was a possible cause of such a generic error?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...