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 ...