CODEWITHSUNDEEP
javaspringpostgresqlherokujhipster
Grim
Grim

Reputation: 73

PostgreSQL error when deploying jhipster app on Heroku

I'm writing simple blog app using React/Spring (generated by jHipster) and I wanted to deploy it on Heroku (As a database I'm using PostgreSQL). Everything works fine on my local using H2, but when I'll deploy the app on the Heroku (using jhipster heroku), and when I'll try to open the app, I don't see any posts and I have following errors in logs:

2020-04-01T12:48:44.491383+00:00 heroku[router]: at=info method=GET path="/api/posts?cacheBuster=1585745324414" host=notrealhostnamehere.herokuapp.com request_id=97cbe756-fca1-44b7-a9a6-b53a56ec43a1 fwd="109.196.225.112" dyno=web.1 connect=1ms service=92ms status=500 bytes=1150 protocol=https
2020-04-01T12:48:44.499072+00:00 app[web.1]:    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
2020-04-01T12:48:44.499074+00:00 app[web.1]:    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
2020-04-01T12:48:44.499074+00:00 app[web.1]:    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:114)
2020-04-01T12:48:44.499075+00:00 app[web.1]:    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:104)
2020-04-01T12:48:44.499075+00:00 app[web.1]:    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118)
2020-04-01T12:48:44.499075+00:00 app[web.1]:    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
2020-04-01T12:48:44.499075+00:00 app[web.1]:    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
2020-04-01T12:48:44.499076+00:00 app[web.1]:    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
2020-04-01T12:48:44.499076+00:00 app[web.1]:    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118)
2020-04-01T12:48:44.499076+00:00 app[web.1]:    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
2020-04-01T12:48:44.499077+00:00 app[web.1]:    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
2020-04-01T12:48:44.499077+00:00 app[web.1]:    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
2020-04-01T12:48:44.499077+00:00 app[web.1]:    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
2020-04-01T12:48:44.499077+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
2020-04-01T12:48:44.499078+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
2020-04-01T12:48:44.499081+00:00 app[web.1]:    at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
2020-04-01T12:48:44.499082+00:00 app[web.1]:    at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)
2020-04-01T12:48:44.499089+00:00 app[web.1]:    at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
2020-04-01T12:48:44.499089+00:00 app[web.1]:    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
2020-04-01T12:48:44.499089+00:00 app[web.1]:    at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
2020-04-01T12:48:44.499090+00:00 app[web.1]:    at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
2020-04-01T12:48:44.499090+00:00 app[web.1]:    at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
2020-04-01T12:48:44.499090+00:00 app[web.1]:    at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
2020-04-01T12:48:44.499091+00:00 app[web.1]:    at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
2020-04-01T12:48:44.499091+00:00 app[web.1]:    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
2020-04-01T12:48:44.499091+00:00 app[web.1]:    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
2020-04-01T12:48:44.499091+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)
2020-04-01T12:48:44.499092+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)
2020-04-01T12:48:44.499092+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)
2020-04-01T12:48:44.499092+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)
2020-04-01T12:48:44.499092+00:00 app[web.1]:    at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
2020-04-01T12:48:44.499093+00:00 app[web.1]:    at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
2020-04-01T12:48:44.499093+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)
2020-04-01T12:48:44.499093+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)
2020-04-01T12:48:44.499093+00:00 app[web.1]:    at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)
2020-04-01T12:48:44.499094+00:00 app[web.1]:    at io.undertow.server.Connectors.executeRootHandler(Connectors.java:376)
2020-04-01T12:48:44.499094+00:00 app[web.1]:    at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
2020-04-01T12:48:44.499094+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
2020-04-01T12:48:44.499094+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
2020-04-01T12:48:44.499095+00:00 app[web.1]:    at java.lang.Thread.run(Thread.java:748)
2020-04-01T12:48:44.499095+00:00 app[web.1]: Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
2020-04-01T12:48:44.499095+00:00 app[web.1]:    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
2020-04-01T12:48:44.499096+00:00 app[web.1]:    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
2020-04-01T12:48:44.499096+00:00 app[web.1]:    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
2020-04-01T12:48:44.499096+00:00 app[web.1]:    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
2020-04-01T12:48:44.499096+00:00 app[web.1]:    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
2020-04-01T12:48:44.499097+00:00 app[web.1]:    at org.hibernate.loader.Loader.getResultSet(Loader.java:2265)
2020-04-01T12:48:44.499098+00:00 app[web.1]:    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2028)
2020-04-01T12:48:44.499099+00:00 app[web.1]:    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1990)
2020-04-01T12:48:44.499099+00:00 app[web.1]:    at org.hibernate.loader.Loader.doQuery(Loader.java:949)
2020-04-01T12:48:44.499099+00:00 app[web.1]:    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351)
2020-04-01T12:48:44.499099+00:00 app[web.1]:    at org.hibernate.loader.Loader.doList(Loader.java:2787)
2020-04-01T12:48:44.499100+00:00 app[web.1]:    at org.hibernate.loader.Loader.doList(Loader.java:2770)
2020-04-01T12:48:44.499100+00:00 app[web.1]:    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2604)
2020-04-01T12:48:44.499100+00:00 app[web.1]:    at org.hibernate.loader.Loader.list(Loader.java:2599)
2020-04-01T12:48:44.499101+00:00 app[web.1]:    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:505)
2020-04-01T12:48:44.499101+00:00 app[web.1]:    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
2020-04-01T12:48:44.499102+00:00 app[web.1]:    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
2020-04-01T12:48:44.499102+00:00 app[web.1]:    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1537)
2020-04-01T12:48:44.499102+00:00 app[web.1]:    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1538)
2020-04-01T12:48:44.499102+00:00 app[web.1]:    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1506)
2020-04-01T12:48:44.499111+00:00 app[web.1]:    at org.hibernate.query.Query.getResultList(Query.java:132)
2020-04-01T12:48:44.499115+00:00 app[web.1]:    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
2020-04-01T12:48:44.499116+00:00 app[web.1]:    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
2020-04-01T12:48:44.499116+00:00 app[web.1]:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
2020-04-01T12:48:44.499116+00:00 app[web.1]:    at java.lang.reflect.Method.invoke(Method.java:498)
2020-04-01T12:48:44.499117+00:00 app[web.1]:    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:403)
2020-04-01T12:48:44.499117+00:00 app[web.1]:    at com.sun.proxy.$Proxy212.getResultList(Unknown Source)
2020-04-01T12:48:44.499118+00:00 app[web.1]:    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:201)
2020-04-01T12:48:44.499118+00:00 app[web.1]:    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
2020-04-01T12:48:44.499118+00:00 app[web.1]:    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
2020-04-01T12:48:44.499119+00:00 app[web.1]:    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
2020-04-01T12:48:44.499119+00:00 app[web.1]:    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
2020-04-01T12:48:44.499120+00:00 app[web.1]:    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
2020-04-01T12:48:44.499120+00:00 app[web.1]:    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
2020-04-01T12:48:44.499120+00:00 app[web.1]:    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
2020-04-01T12:48:44.499121+00:00 app[web.1]:    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
2020-04-01T12:48:44.499121+00:00 app[web.1]:    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
2020-04-01T12:48:44.499121+00:00 app[web.1]:    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
2020-04-01T12:48:44.499122+00:00 app[web.1]:    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
2020-04-01T12:48:44.499122+00:00 app[web.1]:    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
2020-04-01T12:48:44.499122+00:00 app[web.1]:    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
2020-04-01T12:48:44.499123+00:00 app[web.1]:    ... 130 common frames omitted
2020-04-01T12:48:44.499124+00:00 app[web.1]: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "select"
2020-04-01T12:48:44.499124+00:00 app[web.1]:   Position: 228
2020-04-01T12:48:44.499124+00:00 app[web.1]:    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
2020-04-01T12:48:44.499124+00:00 app[web.1]:    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
2020-04-01T12:48:44.499125+00:00 app[web.1]:    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
2020-04-01T12:48:44.499125+00:00 app[web.1]:    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
2020-04-01T12:48:44.499126+00:00 app[web.1]:    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
2020-04-01T12:48:44.499126+00:00 app[web.1]:    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
2020-04-01T12:48:44.499126+00:00 app[web.1]:    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
2020-04-01T12:48:44.499126+00:00 app[web.1]:    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
2020-04-01T12:48:44.499127+00:00 app[web.1]:    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
2020-04-01T12:48:44.499127+00:00 app[web.1]:    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
2020-04-01T12:48:44.499127+00:00 app[web.1]:    ... 166 common frames omitted
2020-04-01T12:48:44.499127+00:00 app[web.1]:

Here is my Repository code:

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {

    @Query("select post from Post post where post.user.login = ?#{principal.username}")
    List<Post> findByUserIsCurrentUser();

    @Query(value = "select distinct post from Post post left join fetch post.tags",
        countQuery = "select count(distinct post) from Post post")
    Page<Post> findAllWithEagerRelationships(Pageable pageable);

    @Query("select distinct post from Post post left join fetch post.tags order by post.date desc")
    List<Post> findAllWithEagerRelationships();

    @Query("select post from Post post left join fetch post.tags where post.id =:id")
    Optional<Post> findOneWithEagerRelationships(@Param("id") Long id);

    @Query("select distinct post from Post post left join post.tags where post.date >:oldestDate order by post.visitCount desc")
    Page<Post> findAllTopHotness(Pageable pageable,@Param("oldestDate") LocalDate oldestDate);
}

And PostResource code:

@RestController
@RequestMapping("/api")
public class PostResource {

    private final Logger log = LoggerFactory.getLogger(PostResource.class);

    private static final String ENTITY_NAME = "post";

    @Value("${jhipster.clientApp.name}")
    private String applicationName;

    private final PostRepository postRepository;

    private final PostMapper postMapper;

    private final PostService postService;

    public PostResource(PostRepository postRepository, PostMapper postMapper, PostService postService) {
        this.postRepository = postRepository;
        this.postMapper = postMapper;
        this.postService = postService;
    }

