Audit Logging Plugin

AuditLogEvent domain object fails to save on PostgreSQL and Oracle databases

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: Grails-AuditLogging 0.5
  • Component/s: None
  • Labels:
    None
  • Environment:
    PostgreSQL and Oracle
  • Patch Submitted:
    Yes

Description

Attached is a patch that provides a workaround for problems discussed on the Grails mailing list http://www.nabble.com/Audit-Logging-Plugin-Question-td17430121.html and http://www.nabble.com/how-do-I-encrypt-a-password--td17130128.html#a17170904 with the Audit Log plugin. The plugin successfully creates rows in the Audit Log table when using the in memory database, but fails silently with at least two 'real' databases (postgres and oracle).

Please note that this particular patch will only work with a postgres database. In particular, the SQL statement used in AuditLogListener.saveAuditLog() uses
1) select nextval ('hibernate_sequence') and
2) now()
which may or may not work depending on your database.

I don't think this is the ideal solution, but it may help some people get past the initial issue until a more permanent solution is found.

Issue Links

This issue is related to:
GRAILS-6272 Upgrading to grails 1.3 causes tests to fail due to unique constraint errors Critical Closed
This issue is depended upon by:
GPAUDITLOGGING-15 Integration tests with auditable domain class do not observe test transaction boundary Critical Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Shawn Hartsock added a comment -

In the AuditLogEvent domain class I'm testing with the following mapping...

static mapping = { table 'audit_log' id generator:'native' }

... does this work for anyone else (not on MySQL)?

Show
Shawn Hartsock added a comment - In the AuditLogEvent domain class I'm testing with the following mapping... static mapping = { table 'audit_log' id generator:'native' } ... does this work for anyone else (not on MySQL)?
Hide
Permalink
Shawn Hartsock added a comment -

The "save()" call from inside the AuditLogListener class event is not propagating to the database when Hibernate is hooked to a PostgreSQL or Oracle database. This problem does not manifest in MySQL or the HQL databases.

Show
Shawn Hartsock added a comment - The "save()" call from inside the AuditLogListener class event is not propagating to the database when Hibernate is hooked to a PostgreSQL or Oracle database. This problem does not manifest in MySQL or the HQL databases.
Hide
Permalink
Mike Hugo added a comment -

Adding a grails bug-report with a very simple application that could be used to replicate the issue. In order to run this you'll need a postgres database called pgtest and a postgres db user called test (with a password of test). You'll need to drop a postgres driver .jar file into the lib directory of the project as well.

To replicate:
1) Create a 'new' author
2) edit the author
3) add a few more authors if you like

Go back to the home screen and click on the AuditLog controller link (OR look at the audit log table in the DB). No rows will have been inserted logging the changes you have made.

Show
Mike Hugo added a comment - Adding a grails bug-report with a very simple application that could be used to replicate the issue. In order to run this you'll need a postgres database called pgtest and a postgres db user called test (with a password of test). You'll need to drop a postgres driver .jar file into the lib directory of the project as well. To replicate: 1) Create a 'new' author 2) edit the author 3) add a few more authors if you like Go back to the home screen and click on the AuditLog controller link (OR look at the audit log table in the DB). No rows will have been inserted logging the changes you have made.
Hide
Permalink
Shawn Hartsock added a comment -

I'm actively working on this issue again. In summary this problem is difficult because hooking into the existing session factory can have the effect of forcing a roll back on some databases and some database configurations. The problem with using the patch is that it assumes that the SQL insert statement will be valid and this is simply not valid HQL. I have attempted to alter the audit log domain object mapping to eliminate synthetic keys which allows for a simpler insert statement but creates problems reviewing the log in standard scaffolding. The creation of a separate unique session using the registered application session factory for audit logging appears to work in most cases on most databases... however on PostgreSQL I've observed a minor bug in flash context that may highlight an issue with this technique as well.

I will release version 0.5 only after extensive testing on Oracle, MySQL, HQL, and PostgreSQL.

