python3 + rpy + pyodb + vertica 7 = Linux segfault

Moderator: NorbertKrupa

Post Reply
matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by matthewcornell » Fri Jan 17, 2014 6:37 pm

Hi Folks,

I've discovered a python-vertica stack configuration that causes a segmentation fault on our Linux cluster. I'd really appreciate your help in deciding how to proceed with a solution. I've attached as much information as I can to help figure this out.

The summary: When running in a virtualenv, if I import rpy2.objects then pyodb and then try to execute certain queries, I get the segfault. Interestingly, if I swap the import order, it seems to work OK (!) Testing on previous versions of pyodb have the segfault happening at connect() instead of execute().

Thanks very much,

matt


** commands to install the stack via virtualenv

/share/apps/python3/bin/python3.3 ~/bin/virtualenv.py --no-site-packages ~/virtualenvs/vertica-test
source ~/virtualenvs/vertica-test/bin/activate

pip install http://pyodbc.googlecode.com/files/pyodbc-3.0.7.zip
-> Successfully installed pyodbc

pip install rpy2
-> Successfully installed rpy2


** commands in interpreter to reproduce the segfault

# NB: bad username or password -> segfault at connect()
# NB: if the import order is switched -> NO segfault

[cornell@wright ~]$ source ~/virtualenvs/vertica-test/bin/activate

(vertica-test)[cornell@wright ~]$ python3.3
Python 3.3.2 (default, May 16 2013, 11:31:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import rpy2.robjects
>>> import pyodbc
>>> conn = pyodbc.connect('DSN=vertica_kdl_dsn;UID=xx;PWD=xx')
>>> curs = conn.cursor()
>>> curs.execute('DROP TABLE IF EXISTS foo')
Segmentation fault


(vertica-test)[cornell@wright ~]$ python3.3
Python 3.3.2 (default, May 16 2013, 11:31:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> import rpy2.robjects
>>> conn = pyodbc.connect('DSN=vertica_kdl_dsn;UID=xx;PWD=xx')
>>> curs = conn.cursor()
>>> curs.execute('DROP TABLE IF EXISTS foo')
<pyodbc.Cursor object at 0x7f8508b5fe10>
>>>


** versions

Python 3.3.2 (default, May 16 2013, 11:31:48)
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux


>>> rpy2.__version__
'2.3.8'


>>> pyodbc.version
'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


$ isql --version
unixODBC 2.2.14


/opt/vertica/lib64:
lrwxrwxrwx 1 root root 23 Dec 18 09:43 libverticaodbc.so.7.0 -> libverticaodbc.so.7.0.0

[dbadmin@compute-0-0 ~]$ /opt/vertica/bin/adminTools
Vertica Analytic Database 7.0.0-0 Administration Tools


[cornell@wright ~]$ uname -a
Linux wright.cs.umass.edu 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
[cornell@wright ~]$ cat /proc/version
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) ) #1 SMP Tue Nov 6 23:43:09 UTC 2012
[cornell@wright ~]$


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


*** odbc trace files: see attached (wright-odbc-trace-2014-01-17)

/tmp/odbctrace.log
/tmp/vertica_odbc_conn_1.log
/tmp/vertica_driver.log
Attachments
odbctrace.log
(5.31 KiB) Downloaded 154 times
vertica_odbc_conn_1.log
(5.78 KiB) Downloaded 154 times
vertica_driver.log
(236 Bytes) Downloaded 160 times

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by id10t » Sat Jan 18, 2014 8:04 am

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 9:16 pm, edited 1 time in total.

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by matthewcornell » Tue Jan 21, 2014 2:14 pm

sKwa wrote:try this:...
Hi sKwa. Thank you for your reply. My output:

Code: Select all

