Stored persistent ID and migration to 3.2

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Stored persistent ID and migration to 3.2

Simon Lundström-2
Hi!

I've just started the upgrade to 3.2 and fixing WARNs as I go. After
migrating persistent ID store to the new syntax and fixing the SQL
schema (we actually had one dupe user, but it had the same data so that
was pretty lucky) I noticed this in the logs:

2015-11-20 15:22:12,305 - WARN [net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx:782] - Stored Id Store: Duplicate insert failed as required with SQL State '23000', ensure this value is configured as a retryable error
2015-11-20 15:22:12,313 - INFO [net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx:467] - Stored Id Store: Data source successfully verified

Is that something you need to fix or for us deployers to configure?
We're using MySQL 5.1.73 from Ubuntu if that matters.

One question regarding the instructions in PID docs [1] though, it says
"Dump the existing table, *reordering the data in the select statement*,
and then load the data back into the recreated table." (emphasis mine).
It can be something specific to another RDBMS but in a MySQL dump there
isn't any SELECT-statement in a dump. IIRC neither is there one in one
from Postgres? Maybe it can be done more generic?

And to the real question; why would it need to be rearranged? Because
the Stored ID DDL reorders them? Why does it reorder them? Seems like a
lot of work for deployers for nothing worth?

I just added the PRIMARY KEY triplet and removed our other indexes
(which probably were wrong anyway) and it worked.

Have a great weekend!

BR,
- Simon

1, https://wiki.shibboleth.net/confluence/display/IDP30/PersistentNameIDGenerationConfiguration#PersistentNameIDGenerationConfiguration-MigratingfromOlderVersions

____________________________________

Simon Lundström
Section for Infrastructure

IT Services
Stockholm University
SE-106 91 Stockholm, Sweden

www.su.se/english/staff-info/it
--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Etienne Dysli Metref
On 20/11/15 15:50, Simon Lundström wrote:
> I just added the PRIMARY KEY triplet and removed our other indexes
> (which probably were wrong anyway) and it worked.

Hi Simon,

Me too. :) I just added the primary key and Postgres didn't have any
problems with that.

  Etienne



--
To unsubscribe from this list send an email to [hidden email]

signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Cantor, Scott E.
In reply to this post by Simon Lundström-2
On 11/20/15, 9:50 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:



>Is that something you need to fix or for us deployers to configure?

You, but we can collect up the codes we need to retry on and just add them to future versions too.

>One question regarding the instructions in PID docs [1] though, it says
>"Dump the existing table, *reordering the data in the select statement*,
>and then load the data back into the recreated table." (emphasis mine).
>It can be something specific to another RDBMS but in a MySQL dump there
>isn't any SELECT-statement in a dump. IIRC neither is there one in one
>from Postgres? Maybe it can be done more generic?

Dump was a general term. How you do it is your choice, but if you can't create a primary key, then you have to figure out if you can apply a unique constraint some other way, or reorder the data.

>And to the real question; why would it need to be rearranged? Because
>the Stored ID DDL reorders them? Why does it reorder them? Seems like a
>lot of work for deployers for nothing worth?

Some databases do not allow non-consecutive primary key columns.

>I just added the PRIMARY KEY triplet and removed our other indexes
>(which probably were wrong anyway) and it worked.

That's not generically successful in my testing but I can suggest people try it.

-- Scott

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Simon Lundström-2
On Fri, 2015-11-20 at 15:04:58 +0000, Cantor, Scott wrote:
> On 11/20/15, 9:50 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:
> >Is that something you need to fix or for us deployers to configure?
>
> You,

Where should this be configured?

> but we can collect up the codes we need to retry on and just add them to future versions too.

Need an Jira for it?

BR,
- Simon
--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Cantor, Scott E.
On 11/23/15, 5:38 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:



>On Fri, 2015-11-20 at 15:04:58 +0000, Cantor, Scott wrote:
>> On 11/20/15, 9:50 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:
>> >Is that something you need to fix or for us deployers to configure?
>>
>> You,
>
>Where should this be configured?

Sorry, I neglected to provide a reference to it in the docs. See the example box labeled "Example persistent ID store beans in saml-nameid.xml" in the updated PersistentNameIDGenerationConfiguration topic.

It's a collection property of the JDBCPersistentIdStoreEx class you wire up from the shibboleth.JDBCPersistentIdStore parent bean.

I'll add it to the example and adjust the docs since that's going to be a common need for a bit. It's better to have control of that bean anyway since you can adjust timeouts with it.

>>but we can collect up the codes we need to retry on and just add them to future versions too.
>
>Need an Jira for it?

