pyodbc
pyodbc copied to clipboard
mkleehammer
Crash on "cursor.execute" only when last column on a TVP argument is null
Environment
- Python: 3.8.10
- pyodbc: 4.0.32 and 4.0.33b6
- OS: Ubuntu 20.04 LTS on WSL2 of Windows 10 Enterprise
- DB: Microsoft SQL Server 2016 SP2
- driver: Microsoft ODBC Driver 17.8.1.1
Also had issue on an alpine docker container with python 3.10 and pyodbc 4.0.32
Issue
Python crashes with segmentation fault when executing a stored procedure with a TVP argument on a non-default table schema, the TVP has 13 arguments and if the last column is not null python does not crashes
Setup database
Run the follow database commands to create the table, table type and stored procedure. It assumes the schema "some_schema" exists:
drop procedure some_schema.some_table_insert_many
go
drop table some_schema.some_table
go
drop type some_schema.typ_some_table
go
IF OBJECT_ID(N'some_schema.some_table', N'U') IS NULL
BEGIN
CREATE TABLE some_schema.some_table
(
the_id INT IDENTITY(1, 1) NOT NULL CONSTRAINT some_table_PK PRIMARY KEY NONCLUSTERED,
field_1 BIGINT NOT NULL ,
field_2 INT NOT NULL,
field_3 BIGINT NULL,
field_4 TINYINT NULL ,
field_5 BIGINT NULL,
field_6 INT NOT NULL,
field_7 SMALLINT NOT NULL,
field_8 DATETIME2(2) NOT NULL,
field_9 TINYINT NOT NULL,
field_10 INT NULL,
field_11 NVARCHAR(512) NULL,
field_12 SMALLINT NULL,
field_13 TINYINT NULL
)
END;
GO
IF TYPE_ID('some_schema.typ_some_table') IS NULL
BEGIN
CREATE TYPE some_schema.typ_some_table AS TABLE
(
the_id INT NOT NULL,
field_1 BIGINT NOT NULL ,
field_2 INT NOT NULL,
field_3 BIGINT NULL,
field_4 TINYINT NULL ,
field_5 BIGINT NULL,
field_6 INT NOT NULL,
field_7 SMALLINT NOT NULL,
field_8 DATETIME2(2) NOT NULL,
field_9 TINYINT NOT NULL,
field_10 INT NULL,
field_11 NVARCHAR(512) NULL,
field_12 SMALLINT NULL,
field_13 TINYINT NULL
INDEX typ_some_table_HX_The_Id NONCLUSTERED (the_id)
)
END;
GO
CREATE OR ALTER PROCEDURE some_schema.some_table_insert_many
(
@arg1 some_schema.typ_some_table READONLY
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO some_schema.some_table (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13)
select field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13
FROM @arg1
where the_id > 0
--indicate everything is ok
select 0, ''
RETURN 0;
END;
GO
Code to reproduce problem
Run the following python script. In order to reproduce problem change the variable "do_you_want_to_crash" to True. In order to see a execution without crahes set variable to False. Also set database connection variables on line 11:
import platform, os, sys
import pyodbc
class Database :
def __init__(self):
self.db_cnx = pyodbc.connect( driver = "ODBC Driver 17 for SQL Server",
server = "localhost",
database = "some_db",
uid = "user",
pwd = "pw")
self.db_cnx.autocommit = True
def exec(self, sql):
self.db_cnx.execute(sql)
def execute_func_and_catch_error(self, i):
sql = ( "BEGIN\n"
"{ call [some_schema].[some_table_insert_many] (?) };\n"
"END\n" )
try:
cursor = self.db_cnx.cursor()
tvp_arg = get_data(i)
print(f"Exec data-set:{i} with {len(tvp_arg[0])-3} lines")
cursor.execute( sql, tvp_arg)
res_per_sp = []
while True:
res_per_sp.append( cursor.fetchone() )
if not cursor.nextset():
break
cursor.close()
return res_per_sp
except Exception as err:
print(f"Exception:{err}")
print(f"sql =>\n {sql}")
return []
return []
def get_data(line):
print(f"Getting line {line}")
data = [
'typ_some_table',
'some_schema',
[0, 1, 2, 3, 4, 5, 6, 7, '2008-08-08T08:08:08Z', 9, 10, '11', 12, 13 ]
]
do_you_want_to_crash = True
total_lines = 1
start = line+1
for cnt in range (1,total_lines+1):
if do_you_want_to_crash:
data.append( [cnt, (start*1000)+cnt, 1, (start*1100)+cnt, None, None, 0, 0, f'2021-{start}-{start}T14:50:11Z',
0, None, None, None, None ])
else:
data.append( [cnt, (start*1000)+cnt, 1, (start*1100)+cnt, None, None, 0, 0, f'2021-{start}-{start}T14:50:11Z',
0, None, None, None, 1 ])
return [ data ]
if __name__ == '__main__':
print("Testing pyodbc on calling procedures with TVP arguments")
print( f"os: {platform.system()}\n"
f"Current directory: {os.getcwd()}\n"
f"sys path: {sys.path}\n" )
print(f"python: {sys.version}")
print(f"pyodbc: {pyodbc.version} {os.path.abspath(pyodbc.__file__)}" )
db = Database()
db.exec("delete from some_schema.some_table")
print("----START ----\n")
for i in range (0,10):
res = db.execute_func_and_catch_error(i)
if res:
print(f"res: {res}")
print("---------------")
print("Everything went A-OK and nothing crashed!")
Crash information
I tried debugging the problem and the memory corruption shifts whether I use debug messages or gdb. With gdb the crash is on a SQLAllocHandle called in Cursor_New
#0 malloc_consolidate (av=av@entry=0x7ffff7fb8b80 <main_arena>) at malloc.c:4500
#1 0x00007ffff7e67e03 in _int_malloc (av=av@entry=0x7ffff7fb8b80 <main_arena>, bytes=bytes@entry=1672) at malloc.c:3699
#2 0x00007ffff7e6bd15 in __libc_calloc (n=<optimized out>, elem_size=<optimized out>) at malloc.c:3428
#3 0x00007ffff741a29c in __alloc_stmt () at __handles.c:784
#4 0x00007ffff73e4545 in __SQLAllocHandle (handle_type=handle_type@entry=3, input_handle=0xa6b590,
output_handle=output_handle@entry=0x7ffff71b4648, requested_version=requested_version@entry=0) at SQLAllocHandle.c:580
#5 0x00007ffff73e53ea in SQLAllocHandle (handle_type=handle_type@entry=3, input_handle=<optimized out>,
output_handle=output_handle@entry=0x7ffff71b4648) at SQLAllocHandle.c:1304
#6 0x00007ffff7669f5e in Cursor_New (cnxn=0x7ffff6e711f0) at src/cursor.cpp:2556
But adding debug messges to 4.0.33b6 the crash moves to this line :
ret = SQLPutData(cur->hstmt, hasTvpRows ? (SQLPOINTER)1 : 0, hasTvpRows);
on the execute function.
I am just starting to use pyodbc so I am not familiar with the code so any help would be appreciated.
Best Regards
I tried to reproduce it but got a different error, "A TVP's rows must all be the same size.", it seems to think that a row being passed in is 11 columns instead of 14 for some reason.
I'm afraid I won't be able to look deeper into it until the new year, though.
Hello,
Tried running the code I actually posted and was able to reproduce again, this was my output with the flag set to not crash:
Testing pyodbc on calling procedures with TVP arguments
os: Linux
Current directory: /mnt/c/temp/pyodbc_crash
sys path: ['/mnt/c/temp/pyodbc_crash', '/usr/lib/python38.zip', '/usr/lib/python3.8', '/usr/lib/python3.8/lib-dynload', '/home/pedro/.local/lib/python3.8/site-packages', '/usr/local/lib/python3.8/dist-packages', '/usr/local/lib/python3.8/dist-packages/pyodbc-4.0.33b6-py3.8-linux-x86_64.egg', '/usr/lib/python3/dist-packages']
python: 3.8.10 (default, Sep 28 2021, 16:10:42)
[GCC 9.3.0]
pyodbc: 4.0.32 /home/pedro/.local/lib/python3.8/site-packages/pyodbc.cpython-38-x86_64-linux-gnu.so
----START ----
Getting line 0
Exec data-set:0 with 1 lines
res: [(0, '')]
---------------
Getting line 1
Exec data-set:1 with 1 lines
res: [(0, '')]
---------------
Getting line 2
Exec data-set:2 with 1 lines
res: [(0, '')]
---------------
Getting line 3
Exec data-set:3 with 1 lines
res: [(0, '')]
---------------
Getting line 4
Exec data-set:4 with 1 lines
res: [(0, '')]
---------------
Getting line 5
Exec data-set:5 with 1 lines
res: [(0, '')]
---------------
Getting line 6
Exec data-set:6 with 1 lines
res: [(0, '')]
---------------
Getting line 7
Exec data-set:7 with 1 lines
res: [(0, '')]
---------------
Getting line 8
Exec data-set:8 with 1 lines
res: [(0, '')]
---------------
Getting line 9
Exec data-set:9 with 1 lines
res: [(0, '')]
---------------
Everything went A-OK and nothing crashed!
And on the database the data was inserted on all 14 columns
the_id field_1 field_2 field_3 field_4 field_5 field_6 field_7 field_8 field_9 field_10 field_11 field_12 field_13
----------- -------------------- ----------- -------------------- ------- -------------------- ----------- ------- --------------------------- ------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------
39 1001 1 1101 NULL NULL 0 0 2021-01-01 14:50:11.00 0 NULL NULL NULL 1
40 2001 1 2201 NULL NULL 0 0 2021-02-02 14:50:11.00 0 NULL NULL NULL 1
41 3001 1 3301 NULL NULL 0 0 2021-03-03 14:50:11.00 0 NULL NULL NULL 1
42 4001 1 4401 NULL NULL 0 0 2021-04-04 14:50:11.00 0 NULL NULL NULL 1
43 5001 1 5501 NULL NULL 0 0 2021-05-05 14:50:11.00 0 NULL NULL NULL 1
44 6001 1 6601 NULL NULL 0 0 2021-06-06 14:50:11.00 0 NULL NULL NULL 1
45 7001 1 7701 NULL NULL 0 0 2021-07-07 14:50:11.00 0 NULL NULL NULL 1
46 8001 1 8801 NULL NULL 0 0 2021-08-08 14:50:11.00 0 NULL NULL NULL 1
47 9001 1 9901 NULL NULL 0 0 2021-09-09 14:50:11.00 0 NULL NULL NULL 1
48 10001 1 11001 NULL NULL 0 0 2021-10-10 14:50:11.00 0 NULL NULL NULL 1
(10 rows affected)
Completion time: 2021-12-18T00:25:45.4587229+00:00
Settting the flag to crash would also crash python as expected
Hello, I forgot to mention something important this only happens on Linux, running this test on Windows ( in a powershell ) has no issues and does not crashes
Hello v-chojas
My unixODBC version is 2.3.7
pedro@PT-PEDMARQU1:/usr/bin$ odbcinst --version
unixODBC 2.3.7
The 2.3.9 was already released but on apt-get 2.3.7 is still listed as the current version.
I'm going to close this for inactivity. If someone could confirm with 2.3.9 it would be useful.