>>> import rpy2.robjects
>>> import pyodbc
>>> conn = pyodbc.connect('DSN=vertica_kdl_dsn;UserName=xx;Password=xx')
>>> for attr in vars(pyodbc):
...     try:
...         value = conn.getinfo(getattr(pyodbc, attr))
...         print('{:<40s} | {}'.format(attr, value))
...     except:
...         pass
... 
SQL_SQL_CONFORMANCE                      | 1
SQL_TXN_ISOLATION_OPTION                 | 10
SQL_PROCEDURE_TERM                       | function
SQL_DROP_TRANSLATION                     | 0
SQL_CATALOG_USAGE                        | 0
SQL_SCHEMA_USAGE                         | 21
SQL_NULLABLE                             | 0
SQL_STATIC_CURSOR_ATTRIBUTES2            | 0
SQL_STATIC_CURSOR_ATTRIBUTES1            | 0
SQL_MAX_COLUMNS_IN_TABLE                 | 1600
SQL_GETDATA_EXTENSIONS                   | 15
SQL_INTERVAL_MONTH                       | 0
SQL_LIKE_ESCAPE_CLAUSE                   | True
SQL_TIMEDATE_DIFF_INTERVALS              | 511
SQL_ROW_UPDATES                          | False
SQL_MAX_ROW_SIZE                         | 32000000
SQL_INTERVAL_YEAR                        | 1600
SQL_XOPEN_CLI_YEAR                       | 1995
SQL_INTERVAL_SECOND                      | 0
SQL_INTERVAL_MINUTE                      | 0
SQL_ACTIVE_ENVIRONMENTS                  | 0
SQL_MAX_INDEX_SIZE                       | 0
SQL_TIMEDATE_FUNCTIONS                   | 1998847
SQL_ALTER_TABLE                          | 62312
SQL_CREATE_TABLE                         | 1045
SQL_INTERVAL_DAY_TO_MINUTE               | 511
SQL_SCOPE_SESSION                        | vertica_kdl_dsn
SQL_SQL92_REVOKE                         | 13744
SQL_CREATE_TRANSLATION                   | 0
SQL_MAX_BINARY_LITERAL_LEN               | 0
SQL_PARAM_INPUT                          | 0
SQL_PARAM_ARRAY_ROW_COUNTS               | 1
SQL_COLUMN_ALIAS                         | True
SQL_SQL92_ROW_VALUE_CONSTRUCTOR          | 3
SQL_SCROLL_OPTIONS                       | 1
SQL_SCOPE_TRANSACTION                    | 0
lowercase                                | 0
SQL_BATCH_ROW_COUNT                      | 2
SQL_DYNAMIC_CURSOR_ATTRIBUTES2           | 0
SQL_DYNAMIC_CURSOR_ATTRIBUTES1           | 0
SQL_CATALOG_NAME                         | False
SQL_SUBQUERIES                           | 31
SQL_SCHEMA_TERM                          | schema
SQL_UNION                                | 3
SQL_NEED_LONG_DATA_LEN                   | False
SQL_DATA_SOURCE_READ_ONLY                | False
SQL_DDL_INDEX                            | 0
SQL_NON_NULLABLE_COLUMNS                 | 1
SQL_MAX_IDENTIFIER_LEN                   | 128
SQL_QUOTED_IDENTIFIER_CASE               | 4
SQL_SERVER_NAME                          | compute-0-0
SQL_UNKNOWN_TYPE                         | 0
SQL_NUMERIC                              | vertica_kdl_dsn
SQLWCHAR_SIZE                            | vertica_kdl_dsn
SQL_CORRELATION_NAME                     | 2
SQL_DESCRIBE_PARAMETER                   | False
SQL_INTERVAL_HOUR_TO_MINUTE              | False
SQL_EXPRESSIONS_IN_ORDERBY               | True
SQL_OJ_CAPABILITIES                      | 127
SQL_TABLE_TERM                           | table
SQL_SQL92_FOREIGN_KEY_DELETE_RULE        | 0
SQL_CATALOG_LOCATION                     | 1
SQL_BOOKMARK_PERSISTENCE                 | 0
SQL_MAX_COLUMNS_IN_GROUP_BY              | 0
SQL_CREATE_DOMAIN                        | 0
SQL_INTEGRITY                            | False
SQL_DRIVER_NAME                          | verticaodbcw.so
SQL_TYPE_TIME                            | 0
SQL_DEFAULT_TXN_ISOLATION                | 2
SQL_INFO_SCHEMA_VIEWS                    | 0
SQL_TYPE_TIMESTAMP                       | 4
SQL_SCOPE_CURROW                         | 0
SQL_SQL92_NUMERIC_VALUE_FUNCTIONS        | 46
SQL_ORDER_BY_COLUMNS_IN_SELECT           | True
SQL_TYPE_DATE                            | 21
SQL_CREATE_ASSERTION                     | 0
SQL_INTERVAL_MINUTE_TO_SECOND            | True
SQL_NULLABLE_UNKNOWN                     | vertica_kdl_dsn
SQL_MULTIPLE_ACTIVE_TXN                  | True
SQL_STRING_FUNCTIONS                     | 9732063
SQL_CONCAT_NULL_BEHAVIOR                 | 0
SQL_MAX_CATALOG_NAME_LEN                 | 0
SQL_MAX_SCHEMA_NAME_LEN                  | 128
SQL_MAX_CONCURRENT_ACTIVITIES            | 0
SQL_MAX_CURSOR_NAME_LEN                  | 0
SQL_IDENTIFIER_CASE                      | 4
SQL_CURSOR_ROLLBACK_BEHAVIOR             | 2
SQL_DBMS_VER                             | 07.00.0000
SQL_DROP_VIEW                            | 1
SQL_CHAR                                 | 0
SQL_SEARCH_PATTERN_ESCAPE                | \
SQL_REAL                                 | 07.00.0000
SQL_GROUP_BY                             | 2
SQL_DATABASE_NAME                        | 
SQL_SQL92_GRANT                          | 3440
SQL_DROP_TABLE                           | 7
SQL_ACCESSIBLE_PROCEDURES                | False
SQL_BATCH_SUPPORT                        | 3
SQL_MAX_TABLES_IN_SELECT                 | 0
SQL_ODBC_VER                             | 03.52
SQL_COLLATION_SEQ                        | 
SQL_MAX_STATEMENT_LEN                    | 0
SQL_DRIVER_VER                           | 07.00.0000
SQL_DBMS_NAME                            | Vertica Database
SQL_PARAM_INPUT_OUTPUT                   | vertica_kdl_dsn
SQL_INTERVAL_HOUR_TO_SECOND              | 0
SQL_PARAM_TYPE_UNKNOWN                   | 0
SQL_PC_UNKNOWN                           | 0
SQL_DM_VER                               | 03.52.0002.0002
SQL_CONVERT_VARCHAR                      | 12317183
SQL_DRIVER_ODBC_VER                      | 03.52
SQL_PC_NOT_PSEUDO                        | 0
SQL_INTERVAL_YEAR_TO_MONTH               | 128
SQL_PC_PSEUDO                            | vertica_kdl_dsn
SQL_INTERVAL_DAY                         | False
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2      | 4097
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1      | 1
SQL_KEYSET_CURSOR_ATTRIBUTES2            | 4097
SQL_SQL92_STRING_FUNCTIONS               | 255
SQL_CATALOG_TERM                         | 
SQL_INDEX_KEYWORDS                       | 0
SQL_CREATE_COLLATION                     | 0
SQL_AGGREGATE_FUNCTIONS                  | 127
SQL_FLOAT                                | verticaodbcw.so
SQL_IDENTIFIER_QUOTE_CHAR                | "
SQL_MAX_TABLE_NAME_LEN                   | 128
SQL_MAX_USER_NAME_LEN                    | 128
SQL_SYSTEM_FUNCTIONS                     | 7
SQL_MAX_DRIVER_CONNECTIONS               | 0
SQL_SQL92_PREDICATES                     | 16007
SQL_ODBC_INTERFACE_CONFORMANCE           | 1
SQL_SQL92_FOREIGN_KEY_UPDATE_RULE        | 0
SQL_MAX_ASYNC_CONCURRENT_STATEMENTS      | 0
SQL_NUMERIC_FUNCTIONS                    | 16121855
SQL_INSERT_STATEMENT                     | 7
SQL_KEYSET_CURSOR_ATTRIBUTES1            | 4687
SQL_CREATE_SCHEMA                        | 3
SQL_SQL92_DATETIME_FUNCTIONS             | 7
SQL_DROP_SCHEMA                          | 7
SQL_FILE_USAGE                           | 0
SQL_MAX_COLUMN_NAME_LEN                  | 128
SQL_KEYWORDS                             | 
SQL_PROCEDURES                           | False
SQL_CREATE_CHARACTER_SET                 | 0
SQL_DROP_DOMAIN                          | 0
SQL_NULL_COLLATION                       | 1
SQL_STANDARD_CLI_CONFORMANCE             | 2
SQL_TXN_CAPABLE                          | 3
SQL_SQL92_VALUE_EXPRESSIONS              | 15
SQL_MAX_COLUMNS_IN_SELECT                | 0
pooling                                  | 0
SQL_MAX_CHAR_LITERAL_LEN                 | 0
SQL_ALTER_DOMAIN                         | 0
SQL_DROP_COLLATION                       | 0
SQL_PARAM_ARRAY_SELECTS                  | 3
SQL_CURSOR_COMMIT_BEHAVIOR               | 2
SQL_SQL92_RELATIONAL_JOIN_OPERATORS      | 474
SQL_MAX_ROW_SIZE_INCLUDES_LONG           | False
SQL_MAX_COLUMNS_IN_ORDER_BY              | 0
SQL_INTERVAL_HOUR                        | 32000000
SQL_MAX_PROCEDURE_NAME_LEN               | 128
SQL_MAX_COLUMNS_IN_INDEX                 | 0
SQL_CONVERT_FUNCTIONS                    | 3
SQL_CATALOG_NAME_SEPARATOR               | .
SQL_ACCESSIBLE_TABLES                    | False
SQL_INTERVAL_DAY_TO_HOUR                 | 0
threadsafety                             | 0
SQL_SPECIAL_CHARACTERS                   | 
SQL_CREATE_VIEW                          | 1
SQL_INTERVAL_DAY_TO_SECOND               | 511
SQL_DROP_CHARACTER_SET                   | 0
SQL_USER_NAME                            | test
SQL_TIMEDATE_ADD_INTERVALS               | 511
SQL_NO_NULLS                             | 0
SQL_DATETIME_LITERALS                    | 65535
SQL_DATA_SOURCE_NAME                     | vertica_kdl_dsn
SQL_DROP_ASSERTION                       | 0
SQL_MULT_RESULT_SETS                     | True
SQL_ASYNC_MODE                           | 0
>>>

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by id10t » Tue Jan 21, 2014 3:25 pm

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 9:15 pm, edited 1 time in total.

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by matthewcornell » Tue Jan 21, 2014 4:14 pm