    @PostMapping("/posts")
    @PreAuthorize("hasRole(\"" + AuthoritiesConstants.ADMIN + "\")")
    public ResponseEntity<PostDTO> createPost(@Valid @RequestBody PostDTO postDTO) throws URISyntaxException {
        log.debug("REST request to save Post : {}", postDTO);
        if (postDTO.getId() != null) {
            throw new BadRequestAlertException("A new post cannot already have an ID", ENTITY_NAME, "idexists");
        }
        Post post = postMapper.toEntity(postDTO);
        post = postRepository.save(post);
        PostDTO result = postMapper.toDto(post);
        return ResponseEntity.created(new URI("/api/posts/" + result.getId()))
            .headers(HeaderUtil.createEntityCreationAlert(applicationName, true, ENTITY_NAME, result.getId().toString()))
            .body(result);
    }

    @PutMapping("/posts")
    @PreAuthorize("hasRole(\"" + AuthoritiesConstants.ADMIN + "\")")
    public ResponseEntity<PostDTO> updatePost(@Valid @RequestBody PostDTO postDTO) throws URISyntaxException {
        log.debug("REST request to update Post : {}", postDTO);
        if (postDTO.getId() == null) {
            throw new BadRequestAlertException("Invalid id", ENTITY_NAME, "idnull");
        }
        Post post = postMapper.toEntity(postDTO);
        post = postRepository.save(post);
        PostDTO result = postMapper.toDto(post);
        return ResponseEntity.ok()
            .headers(HeaderUtil.createEntityUpdateAlert(applicationName, true, ENTITY_NAME, postDTO.getId().toString()))
            .body(result);
    }

    @GetMapping("/posts")
    public List<PostDTO> getAllPosts() {
        log.debug("REST request to get all Posts");
        return postRepository.findAllWithEagerRelationships().stream().map(postMapper::toDto).collect(Collectors.toList());
    }

    @GetMapping("/posts/hot")
    public List<Post> getHotnessPosts() {
    // public List<PostDTO> getHotnessPosts() {
        log.debug("REST request to get all Posts");
        return postRepository.findAllTopHotness(PageRequest.of(0, 5), LocalDate.now().minusDays((long)7)).stream().collect(Collectors.toList());
    }

    @GetMapping("/posts/{id}")
    public ResponseEntity<PostDTO> getPost(@PathVariable Long id, HttpServletRequest request) {
        return ResponseUtil.wrapOrNotFound(postService.getPost(id, request.getRemoteAddr()));
    }

    @DeleteMapping("/posts/{id}")
    @PreAuthorize("hasRole(\"" + AuthoritiesConstants.ADMIN + "\")")
    public ResponseEntity<Void> deletePost(@PathVariable Long id) {
        log.debug("REST request to delete Post : {}", id);
        postRepository.deleteById(id);
        return ResponseEntity.noContent().headers(HeaderUtil.createEntityDeletionAlert(applicationName, true, ENTITY_NAME, id.toString())).build();
    }
}

Can someone help me please? :)

Edit, SQL statement:

select distinct post0_.id as id1_7_0_, tag2_.id as id1_10_1_, post0_.content as content2_7_0_, post0_.date as date3_7_0_, post0_.picture as picture4_7_0_, post0_.picture_content_type as picture_5_7_0_, post0_.title as title6_7_0_, post0_.user_id as user_id7_7_0_, select count() FROM COMMENT c WHERE c.POST_ID = post0_.id as formula0_0_, select count() FROM POST_VISIT p WHERE p.POST_ID = post0_.id as formula1_0_, tag2_.name as name2_10_1_, tags1_.post_id as post_id1_8_0__, tags1_.tag_id as tag_id2_8_0__ from post post0_ left outer join post_tag tags1_ on post0_.id=tags1_.post_id left outer join tag tag2_ on tags1_.tag_id=tag2_.id order by post0_.date desc

Upvotes: 2

Views: 385

Answers (2)

vicpermir
vicpermir

Reputation: 3702

When you define calculated fields using @Formula in your entities it's very important to wrap it in parentheses if you are using a subquery. Documentation on Hibernate's @Formula annotation can be found here and the API reference here.

Example, you must have something like this:

@Entity
@Table(name = "post")
public class Post implements Serializable {
...
    @Formula("select count() from comment c where c.post_id = id")
    private Integer totalComments;
...

But that will not work, you have to put it like this:

    @Formula("(select count() from comment c where c.post_id = id)")
    private Integer totalComments;

Notice the extra parentheses ( and ) on the formula definition.

Upvotes: 1

pifor
pifor

Reputation: 7882

You need to put parenthesis for SELECT count() ... in the SELECT clause:

SELECT DISTINCT 
post0_.id AS id1_7_0_,
tag2_.id AS id1_10_1_,
post0_.content AS content2_7_0_,
post0_.date AS date3_7_0_,
post0_.picture AS picture4_7_0_,
post0_.picture_content_type AS picture_5_7_0_,
post0_.title AS title6_7_0_,
post0_.user_id AS user_id7_7_0_,
      (SELECT count()
       FROM COMMENT c
       WHERE c.POST_ID = post0_.id AS formula0_0_),
      (SELECT count()
       FROM POST_VISIT p
       WHERE p.POST_ID = post0_.id AS formula1_0_), 
tag2_.name AS name2_10_1_,
tags1_.post_id AS post_id1_8_0__,
tags1_.tag_id AS tag_id2_8_0__
FROM post post0_
LEFT OUTER JOIN post_tag tags1_ ON post0_.id=tags1_.post_id
LEFT OUTER JOIN tag tag2_ ON tags1_.tag_id=tag2_.id
ORDER BY post0_.date DESC

Upvotes: 0

Related Questions