/collection/{slug}: exception happens when user adds a series to collection that already has this series

[php-coder]

018-12-19 00:13:54.532 ERROR 2181 --- [tp2100942741-24] r.m.web.controller.ErrorController       : Exception 'org.springframework.web.util.NestedServletException' occurred on page /series/1

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ?]; Нарушение уникального индекса или первичного ключа: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"
Unique index or primary key violation: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"; SQL statement:
INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ? [23505-197]; nested exception is org.h2.jdbc.JdbcSQLException: Нарушение уникального индекса или первичного ключа: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"
Unique index or primary key violation: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"; SQL statement:
INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ? [23505-197]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982) ~[spring-webmvc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) ~[javax.servlet-api-3.1.0.jar:3.1.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) ~[javax.servlet-api-3.1.0.jar:3.1.0]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:124) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177) ~[spring-security-web-4.2.9.RELEASE.jar:4.2.9.RELEASE]
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) ~[jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) [jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577) ~[jetty-security-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) [jetty-servlet-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) ~[jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.servlets.gzip.GzipHandler.handle(GzipHandler.java:529) [jetty-servlets-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) [jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.Server.handle(Server.java:499) [jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311) [jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257) [jetty-server-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544) [jetty-io-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) [jetty-util-9.2.18.v20160721.jar:9.2.18.v20160721]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) [jetty-util-9.2.18.v20160721.jar:9.2.18.v20160721]
	at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45]
Caused by: org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ?]; Нарушение уникального индекса или первичного ключа: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"
Unique index or primary key violation: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"; SQL statement:
INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ? [23505-197]; nested exception is org.h2.jdbc.JdbcSQLException: Нарушение уникального индекса или первичного ключа: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"
Unique index or primary key violation: "UC_COLLECTIONS_SERIES_COLLECTION_ID_SERIES_ID_INDEX_1 ON PUBLIC.COLLECTIONS_SERIES(COLLECTION_ID, SERIES_ID) VALUES (1, 1, 1)"; SQL statement:
INSERT INTO collections_series ( collection_id , series_id , number_of_stamps , price , currency ) SELECT c.id AS collection_id , ? AS series_id , ? AS number_of_stamps , ? as price , ? as currency FROM collections c WHERE c.user_id = ? [23505-197]
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:238) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:876) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:900) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:287) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:292) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
	at ru.mystamps.web.feature.collection.JdbcCollectionDao.addSeriesToUserCollection(JdbcCollectionDao.java:232) ~[classes/:na]
	at ru.mystamps.web.feature.collection.CollectionServiceImpl.addToCollection(CollectionServiceImpl.java:90) ~[classes/:na]
...

Solution:

• we should remove unique constraint from collection table to allow to have more than one instance of a series

[php-coder]

we should remove unique constraint from collection table to allow to have more than one instance of a series

In fact we should revert the changes from src/main/resources/liquibase/version/0.4/2016-01-04--unique_series_in_collection.xml. Let's try to use <rollback> for that: https://www.liquibase.org/documentation/rollback.html

[php-coder]

Just removing an unique constraint shouldn't introduce bugs but a full support for having many series in a collection will do that:

• on a series info page we should support adding more than one series into collection
• on a series info page we should correctly show more than one series in a collection
• on a series info page we should correctly remove one of the series from a collection (at this moment, all series will be removed)

[php-coder]

^^^ these points will be addressed later

[mukeshk]

I checked out a few articles to grasp the concept of rollback, but I don't understand it completely.
Should we create a new 2019-09-18--rollback_collection_unique_series_constraint.xml and add

<changeSet author="mukeshk" id="dropUniqueConstraint-collection_series">
    <dropUniqueConstraint 
            constraintName="uc_collections_series_collection_id_series_id"
            tableName="collections_series"
            uniqueColumns="collection_id, series_id"/>
</changeSet>

[mukeshk]

Should I follow this approach?

[php-coder]

Should I follow this approach?

Yes, something like this. Are you sure that we need to provide uniqueColumns in this case? I'd expect that table name and a constraint name should be enough to remove the constraint.

Also, could we use rollback section with a reference to the original changeset? Like in the example at https://www.liquibase.org/documentation/rollback.html

   <rollback changeSetId="changeRollback2-create" changeSetAuthor="nvoxland"/>