Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
php-coder opened this issue Dec 19, 2018 · 19 comments
Assignees
Labels
Milestone

Comments

@php-coder
Copy link
Owner

php-coder commented Dec 19, 2018

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
Copy link
Owner Author

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
Copy link
Owner Author

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
Copy link
Owner Author

^^^ these points will be addressed later

@mukeshk
Copy link
Contributor

mukeshk commented Sep 18, 2019

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
Copy link
Contributor

mukeshk commented Sep 18, 2019

Should I follow this approach?

@php-coder
Copy link
Owner Author

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"/>

@mukeshk
Copy link
Contributor

mukeshk commented Sep 19, 2019

https://www.baeldung.com/liquibase-rollback, I have read this article for understanding the rollback concept. I am not sure how it fits our need.
I mean changeset works for us.
How are rollback triggered in our case?

@php-coder
Copy link
Owner Author

How are rollback triggered in our case?

Most likely we won't need/use it but it's good to have it anyway, just in case. Also, it serves for documentation purpose in this case.

mukeshk added a commit to mukeshk/mystamps that referenced this issue Sep 20, 2019
mukeshk added a commit to mukeshk/mystamps that referenced this issue Sep 21, 2019
mukeshk added a commit to mukeshk/mystamps that referenced this issue Sep 21, 2019
mukeshk added a commit to mukeshk/mystamps that referenced this issue Sep 21, 2019
mukeshk added a commit to mukeshk/mystamps that referenced this issue Oct 3, 2019
@php-coder php-coder assigned php-coder and unassigned mukeshk Nov 8, 2019
@php-coder
Copy link
Owner Author

php-coder commented Nov 8, 2019

@mukeshk I'm going to fix this if you don't mind.

@mukeshk
Copy link
Contributor

mukeshk commented Nov 9, 2019 via email

@php-coder php-coder assigned mukeshk and unassigned php-coder Nov 9, 2019
@php-coder
Copy link
Owner Author

@mukeshk Sure, thank you for the help!

It's not urgent and it can be fixed during the next week. But it's definitely should be included in 0.4.2 release that is planned on Dec 15 and this issue is just the first step in order to have a support for multiple series in a collection. That means that we can't fix it just a day before the deadline.

@mukeshk
Copy link
Contributor

mukeshk commented Nov 9, 2019 via email

@php-coder
Copy link
Owner Author

is there any major in Dev environment ?

I'm not sure that I understood the question. What do you mean? Is there something more important than this task?

@mukeshk
Copy link
Contributor

mukeshk commented Nov 9, 2019 via email

@mukeshk
Copy link
Contributor

mukeshk commented Nov 9, 2019 via email

@php-coder
Copy link
Owner Author

I don't remember such changes so everything is the same. Ah, yes, we run our integration tests against PostgreSQL now (see #1140). Also Travis CI is failing frequently without showing the logs :-(

@php-coder
Copy link
Owner Author

@mukeshk weekly ping ;) don't hesitate to say "no" if you don't have time. It's open source and here are no obligations.

@mukeshk
Copy link
Contributor

mukeshk commented Nov 16, 2019 via email

@php-coder
Copy link
Owner Author

Mukesh, thank you for the update and have a nice weekend!

@php-coder php-coder self-assigned this Nov 17, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants