Hibernate envers, post-delete event throws constraint violation exception

Question

Project uses Hibernate 3.5, Spring Webflow 2 and Hibernate Envers for auditing. Envers is configured in hibernate.cfg.xml. I have one-to-many relation mapping between entity 'ArticleGroup' and 'Article'. Table 'articles' has a foreign key 'article_group_id' references to the id in table 'article_groups'. In the front-end, when I delete an article, Hibernate Envers throws constraint violation exception for the post-delete event. If I don't use Envers, delete operations work fine. The two entities are defined as follows

    @Entity
    @Table(name="article_groups")
    @Audited
    public class ArticleGroup implements Serializable {

        @OneToMany(mappedBy="articleGroup", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
        @OrderBy("id")
        private List<Article> articles;

        // other fields, getters and setters
    }

    @Entity 
    @Table(name="articles")
    @Audited
    public class Article implements Serializable {

        @ManyToOne
        @JoinColumn(name = "article_group_id")
        private ArticleGroup articleGroup;

        // other fields, getters, setters
    }

Article deletion is coded as follows:

    @Service("articleManager")
    public class ArticleManagerImpl implements ArticleManager {
            // inject dao
            @SuppressWarnings("unchecked")
            @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
            public void deleteArticle(Article article, Object articles) {
            articleDao.delete(article);
            ((List<Article>)  ((OneSelectionTrackingListDataModel)articles).getWrappedData()).remove(article);
        }
    }

The constraint violation exception:

Hibernate: delete from live.articles where id=?
Hibernate: select nextval ('hibernate_sequence')
Hibernate: insert into audit.REVINFO (REVTSTMP, REV) values (?, ?)
Hibernate: insert into audit.articles_AUD (REVTYPE, content, language, name, order_number, title, article_group_id, id, REV) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
82828 [http-bio-8080-exec-2] DEBUG   org.springframework.orm.hibernate3.HibernateTransactionManager  - Initiating transaction rollback after commit exception
org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
...
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into audit.articles_AUD (REVTYPE, content, language, name, order_number, title, article_group_id, id, REV) values ('2', NULL, NULL, NULL, NULL, NULL, NULL, '14', '17') was aborted.  Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2619)

As you could see, when Envers inserts into table 'audit.articles_AUD', the article_group_id is null which caused the constraint violation. Anybody knows how to fix it? Thank you very much.

Answer 1

Got it. Set this property in the configuration file:

<prop key="org.hibernate.envers.store_data_at_delete">true</prop>

Reference: http://docs.jboss.org/hibernate/envers/3.6/reference/en-US/html/configuration.html Table 3.1. Envers Configuration Properties org.hibernate.envers.store_data_at_delete

Answer 2

If you're doing a post-delete, and articles_AUD has any constraints on articles or any other entry you're removing during this operation, you'd then be trying to to add an entry into articles_AUD with a reference that no longer exists.

As a rule of thumb, we keep references out of audit tables in order to better preserver revision history. If audit tables contain constraints on non-audit tables then revision history could potentially break if any entry from an audited table is removed.

---

In reference to your solution working when setting org.hibernate.envers.store_data_at_delete.

Should the entity data be stored in the revision when the entity is deleted (instead of only storing the id and all other properties as null). This is not normally needed, as the data is present in the last-but-one revision. Sometimes, however, it is easier and more efficient to access it in the last revision (then the data that the entity contained before deletion is stored twice).

This tells me that there are columns in your audit table that are NOT NULL, hence the constraint violation, as Envers is trying to insert null. You may want to allow null in your audit tables.