I checked in this code already.

-- Scott

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Simon Lundström-2
As always Scott, thank you for your response and incredible patience!

BR,
- Simon

On Mon, 2015-11-23 at 14:54:20 +0000, Cantor, Scott wrote:

> On 11/23/15, 5:38 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:
>
>
>
> >On Fri, 2015-11-20 at 15:04:58 +0000, Cantor, Scott wrote:
> >> On 11/20/15, 9:50 AM, "users on behalf of Simon Lundström" <[hidden email] on behalf of [hidden email]> wrote:
> >> >Is that something you need to fix or for us deployers to configure?
> >>
> >> You,
> >
> >Where should this be configured?
>
> Sorry, I neglected to provide a reference to it in the docs. See the example box labeled "Example persistent ID store beans in saml-nameid.xml" in the updated PersistentNameIDGenerationConfiguration topic.
>
> It's a collection property of the JDBCPersistentIdStoreEx class you wire up from the shibboleth.JDBCPersistentIdStore parent bean.
>
> I'll add it to the example and adjust the docs since that's going to be a common need for a bit. It's better to have control of that bean anyway since you can adjust timeouts with it.
>
> >>but we can collect up the codes we need to retry on and just add them to future versions too.
> >
> >Need an Jira for it?
>
> I checked in this code already.
>
> -- Scott
>
> --
> To unsubscribe from this list send an email to [hidden email]
--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Chris Phillips
I have this same warning in my logs as well and can't seem to eliminate it
and am looking for recommendations on how to resolve the warning

My platform & version info:
CentOS7, using Mysql 5.6.27 with mysql driver 5.1.35.
2015-12-07 15:17:25,833 - INFO
[net.shibboleth.idp.log.LogbackLoggingService:240] - Shibboleth IdP
Version 3.2.0
2015-12-07 15:17:25,843 - INFO
[net.shibboleth.idp.log.LogbackLoggingService:241] - Java
version='1.8.0_25' vendor='Oracle Corporation'
Commons jars:
commons-pool2-2.4.2.jar
commons-dbcp2-2.1.1.jar



What I've done:
Installed Shib3.2.0 and have implemented all the recommendations items
from:
https://wiki.shibboleth.net/confluence/display/IDP30/PersistentNameIDGenera
tionConfiguration


The schema is a cut and paste and has the primary key requirement.

In saml-nameid.properties I set idp.persistentId.store =
MyPersistentIdStore

And have the this in my saml-nameid.xml
--begin--
<!-- A DataSource bean suitable for use in the idp.persistentId.dataSource
property. -->
<bean id="MyDataSource" class="org.apache.commons.dbcp2.BasicDataSource"
    p:driverClassName="com.mysql.jdbc.Driver"
    p:url="jdbc:mysql://127.0.0.1:3306/shibboleth"
    p:username="shibboleth"
    p:password="suppressed"
    p:maxIdle="5"
    p:maxWaitMillis="15000"
    p:testOnBorrow="true"
    p:validationQuery="select 1"
    p:validationQueryTimeout="5" />
 
<!-- A "store" bean suitable for use in the idp.persistentId.store
property. -->
<bean id="MyPersistentIdStore" parent="shibboleth.JDBCPersistentIdStore"
    p:dataSource-ref="MyDataSource"
    p:queryTimeout="PT2S"
    p:retryableErrors="#{{'23000'}}" />
</beans>

--end--

And still see the error.

My shibpid table as I do this is either empty or has one row.

I've watched the mysql general_logs table to see what's flowing and it's
not clear how the 23000 error determination is happening.
I see the dummy insertion and deletion at the end.
I've taken the same insert and then ran it via command line mysql and see
the expected duplicate error with a double insertion.


It's like retryableErrors is not being trapped/observed and would like a
recommendation on how to chase this down further.

Thoughts and recommendations welcome.


C

On 2015-11-24, 8:23 AM, "users on behalf of Simon Lundström"
<[hidden email] on behalf of [hidden email]> wrote:

>As always Scott, thank you for your response and incredible patience!
>
>BR,
>- Simon
>
>On Mon, 2015-11-23 at 14:54:20 +0000, Cantor, Scott wrote:
>> On 11/23/15, 5:38 AM, "users on behalf of Simon Lundström"
>><[hidden email] on behalf of [hidden email]> wrote:
>>
>>
>>
>> >On Fri, 2015-11-20 at 15:04:58 +0000, Cantor, Scott wrote:
>> >> On 11/20/15, 9:50 AM, "users on behalf of Simon Lundström"
>><[hidden email] on behalf of [hidden email]> wrote:
>> >> >Is that something you need to fix or for us deployers to configure?
>> >>
>> >> You,
>> >
>> >Where should this be configured?
>>
>> Sorry, I neglected to provide a reference to it in the docs. See the
>>example box labeled "Example persistent ID store beans in
>>saml-nameid.xml" in the updated PersistentNameIDGenerationConfiguration
>>topic.
>>
>> It's a collection property of the JDBCPersistentIdStoreEx class you
>>wire up from the shibboleth.JDBCPersistentIdStore parent bean.
>>
>> I'll add it to the example and adjust the docs since that's going to be
>>a common need for a bit. It's better to have control of that bean anyway
>>since you can adjust timeouts with it.
>>
>> >>but we can collect up the codes we need to retry on and just add them
>>to future versions too.
>> >
>> >Need an Jira for it?
>>
>> I checked in this code already.
>>
>> -- Scott
>>
>> --
>> To unsubscribe from this list send an email to
>>[hidden email]
>--
>To unsubscribe from this list send an email to
>[hidden email]

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Cantor, Scott E.
On 12/7/15, 5:07 PM, "users on behalf of Chris Phillips" <[hidden email] on behalf of [hidden email]> wrote:



>In saml-nameid.properties I set idp.persistentId.store =
>MyPersistentIdStore

And the other/new dataSource property isn't set?

>I've watched the mysql general_logs table to see what's flowing and it's
>not clear how the 23000 error determination is happening.
>I see the dummy insertion and deletion at the end.

There's a second insert done and that either succeeds (which means there's no key defined), or it fails and it checks if the SQLState value coming back is configured in the retryableErrors collection. You should have that logged one way or the other. Not just an insert and delete. It's two inserts and then a delete.

>It's like retryableErrors is not being trapped/observed and would like a
>recommendation on how to chase this down further.

Well, if it's not configuring that, the config isn't what you think it is. There's no way I know of to debug that other than an actual debugger. I'd also make sure there aren't any other log messages suggesting it's generating some kind of compatibility config because of older settings.

The exception handling code's quite simple:

if (!retryableErrors.contains(e.getSQLState())) {
   log.warn("{} Duplicate insert failed as required with SQL State '{}', ensure this value is "
      + "configured as a retryable error", getLogPrefix(), e.getSQLState());
            }

-- Scott


--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Chris Phillips
I think I'm close to narrowing this down but still need guidance.

Note this is a longer post and has a stack trace.
Hopefully working this out on the shib-users list publicly may help
others..

Recap of desired outcome:
Appropriate and safe population of eduPersonTargetedId from a DB
connection that will not issue WARN conditions on the database in IdP
3.2.0.



As Scott commented in this thread earlier, there WAS another datasource
property in play in attribute-filter.xml which was/is for
eduPersonTargetedId generation/storage in a mysql DB using the StoredId
connector approach.

First question:
That connector has no ability to handle the retry conditions and there's
no way to configure it to in attribute-resolver.xml -- or is there?

I assume there isn't.
Given that, I looked at attempting to re-use the bean references in
saml-nameid.xml in attribute-resolver.xml and they were not available. I
assume it's a sequence of events thing where the beans in saml-nameid.xml
are instantiated AFTER the resolver is in place.


Since I encountered that problem, I took the approach inspired by the
StorageConfiguration[1] with the technique from
PersistentNameIDGenerationConfiguration[2] persistentID Store, and created
this in global.xml thinking I could create a global datasource connector
just like the one in saml-nameid.xml to handle the extra retryableErrors
conditions but available more globally:

In global.xml:
--begin--
 <!-- BaseGlobalDataSource -->

<bean id="BaseGlobalDataSource"
class="org.apache.commons.dbcp2.BasicDataSource"
    p:driverClassName="com.mysql.jdbc.Driver"
    p:url="jdbc:mysql://127.0.0.1:3306/shibboleth"
    p:username="shibboleth"
    p:password="SUPPRESED"
    p:maxIdle="5"
    p:maxWaitMillis="15000"
    p:testOnBorrow="true"
    p:validationQuery="select 1"
    p:validationQueryTimeout="5" />

  <!-- Parent bean for users to configure a custom ID store rather than a
data source only. -->
    <bean id="custom.JDBCPersistentParentStore" abstract="true"
       
class="net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx" />

<!-- A "store" bean suitable for use  to avoid the JDBC errors -->
<bean id="MyGlobalDataSource" parent="custom.JDBCPersistentParentStore"
    p:dataSource-ref="BaseGlobalDataSource"
    p:queryTimeout="PT2S"
    p:retryableErrors="#{{'23000'}}" />



