pyodbc
pyodbc copied to clipboard
mkleehammer
Segfault or abort with core dump on TVP passed to SQL Server Stored Procedure containing NULLs
Environment
To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:
- Python: 3.7.6
- pyodbc: 4.0.32
- OS: Linux
- DB: SQL Sever 2019
- driver: ODBC Driver 17 for SQL Server
Issue
Expected Behavior Passing NULL values in a TVP to a stored procedure returns normally when the target table type allows NULLs for the column value.
Observed Behavior segfault or abort with coredump when passing NULL values for NULLable columns in a TVP to a stored procedure for some, but not all column types. Specifically, segfault occurs when passing NULL to varbinary(MAX) column and abort with coredump occurs when passing NULL in a float or tinyint column.
Do you have a repro app (and SQL queries to setup the stored procedure and the like)? I want to try it out and see what's happening.
Python app:
import pyodbc
conn=pyodbc.connect('DSN=MSSQLServerDatabase;UID=JavaUser;PWD=Redacted;DATABASE=JS') cursor=conn.cursor() cursor.execute("{CALL JavaTest.HardLoad()}") rows=cursor.fetchall() tvp_payload = [["HardTableType", "JavaTest", ] + rows] cursor.execute("{CALL JavaTest.HardStore(?)}", tvp_payload) cursor.commit()
The app simply calls JavaTest.HardLoad to load a resultset into a TVP and then passes that to JavaTest.HardStore, which will insert any rows that it does not already have.
HardSourceTable USE [JS] GO
/****** Object: Table [JavaTest].[HardSourceTable] Script Date: 9/14/2021 3:30:27 PM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [JavaTest].[HardSourceTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [SomeText] [sysname] NULL, [SomeTiny] [tinyint] NULL, [SomeBig] [bigint] NULL, [SomeSmall] [smallint] NULL, [SomeDate] [date] NULL, [SomeTime] time NULL, [SomeDateTime] [datetime] NULL, [SomeDateTime2] datetime2 NULL, [SomeDateTime3] datetime2 NULL, [SomeUnique] [uniqueidentifier] NULL, [SomeBinary] varbinary NULL, [SomeBigText] nvarchar NULL, [SomeBigBinary] varbinary NULL, [SomeFloat] [float] NULL, [SomeSmallNum] [numeric](8, 2) NULL, [SomeNum] [numeric](38, 6) NULL, [SomeBit] [bit] NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
USE [JS] GO /****** Object: StoredProcedure [JavaTest].[HardLoad] Script Date: 9/14/2021 3:33:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Proc [JavaTest].[HardLoad] AS SELECT * FROM JavaTest.HardSourceTable RETURN 0
USE [JS] GO /****** Object: StoredProcedure [JavaTest].[HardStore] Script Date: 9/14/2021 3:33:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Proc [JavaTest].[HardStore] @ResultSetToStore JavaTest.HardTableType READONLY AS INSERT INTO JavaTest.HardTargetTable SELECT * FROM @ResultSetToStore WHERE ID not in (select ID from JavaTest.HardTargetTable) RETURN 0
USE [JS] GO
/****** Object: Table [JavaTest].[HardTargetTable] Script Date: 9/14/2021 3:35:20 PM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [JavaTest].[HardTargetTable]( [ID] [int] NOT NULL, [SomeText] [sysname] NULL, [SomeTiny] [tinyint] NULL, [SomeBig] [bigint] NULL, [SomeSmall] [smallint] NULL, [SomeDate] [date] NULL, [SomeTime] time NULL, [SomeDateTime] [datetime] NULL, [SomeDateTime2] datetime2 NULL, [SomeDateTime3] datetime2 NULL, [SomeUnique] [uniqueidentifier] NULL, [SomeBinary] varbinary NULL, [SomeBigText] nvarchar NULL, [SomeBigBinary] varbinary NULL, [SomeFloat] [float] NULL, [SomeSmallNum] [numeric](8, 2) NULL, [SomeNum] [numeric](38, 6) NULL, [SomeBit] [bit] NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
USE [JS] GO
/****** Object: UserDefinedTableType [JavaTest].[HardTableType] Script Date: 9/14/2021 3:37:32 PM ******/ CREATE TYPE [JavaTest].[HardTableType] AS TABLE( [ID] [int] NOT NULL, [SomeText] [sysname] NULL, [SomeTiny] [tinyint] NULL, [SomeBig] [bigint] NULL, [SomeSmall] [smallint] NULL, [SomeDate] [date] NULL, [SomeTime] time NULL, [SomeDateTime] [datetime] NULL, [SomeDateTime2] datetime2 NULL, [SomeDateTime3] datetime2 NULL, [SomeUnique] [uniqueidentifier] NULL, [SomeBinary] varbinary NULL, [SomeBigText] nvarchar NULL, [SomeBigBinary] varbinary NULL, [SomeFloat] [float] NULL, [SomeSmallNum] [numeric](8, 2) NULL, [SomeNum] [numeric](38, 6) NULL, [SomeBit] [bit] NULL ) GO
JavaTest is the schema.
On Tue, Sep 14, 2021 at 3:44 PM v-makouz @.***> wrote:
What's the schema for [JavaTest] ?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/mkleehammer/pyodbc/issues/955#issuecomment-919498785, or unsubscribe https://github.com/notifications/unsubscribe-auth/AF5UZUOGB72YIKT62EZ4RZDUB6XZFANCNFSM5EA5Q54A . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.
USE [JS] GO
/****** Object: Schema [JavaTest] Script Date: 9/14/2021 4:30:57 PM ******/ CREATE SCHEMA [JavaTest] GO
I can't seem to reproduce it, I instead get:
cursor.execute("{CALL JavaTest.HardStore(?)}", tvp_payload)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type HardTableType. (2715) (SQLParamData)')
If I don't use the schema the app executes without any errors...
HardTableType should be in the JavaTest schema. Is it? What are you populating tvp_payload with? The first two rows must point to the location of the data type. In this case, ("HardTableType", "JavaTest").
What do you mean when you say, "If I don't use the schema.."?
Also keep in mind that you need to have NULL values in HardSourceTable for SomeBigBinary, SomeFloat, or SomeTiny.
When the Java app executes successfully, both HardSourceTable and HardTargetTable will have the same contents.
When I try to reproduce it, I get
[HY004] [Microsoft][ODBC Driver 17 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)
When trying to copy a table that has NULLs for SomeBigBinary, SomeFloat, and SomeTiny. That's if I don't use JavaTest schema, and create the tables and stored procedures in default dbo schema. Otherwise I get that "type not found" error, so I still can't seem to get the segfault.
My Python code:
import pyodbc
conn=pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=***;UID=***;PWD=***;DATABASE=JS')
cursor=conn.cursor()
cursor.execute("{CALL JavaTest.HardLoad()}")
rows=cursor.fetchall()
tvp_payload = [["HardTableType", "JavaTest", ] + rows]
cursor.execute("{CALL JavaTest.HardStore(?)}", tvp_payload)
cursor.commit()
This line tells pyodbc to look for the data table type named HardTabletype in JavaTest, so you must have it defined there and used by JavaTest.HardStore.
tvp_payload = [["HardTableType", "JavaTest", ] + rows]
I have JavaTest defined and and created HardTableType like so: CREATE TABLE [JavaTest].[HardTableType] ....
I tried with a few Python versions, but mostly Python 3.9.6
Try running the following code once. This will produce a JavaTest.HardSourceTable with a single row containing a NULL value for only the SomeBigBinary column. Running the Python script should produce a SegFault.
USE JS;
DELETE FROM JavaTest.HardSourceTable;
INSERT INTO JavaTest.HardSourceTable (SomeText,
SomeTiny ,
SomeBig ,
SomeSmall ,
SomeDate ,
SomeTime ,
SomeDateTime ,
SomeDateTime2 ,
SomeDateTime3 ,
SomeUnique ,
SomeBinary ,
SomeBigText ,
-- SomeBigBinary ,
SomeFloat ,
SomeSmallNum ,
SomeNum ,
SomeBit
) VALUES ( 'SomeText', -- Some text
RAND()*100, --SomeTiny
RAND()*9999999999999999, --SomeBig
RAND()*30000, --SomeSmall
SYSDATETIME(), --SomeDate
SYSDATETIME(), --SomeTime
SYSDATETIME(), --SomeDateTime
SYSDATETIME(), --SomeDateTime2
SYSDATETIME(), --SomeDateTime3
NEWID(), --SomeUnique
0x1234567890, --SomeBinary
REPLICATE('SomeText', 100000), --SomeBigText
-- ENCRYPTBYPASSPHRASE('SuperSecretTextPassPhrase', REPLICATE(@x, 100)), --SomeBigBinary
RAND(), --SomeFloat
RAND() * 10, --SomeSmallNum
RAND()*1000000, --SomeNum
1); --SomeBit
Runtime Exception:
Traceback (most recent call last):
File "pyodbcTest.py", line 8, in <module>
cursor.execute("{CALL JavaTest.HardStore(?)}", tvp_payload)
pyodbc.Error: ('HY090', '[HY090] [unixODBC][Driver Manager]Invalid string or buffer length (0) (SQLBindParameter)')
Segmentation fault (core dumped)
I am unable to recreate the test environment. After creating the tables, table type, and stored procedures, when I try the INSERT INTO in the previous post I get
(0 rows affected) Msg 2628, Level 16, State 1, Line 3 String or binary data would be truncated in table 'JS.JavaTest.HardSourceTable', column 'SomeBinary'. Truncated value: ''. The statement has been terminated.
That is because the varbinary and nvarchar columns do not specify a width, so it defaults to 1.
Even after fixing the DDL for the tables and table-type to use
[SomeBinary] [varbinary](5) NULL,
[SomeBigText] [nvarchar](max) NULL,
[SomeBigBinary] [varbinary](max) NULL,
and successfully populating the [HardSourceTable] the Python test code just throws
[HY004] [Microsoft][ODBC Driver 17 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)
as @v-makouz mentioned above