[JIRA] (JJDBC-17) ERROR when JSON Parsing with MS SQL Server

[Rod Widdowson (Jira)]

Rod Widdowson ( https://shibboleth.atlassian.net/secure/ViewProfile.jspa?accountId=59fb32bbc24efb3c4ed3c977 ) *commented* on JJDBC-17 ( https://shibboleth.atlassian.net/browse/JJDBC-17?atlOrigin=eyJpIjoiNGIzNzQwOTRlZTI4NDU0NGFiYzQzMTMzZjgyMDI5YjAiLCJwIjoiaiJ9 )

Re: ERROR when JSON Parsing with MS SQL Server ( https://shibboleth.atlassian.net/browse/JJDBC-17?atlOrigin=eyJpIjoiNGIzNzQwOTRlZTI4NDU0NGFiYzQzMTMzZjgyMDI5YjAiLCJwIjoiaiJ9 )

I've not been able to reproduce this locally. I'll put al the version and configuration details below, but to cut to the chase I see this

First time round
----------------

Lookup

2022-09-05 15:26:12,310 - 127.0.0.1 - TRACE [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:641] - Read:: 'SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?' 1: 'intercept/attribute-release' ; 2: 'jdoe:https://sp.example.org'
2022-09-05 15:26:18,650 - 127.0.0.1 - DEBUG [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:646] - Nothing returned

Insert

2022-09-05 15:26:52,254 - 127.0.0.1 - TRACE [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:542] - Create [Query]:: 'SELECT expires FROM StorageRecords WHERE context =? AND id=?'::  1: 'intercept/attribute-release' ; 2: 'jdoe:https://sp.example.org'
2022-09-05 15:26:52,628 - 127.0.0.1 - TRACE [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:553] - Create [Insert]:: 'INSERT INTO StorageRecords(context, id, expires, value, version) VALUES (?, ?, ?, ?, 1)'  1: 'intercept/attribute-release' ; 2: 'jdoe:https://sp.example.org' ; 3 'null' ; 4 '[{"id":307},{"id":308},{"id":"mail"},{"id":"schacHomeOrganization"},{"id":"uid"}]''

And on second login
-------------------

(ignore the time stamp - it's from an earlier test)

2022-09-05 15:22:36,694 - 127.0.0.1 - TRACE [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:641] - Read:: 'SELECT version, expires, value FROM StorageRecords WHERE context =? AND id=?' 1: 'intercept/attribute-release' ; 2: 'jdoe:https://sp.example.org'
2022-09-05 15:22:36,721 - 127.0.0.1 - TRACE [net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService:652] - Considering Version '1', Expires '[{"id":307},{"id":308},{"id":"mail"},{"id":"schacHomeOrganization"},{"id":"uid"}]', Value 'null''

So, for me the data is going down and coming back from the driver OK.

The driver itself is

> 
> 
> 
> mssql-jdbc-10.2.0.jre11.jar
> 
> 

And on my Window Server 2019 SQLServer (config tool) describes itself as

> 
> 
> 
> Microsoft Corporation
> 
> 
> 
> Version: 2019.0150.2000.05
> 
> 

Configuration
-------------

Table Creation

CREATE TABLE StorageRecords (
 context varchar(255) COLLATE  Latin1_General_100_CS_AS  NOT NULL,
 id varchar(255) COLLATE Latin1_General_100_CS_AS NOT NULL,
 expires bigint DEFAULT NULL,
 value varchar(255) NOT NULL,
 version bigint NOT NULL,
 PRIMARY KEY (context,id)
)

Global.xml

<bean id="my.dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" lazy-init="true"
  p:driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  p:url="jdbc:sqlserver://10.0.0.125:1433;encrypt=false"
  p:username="sa"
  p:password="*****" /> <! yea, I know but it's a test machine in a private to my VM host network-->

<bean id="JDBCStorageService" parent="shibboleth.JDBCStorageService"
     p:dataSource-ref="my.dataSource"
     p:transactionIsolation="4"
     p:retryableErrors="40001, 4001, 4002" />

idp.properties

idp.consent.StorageService = JDBCStorageService

( https://shibboleth.atlassian.net/browse/JJDBC-17#add-comment?atlOrigin=eyJpIjoiNGIzNzQwOTRlZTI4NDU0NGFiYzQzMTMzZjgyMDI5YjAiLCJwIjoiaiJ9 ) Add Comment ( https://shibboleth.atlassian.net/browse/JJDBC-17#add-comment?atlOrigin=eyJpIjoiNGIzNzQwOTRlZTI4NDU0NGFiYzQzMTMzZjgyMDI5YjAiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100206- sha1:3fcec87 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shibboleth.net/pipermail/commits/attachments/20220905/80433dfa/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-comment-icon-b0b6758e-921e-4c45-a49b-d9e5af7ec86d
Type: image/png
Size: 1084 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220905/80433dfa/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-avatar-1829b6b2-c02d-4b6f-9404-0e3b61543b15
Type: image/png
Size: 425 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220905/80433dfa/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-footer-desktop-logo-9d8f24bb-d474-422c-86bd-bd2f322cfe80
Type: image/png
Size: 10805 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220905/80433dfa/attachment-0005.png>