--end--

Which I then refer to in attribute-resolver.xml that I can then refer to
via a BeanManaged Connection:

<resolver:DataConnector id="StoredId" xsi:type="StoredId"
xmlns="urn:mace:shibboleth:2.0:resolver:dc"
            generatedAttributeID="persistentId" sourceAttributeID="uid"
salt="SUPPRESSED">
        <resolver:Dependency ref="uid" />
        <dc:BeanManagedConnection>MyGlobalDataSource</dc:BeanManagedConnection>
    </resolver:DataConnector>



However, what I get is the following stack trace saying that the the
Beantype is not the right type and yet this technique works in
saml-nameid.xml:


2015-12-09 15:23:21,002 - WARN
[net.shibboleth.ext.spring.context.FilesystemGenericApplicationContext:545]
 - Exception encountered during context initialization - cancelling
refresh attempt: org.springframework.beans.factory.BeanCreationException:
Error creating bean with name 'StoredId': Initialization of bean failed;
nested exception is
org.springframework.beans.ConversionNotSupportedException: Failed to
convert property value of type
'net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx' to required
type 'javax.sql.DataSource' for property 'dataSource'; nested exception is
java.lang.IllegalStateException: Cannot convert value of type
[net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx] to required
type [javax.sql.DataSource] for property 'dataSource': no matching editors
or conversion strategy found
2015-12-09 15:23:21,038 - ERROR
[net.shibboleth.utilities.java.support.service.AbstractReloadableService:18
1] - Service 'shibboleth.AttributeResolverService': Initial load failed
net.shibboleth.utilities.java.support.service.ServiceException:
org.springframework.beans.factory.BeanCreationException: Error creating
bean with name 'StoredId': Initialization of bean failed; nested exception
is org.springframework.beans.ConversionNotSupportedException: Failed to
convert property value of type
'net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx' to required
type 'javax.sql.DataSource' for property 'dataSource'; nested exception is
java.lang.IllegalStateException: Cannot convert value of type
[net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx] to required
type [javax.sql.DataSource] for property 'dataSource': no matching editors
or conversion strategy found
        at
net.shibboleth.ext.spring.service.ReloadableSpringService.doReload(Reloadab
leSpringService.java:334)



So, at this point I'm wondering:

A. How StorageConfiguration[1] escapes the problem of the retry scenario?
If I can't do this bean approach to capture retry conditions is the
StorageConfiguration going to suffer the same risk as the saml-nameid.xml
connection pool, and if so, how would one deal with that? Is this a bug
yet to be encountered by the JPA/global.xml storage?

B. What is the recommended way to handle database connections in
attribute-resolver.xml in Shib 3.2.0 that need to trap the retry scenario
and avoid the WARN statement?

C. If the attribute-resolver.xml can properly route the population of
ePTId to the saml-nameid.xml configuration where it may (should?) reside
what is the recommended way to do that?

Thanks for thoughts and comments.

C


[1]
https://wiki.shibboleth.net/confluence/display/IDP30/StorageConfiguration
[2]
https://wiki.shibboleth.net/confluence/display/IDP30/PersistentNameIDGenera
tionConfiguration




On 2015-12-07, 5:18 PM, "users on behalf of Cantor, Scott"
<[hidden email] on behalf of [hidden email]> wrote:

>On 12/7/15, 5:07 PM, "users on behalf of Chris Phillips"
><[hidden email] on behalf of [hidden email]>
>wrote:
>
>
>
>>In saml-nameid.properties I set idp.persistentId.store =
>>MyPersistentIdStore
>
>And the other/new dataSource property isn't set?
>
>>I've watched the mysql general_logs table to see what's flowing and it's
>>not clear how the 23000 error determination is happening.
>>I see the dummy insertion and deletion at the end.
>
>There's a second insert done and that either succeeds (which means
>there's no key defined), or it fails and it checks if the SQLState value
>coming back is configured in the retryableErrors collection. You should
>have that logged one way or the other. Not just an insert and delete.
>It's two inserts and then a delete.
>
>>It's like retryableErrors is not being trapped/observed and would like a
>>recommendation on how to chase this down further.
>
>Well, if it's not configuring that, the config isn't what you think it
>is. There's no way I know of to debug that other than an actual debugger.
>I'd also make sure there aren't any other log messages suggesting it's
>generating some kind of compatibility config because of older settings.
>
>The exception handling code's quite simple:
>
>if (!retryableErrors.contains(e.getSQLState())) {
>   log.warn("{} Duplicate insert failed as required with SQL State '{}',
>ensure this value is "
>      + "configured as a retryable error", getLogPrefix(),
>e.getSQLState());
>            }
>
>-- Scott
>
>
>--
>To unsubscribe from this list send an email to
>[hidden email]

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Cantor, Scott E.
On 12/9/15, 3:51 PM, "users on behalf of Chris Phillips" <[hidden email] on behalf of [hidden email]> wrote:



>As Scott commented in this thread earlier, there WAS another datasource
>property in play in attribute-filter.xml which was/is for
>eduPersonTargetedId generation/storage in a mysql DB using the StoredId
>connector approach.

You mean resolver, not filter.

>First question:
>That connector has no ability to handle the retry conditions and there's
>no way to configure it to in attribute-resolver.xml -- or is there?

Umm, doubt it. I probably didn't circle back to it other than making sure the updated classes got spun up at runtime in place of the old ones. The underlying code is the same, but the config isn't and would have to be specially handled. Not out of the realm of possibility.

>Given that, I looked at attempting to re-use the bean references in
>saml-nameid.xml in attribute-resolver.xml and they were not available. I
>assume it's a sequence of events thing where the beans in saml-nameid.xml
>are instantiated AFTER the resolver is in place.

They're not visible to each other at all, the two Spring contexts are siblings.

>Since I encountered that problem, I took the approach inspired by the
>StorageConfiguration[1] with the technique from
>PersistentNameIDGenerationConfiguration[2] persistentID Store, and created
>this in global.xml thinking I could create a global datasource connector
>just like the one in saml-nameid.xml to handle the extra retryableErrors
>conditions but available more globally:

No, there's no way to inject it into the resolver plugin.

>Which I then refer to in attribute-resolver.xml that I can then refer to
>via a BeanManaged Connection:

No, you can't. One is a plain JDBC DataSource and one is a special object of a different interface.

>However, what I get is the following stack trace saying that the the
>Beantype is not the right type and yet this technique works in
>saml-nameid.xml:

The target bean there knows how to accept both types of objects.

>A. How StorageConfiguration[1] escapes the problem of the retry scenario?

Doesn't.

>If I can't do this bean approach to capture retry conditions is the
>StorageConfiguration going to suffer the same risk as the saml-nameid.xml
>connection pool, and if so, how would one deal with that? Is this a bug
>yet to be encountered by the JPA/global.xml storage?

I wouldn't say never, but I believe the JPA code sees an exception type indicating an insert failed due to a duplicate. Whether that exception is raised reliably I couldn't say.

>B. What is the recommended way to handle database connections in
>attribute-resolver.xml in Shib 3.2.0 that need to trap the retry scenario
>and avoid the WARN statement?

There isn't one.

>C. If the attribute-resolver.xml can properly route the population of
>ePTId to the saml-nameid.xml configuration where it may (should?) reside
>what is the recommended way to do that?

It could never do that. They can share a DataSource, that's about it.

-- Scott

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Cantor, Scott E.
On 12/9/15, 4:08 PM, "users on behalf of Cantor, Scott" <[hidden email] on behalf of [hidden email]> wrote:


>
>>B. What is the recommended way to handle database connections in
>>attribute-resolver.xml in Shib 3.2.0 that need to trap the retry scenario
>>and avoid the WARN statement?
>
>There isn't one.

I guess I would concede that's a bug, though. Deprecated features still get support.

-- Scott

--
To unsubscribe from this list send an email to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Stored persistent ID and migration to 3.2

Chris Phillips
Thanks for these last two replies and the acknowledgement of this being an
issue.
I've opened an issue on it (IDP-880) and can now move ahead at least
knowing it's recognized as an item to be resolved.

C

On 2015-12-09, 4:15 PM, "users on behalf of Cantor, Scott"
<[hidden email] on behalf of [hidden email]> wrote:

>On 12/9/15, 4:08 PM, "users on behalf of Cantor, Scott"
><[hidden email] on behalf of [hidden email]> wrote:
>
>
>>
>>>B. What is the recommended way to handle database connections in
>>>attribute-resolver.xml in Shib 3.2.0 that need to trap the retry
>>>scenario
>>>and avoid the WARN statement?
>>
>>There isn't one.
>
>I guess I would concede that's a bug, though. Deprecated features still
>get support.
>
>-- Scott
>
>--
>To unsubscribe from this list send an email to
>[hidden email]

--
To unsubscribe from this list send an email to [hidden email]