Hi sKwa:

> Actually Vertica doesn't support python with odbc, neither python version 2, nor python version 3.

I'm confused because their docs say:
Programming Python Client Applications
https://my.vertica.com/docs/7.0.x/HTML/ ... s%7C_____0

Programming Python Client Applications .. HP Vertica provides an ODBC driver so applications can connect to the HP Vertica database .. In order to use Python with HP Vertica, you must install the pyodbc module and an HP Vertica ODBC driver on the machine where Python is installed.
Simple calls work so far in python:

Code: Select all

>>> curs.execute('DROP TABLE IF EXISTS foo')
<pyodbc.Cursor object at 0x7fd44f000210>
>>> curs.execute('CREATE TABLE foo (id INT)')
<pyodbc.Cursor object at 0x7fd44f000210>
>>> curs.execute('INSERT INTO foo VALUES (1)')
<pyodbc.Cursor object at 0x7fd44f000210>
>>> curs.execute('SELECT * FROM foo')  ;  print(curs.fetchall())
<pyodbc.Cursor object at 0x7fd44f000210>
[(1, )]
>>> curs.execute('DROP TABLE foo')
<pyodbc.Cursor object at 0x7fd44f000210>

> Use in perl or php.

:lol:

> In [3]: conn.execute("select interval '1 day'")
> ...
> Error: ('ODBC data type 110 is not supported. Cannot read column ?column?.', 'HY000')

Works in isql, so it's a pyodbc issue and not a vertica support one:

Issue 337 - pyodbc - HP Vertica: non of INTERVAL values are supported - Python ODBC library - Google Project Hosting

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by id10t » Tue Jan 21, 2014 5:46 pm

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 9:14 pm, edited 1 time in total.

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: python3 + rpy + pyodb + vertica 7 = Linux segfault

Post by matthewcornell » Tue Jan 21, 2014 6:15 pm

I agree that the support isn't good.

Post Reply

Return to “Python”