[JIRA] (JJDBC-5) Unable to install JDBC Plugin in idp4.1.0 or 4.2.1

[Rod Widdowson (Jira)]

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

Re: Unable to install JDBC Plugin in idp4.1.0 or 4.2.1 ( https://shibboleth.atlassian.net/browse/JJDBC-5?atlOrigin=eyJpIjoiMTMzZmQzYTk4N2E1NDQzZThmZjI0OWZmYTdmNzc0MjciLCJwIjoiaiJ9 )

Vivien Wu ( https://shibboleth.atlassian.net/secure/ViewProfile.jspa?accountId=5f0e4b291a26ad001434d48d ) My apologies that you are having these issues - this one at least we can make traction on.

The error is during startup. The driver tries to create a record, read it and then delete it. These are main line operations and have to work.

As far as I can work out, this error is thrown by the database because the table it is looking at doesn’t have the schema that the JDBC storage service is expecting (and in particular that the number of columns is different). I’d be interested to know what the schema is and I would expect that the SQL

INSERT INTO StorageRecords VALUES ("net.shibboleth.plugin.storage.jdbc.impl.test", 
"net.shibboleth.plugin.storage.jdbc.impl.test", 100,
"net.shibboleth.plugin.storage.jdbc.impl.test", 1)

Would fail similarly,

(incidentally if you set net.shibboleth.plugin.storage.jdbc.impl logging to trace you will see all the SQL prepared statements.)

I am far from being an SQL expert but it appears that you can fix this by specifying the columns.

So the SQL would be

INSERT INTO StorageRecords ('context', 'id, 'expires', 'value', 'version' )
VALUES ("net.shibboleth.plugin.storage.jdbc.impl.test", 
"net.shibboleth.plugin.storage.jdbc.impl.test", 100,
"net.shibboleth.plugin.storage.jdbc.impl.test",  1)

(suitably adjusted for your schema).

If that SQL works then you should specify the createCreateRecordSQL option to be

INSERT INTO StorageRecords ('context', 'id, 'expires', 'value', 'version' )
VALUES (?,?,?,?,1)

as specified in the Configuration section of https://shibboleth.atlassian.net/wiki/spaces/IDPPLUGINS/pages/2989096970/JDBCStorageService

I’d also urge you to review the rest of the SQL statements against your schema

I’m 8 hours back from you so my ability to help in real time is limited but I will respond as best I can to any further questions, either here or in a support case.

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

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#100201- sha1:00501c8 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shibboleth.net/pipermail/commits/attachments/20220624/3b78d916/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-comment-icon-8894cc29-d56e-4055-ad8a-c906f49a2263
Type: image/png
Size: 1084 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220624/3b78d916/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-avatar-34165d77-1ebe-4130-a87a-e9795cfed86f
Type: image/png
Size: 425 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220624/3b78d916/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-footer-desktop-logo-6ad7c3ca-eb21-4737-a3d2-91aec3710eb6
Type: image/png
Size: 10805 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220624/3b78d916/attachment-0005.png>