idpv3 testing: clientIP in audit logs and consent jdbc ?

[Jarno Huuskonen]

Hei,

On Sat, Jan 31, Cantor, Scott wrote:
> > The retries are probably there to deal with storage write conflicts, not
> > timeouts, so changing it would be bad. Have to work out if there's a way to
> > tell the difference. New exception type maybe.
> 
> Actually, it's a bug in the JPA code. It's returning false from the create() operation instead of throwing, so the consent action is interpreting that as a duplicate insert and not a storage error.

Is this something that can be "fixed" in idp/opensaml code or is the
bug deeper in hibernate/java/jpa code ?

BTW I created this mysql table for consent (found it from IDP-584):
create table StorageRecords (
 context varchar(255) not null,
 `key` varchar(255) not null,
 expiration bigint,
 value varchar(255) not null,
 version bigint not null,
 primary key (context, `key`));

After creating this table "consent" still fails to write results to db:
Is it possible that "key" column is not quoted in mysql query ?
(Maybe setting GLOBALLY_QUOTED_IDENTIFIERS would help
(https://docs.jboss.org/hibernate/orm/4.2/javadocs/org/hibernate/cfg/AvailableSettings.html#GLOBALLY_QUOTED_IDENTIFIERS))

(Error log for failed consent update:
2015-02-03 12:17:55,719 - ERROR
[net.shibboleth.idp.consent.flow.storage.AbstractConsentIndexedStorageAction:341]
- Profile Action CreateResult: Exhausted retry attempts storing result
  'ConsentResult{id=null, context=intercept/attribute-release,
key=VALUEREMOVED, value=[{"id":"sn"}], expiration=null}'
2015-02-03 12:17:55,734 - ERROR
[org.opensaml.storage.impl.JPAStorageService:112] - Error creating
record 'VALUERMOEVED:_key_idx' in context 'intercept/attribute-release' with
expiration 'null'
javax.persistence.RollbackException: Error while committing the
transaction
        at
org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:94)
Caused by: javax.persistence.PersistenceException:
org.hibernate.exception.SQLGrammarException: could not execute statement
        at
org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
Caused by: org.hibernate.exception.SQLGrammarException: could not
execute statement
        at
org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'key)
values (null, '["VALUEREMOVED"]', 1,
'intercept/attribute-' at line 1
        at
sun.reflect.GeneratedConstructorAccessor81.newInstance(Unknown Source))


-Jarno

-- 
Jarno Huuskonen