Show
Shawn Hartsock added a comment - I'm actively working on this issue again. In summary this problem is difficult because hooking into the existing session factory can have the effect of forcing a roll back on some databases and some database configurations. The problem with using the patch is that it assumes that the SQL insert statement will be valid and this is simply not valid HQL. I have attempted to alter the audit log domain object mapping to eliminate synthetic keys which allows for a simpler insert statement but creates problems reviewing the log in standard scaffolding. The creation of a separate unique session using the registered application session factory for audit logging appears to work in most cases on most databases... however on PostgreSQL I've observed a minor bug in flash context that may highlight an issue with this technique as well. I will release version 0.5 only after extensive testing on Oracle, MySQL, HQL, and PostgreSQL.
Hide
Permalink
Steven C. Buttgereit added a comment -

I glad to see this one getting attention. The plugin looks great, save for the issue with PostgreSQL. Let me know if there's something I can do to help, testing, etc. that would be a more productive use of my time rather than trying to implement a whole audit log solution on my own!

Show
Steven C. Buttgereit added a comment - I glad to see this one getting attention. The plugin looks great, save for the issue with PostgreSQL. Let me know if there's something I can do to help, testing, etc. that would be a more productive use of my time rather than trying to implement a whole audit log solution on my own!
Hide
Permalink
Shawn Hartsock added a comment -

Currently working on testing with Oracle and PostgreSQL

Show
Shawn Hartsock added a comment - Currently working on testing with Oracle and PostgreSQL
Hide
Permalink
Shawn Hartsock added a comment -

It took a long time for me to figure this out but, you simply cannot use the session that GORM is using for some audit log events. That's because some audit events occur after a transaction has committed. Late session save actions can invalidate sessions or you can essentially roll-back your audit log saves. This is why you have to open your own session and transaction on some transactional databases and not others.

The solution to this bug is to NOT use GORM to save the AuditLogEvent object and instead use a separate hibernate session and separate transaction with its own commit and close. The result is the AuditLog is saved independently of the main GORM transaction and you can't accidentally invalidate/roll-back the GORM transaction.

This is checked in to SVN overwriting the previous 0.5_ALPHA file. I'll be testing this with other databases over the next few days. This fix will be part of 0.5

Show
Shawn Hartsock added a comment - It took a long time for me to figure this out but, you simply cannot use the session that GORM is using for some audit log events. That's because some audit events occur after a transaction has committed. Late session save actions can invalidate sessions or you can essentially roll-back your audit log saves. This is why you have to open your own session and transaction on some transactional databases and not others. The solution to this bug is to NOT use GORM to save the AuditLogEvent object and instead use a separate hibernate session and separate transaction with its own commit and close. The result is the AuditLog is saved independently of the main GORM transaction and you can't accidentally invalidate/roll-back the GORM transaction. This is checked in to SVN overwriting the previous 0.5_ALPHA file. I'll be testing this with other databases over the next few days. This fix will be part of 0.5
Hide
Permalink
Shawn Hartsock added a comment -

This bug will be rolled into the 0.5 release along with GRAILSPLUGINS-1478

Show
Shawn Hartsock added a comment - This bug will be rolled into the 0.5 release along with GRAILSPLUGINS-1478
Hide
Permalink
Shawn Hartsock added a comment -

This will be fixed in the 0.5 GA release.

Show
Shawn Hartsock added a comment - This will be fixed in the 0.5 GA release.
Hide
Permalink
Shawn Hartsock added a comment -

Using sessionFactory to open separate Hibernate sessions to persist the AuditLog now.

Show
Shawn Hartsock added a comment - Using sessionFactory to open separate Hibernate sessions to persist the AuditLog now.

People

Vote (1)
Watch (2)

Dates

  • Due:
    Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.1#660-r161644) | Report a problem
Powered by a free Atlassian JIRA open source license for Grails project. Try JIRA - bug tracking software for your team.