Good day. I have application which deploied on Heroku. Application wrote on spring boot. It works fine in local.It works fine in Heroku if i enter (admin, password which was insert in liquebase). I can open pages in heroku with migrated data. But if i want to create new user or new topic( like insert in DB) i recieve org.hibernate.exception.SQLGrammarException: could not extract ResultSet. I dont have any idea and understanding how to fix it. Could you help me? I use PostrgeSQL ,Spring boot
scripts for liquebase schema.sql
CREATE TABLE usr
(
id bigint NOT NULL,
active boolean NOT NULL,
email character varying(255),
first_name character varying(255),
last_name character varying(255),
password character varying(255),
username character varying(255),
CONSTRAINT usr_pkey PRIMARY KEY (id)
);
CREATE TABLE user_role
(
user_id bigint NOT NULL,
roles character varying(255),
CONSTRAINT fk_user_role_us FOREIGN KEY (user_id)
REFERENCES usr (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE topic
(
id bigint NOT NULL,
content character varying(255),
created timestamp without time zone,
name character varying(255),
user_id bigint,
CONSTRAINT topic_pkey PRIMARY KEY (id),
CONSTRAINT fk_topic FOREIGN KEY (user_id)
REFERENCES usr (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE comments
(
id bigint NOT NULL,
text character varying(255),
user_id bigint,
topic_id bigint,
CONSTRAINT comments_pkey PRIMARY KEY (id),
CONSTRAINT fk_comments_us FOREIGN KEY (user_id)
REFERENCES usr (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_comments_to FOREIGN KEY (topic_id)
REFERENCES topic (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
script for insert.sql
insert into usr values (1,TRUE,'[email protected]','Vladimir','Vladimir','12345','Rustymattok');
insert into user_role values (1,'ADMIN');
insert into topic values (1,'smth','2016-06-22 19:10:25-07','Vladimir',1);
insert into comments values (1,'spring boot help',1,1);
application.properties
spring.main.banner-mode=off
#spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
#spring.jpa.hibernate.ddl-auto=create
# boot
spring.mvc.view.prefix= /WEB-INF/views/
spring.mvc.view.suffix= .jsp
# data base jpa
#spring.datasource.url=jdbc:postgresql://localhost:5432/4job
spring.datasource.url=${JDBC_DATABASE_URL:jdbc:postgresql://localhost:5432/forum}
spring.datasource.username=${JDBC_DATABASE_USERNAME:postgres}
spring.datasource.password=${JDBC_DATABASE_PASSWORD :admin}
spring.datasource.driver-class-name=org.postgresql.Driver
heroku.properties
spring.main.banner-mode=off
# boot
spring.mvc.view.prefix= /WEB-INF/views/
spring.mvc.view.suffix= .jsp
## datasource
spring.datasource.url=postgres://xvgbbyabcnxrwh:ea21081dc20d1e648812718125bef4aa603ea60e63b13ffb06cd2a23acf90344@ec2-54-247-122-209.eu-west-1.compute.amazonaws.com:5432/db8l3sl1gpm7i4
server.port=${PORT:5000}
Procfile
web: java -jar target/4jobforum-1.0-SNAPSHOT.jar --spring.config.location=heroku.properties
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
</parent>
<groupId>4jobforum</groupId>
<artifactId>4jobforum</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<!--<spring.profiles.active>local,no-liquibase</spring.profiles.active>-->
</properties>
<dependencies>
<!-- security -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<!--DataBase support-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.12</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>provided</scope>
</dependency>
<!-- Test module -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>3.6.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-checkstyle-plugin</artifactId>
<version>3.1.1</version>
<configuration>
<configLocation>checkstyle.xml</configLocation>
<encoding>UTF-8</encoding>
<consoleOutput>true</consoleOutput>
<failsOnError>true</failsOnError>
<linkXRef>false</linkXRef>
</configuration>
<executions>
<execution>
<id>validate</id>
<phase>validate</phase>
<goals>
<goal>check</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
log file during deploy
2020-08-02T17:11:02.566933+00:00 heroku[web.1]: State changed from down to starting
2020-08-02T17:11:06.129619+00:00 heroku[web.1]: Starting process with command `java -jar target/4jobforum-1.0-SNAPSHOT.jar --spring.config.location=heroku.properties`
2020-08-02T17:11:07.881906+00:00 app[web.1]: Setting JAVA_TOOL_OPTIONS defaults based on dyno size. Custom settings will override them.
2020-08-02T17:11:07.885202+00:00 app[web.1]: Picked up JAVA_TOOL_OPTIONS: -Xmx300m -Xss512k -XX:CICompilerCount=2 -Dfile.encoding=UTF-8
2020-08-02T17:11:09.549537+00:00 app[web.1]: 2020-08-02 17:11:09.544 INFO 4 --- [ main] ru.makarov.SpringWebApplication : Starting SpringWebApplication v1.0-SNAPSHOT on 74a1432b-9c2b-4216-ac90-7ba17e0c0929 with PID 4 (/app/target/4jobforum-1.0-SNAPSHOT.jar started by u56548 in /app)
2020-08-02T17:11:09.550740+00:00 app[web.1]: 2020-08-02 17:11:09.550 INFO 4 --- [ main] ru.makarov.SpringWebApplication : No active profile set, falling back to default profiles: default
2020-08-02T17:11:10.743828+00:00 app[web.1]: 2020-08-02 17:11:10.741 INFO 4 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2020-08-02T17:11:10.868091+00:00 app[web.1]: 2020-08-02 17:11:10.867 INFO 4 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 110ms. Found 3 JPA repository interfaces.
2020-08-02T17:11:11.509192+00:00 app[web.1]: 2020-08-02 17:11:11.508 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.545270+00:00 app[web.1]: 2020-08-02 17:11:11.545 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.security.config.annotation.configuration.ObjectPostProcessorConfiguration' of type [org.springframework.security.config.annotation.configuration.ObjectPostProcessorConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.549013+00:00 app[web.1]: 2020-08-02 17:11:11.548 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'objectPostProcessor' of type [org.springframework.security.config.annotation.configuration.AutowireBeanFactoryObjectPostProcessor] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.551743+00:00 app[web.1]: 2020-08-02 17:11:11.551 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler@14dd9eb7' of type [org.springframework.security.access.expression.method.DefaultMethodSecurityExpressionHandler] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.552900+00:00 app[web.1]: 2020-08-02 17:11:11.552 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.security.config.annotation.method.configuration.GlobalMethodSecurityConfiguration' of type [org.springframework.security.config.annotation.method.configuration.GlobalMethodSecurityConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.557767+00:00 app[web.1]: 2020-08-02 17:11:11.557 INFO 4 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'methodSecurityMetadataSource' of type [org.springframework.security.access.method.DelegatingMethodSecurityMetadataSource] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-08-02T17:11:11.990844+00:00 app[web.1]: 2020-08-02 17:11:11.990 INFO 4 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 21495 (http)
2020-08-02T17:11:12.007032+00:00 app[web.1]: 2020-08-02 17:11:12.006 INFO 4 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2020-08-02T17:11:12.007293+00:00 app[web.1]: 2020-08-02 17:11:12.007 INFO 4 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.29]
2020-08-02T17:11:12.539887+00:00 app[web.1]: 2020-08-02 17:11:12.539 INFO 4 --- [ main] org.apache.jasper.servlet.TldScanner : At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
2020-08-02T17:11:12.811533+00:00 app[web.1]: 2020-08-02 17:11:12.811 INFO 4 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2020-08-02T17:11:12.811759+00:00 app[web.1]: 2020-08-02 17:11:12.811 INFO 4 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 3158 ms
2020-08-02T17:11:13.177663+00:00 app[web.1]: 2020-08-02 17:11:13.177 INFO 4 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2020-08-02T17:11:13.498474+00:00 app[web.1]: 2020-08-02 17:11:13.498 INFO 4 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2020-08-02T17:11:14.384825+00:00 app[web.1]: 2020-08-02 17:11:14.384 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM public.databasechangeloglock
2020-08-02T17:11:14.386931+00:00 app[web.1]: 2020-08-02 17:11:14.386 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM public.databasechangeloglock
2020-08-02T17:11:14.389837+00:00 app[web.1]: 2020-08-02 17:11:14.389 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-08-02T17:11:14.408959+00:00 app[web.1]: 2020-08-02 17:11:14.408 INFO 4 --- [ main] l.lockservice.StandardLockService : Successfully acquired change log lock
2020-08-02T17:11:15.283976+00:00 app[web.1]: 2020-08-02 17:11:15.283 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT MD5SUM FROM public.databasechangelog WHERE MD5SUM IS NOT NULL LIMIT 1
2020-08-02T17:11:15.285842+00:00 app[web.1]: 2020-08-02 17:11:15.285 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT COUNT(*) FROM public.databasechangelog
2020-08-02T17:11:15.287163+00:00 app[web.1]: 2020-08-02 17:11:15.287 INFO 4 --- [ main] l.c.StandardChangeLogHistoryService : Reading from public.databasechangelog
2020-08-02T17:11:15.287451+00:00 app[web.1]: 2020-08-02 17:11:15.287 INFO 4 --- [ main] liquibase.executor.jvm.JdbcExecutor : SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-08-02T17:11:15.301421+00:00 app[web.1]: 2020-08-02 17:11:15.301 INFO 4 --- [ main] l.lockservice.StandardLockService : Successfully released change log lock
2020-08-02T17:11:15.431032+00:00 app[web.1]: 2020-08-02 17:11:15.430 INFO 4 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-08-02T17:11:15.562816+00:00 app[web.1]: 2020-08-02 17:11:15.562 INFO 4 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.4.9.Final}
2020-08-02T17:11:15.771354+00:00 app[web.1]: 2020-08-02 17:11:15.771 INFO 4 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-08-02T17:11:15.935060+00:00 app[web.1]: 2020-08-02 17:11:15.934 INFO 4 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL10Dialect
2020-08-02T17:11:17.179767+00:00 app[web.1]: 2020-08-02 17:11:17.179 INFO 4 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-08-02T17:11:17.189142+00:00 app[web.1]: 2020-08-02 17:11:17.188 INFO 4 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-08-02T17:11:17.258418+00:00 app[web.1]: 2020-08-02 17:11:17.258 WARN 4 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2020-08-02T17:11:18.110334+00:00 app[web.1]: 2020-08-02 17:11:18.110 INFO 4 --- [ main] o.s.s.web.DefaultSecurityFilterChain : Creating filter chain: any request, [org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter@65e61854, org.springframework.security.web.context.SecurityContextPersistenceFilter@1ca25c47, org.springframework.security.web.header.HeaderWriterFilter@79e18e38, org.springframework.security.web.csrf.CsrfFilter@71e5f61d, org.springframework.security.web.authentication.logout.LogoutFilter@5b6813df, org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter@1e5f4170, org.springframework.security.web.savedrequest.RequestCacheAwareFilter@533b266e, org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter@89ff02e, org.springframework.security.web.authentication.AnonymousAuthenticationFilter@1568159, org.springframework.security.web.session.SessionManagementFilter@1849db1a, org.springframework.security.web.access.ExceptionTranslationFilter@60fa3495, org.springframework.security.web.access.intercept.FilterSecurityInterceptor@2b62442c]
2020-08-02T17:11:18.302424+00:00 app[web.1]: 2020-08-02 17:11:18.302 INFO 4 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2020-08-02T17:11:18.452889+00:00 app[web.1]: 2020-08-02 17:11:18.452 INFO 4 --- [ main] o.s.b.a.w.s.WelcomePageHandlerMapping : Adding welcome page template: index
2020-08-02T17:11:18.675452+00:00 app[web.1]: 2020-08-02 17:11:18.675 INFO 4 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 21495 (http) with context path ''
2020-08-02T17:11:18.680000+00:00 app[web.1]: 2020-08-02 17:11:18.679 INFO 4 --- [ main] ru.makarov.SpringWebApplication : Started SpringWebApplication in 9.946 seconds (JVM running for 10.795)
2020-08-02T17:11:19.075009+00:00 heroku[web.1]: State changed from starting to up
2020-08-02T17:11:19.973115+00:00 app[web.1]: 2020-08-02 17:11:19.972 INFO 4 --- [io-21495-exec-3] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-08-02T17:11:19.973286+00:00 app[web.1]: 2020-08-02 17:11:19.973 INFO 4 --- [io-21495-exec-3] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2020-08-02T17:11:19.982684+00:00 app[web.1]: 2020-08-02 17:11:19.982 INFO 4 --- [io-21495-exec-3] o.s.web.servlet.DispatcherServlet : Completed initialization in 9 ms
log file during status 500
2020-08-02T17:14:03.307295+00:00 heroku[router]: at=info method=POST path="/newtopic" host=forum-4job.herokuapp.com request_id=aabbf4a0-05a3-4eb7-8692-a7a29bd8d52f fwd="176.120.239.23" dyno=web.1 connect=0ms service=99ms status=500 bytes=835 protocol=https
Entinity
@Data
@RequiredArgsConstructor
@NoArgsConstructor
@Entity
public class Topic {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@NonNull
private String name;
@NonNull
private String content;
@NonNull
private Calendar created;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "user_id")
private User author;
Controller
@PostMapping("/newtopic")
public String createTopic(@ModelAttribute("topic") Topic topic) {
Calendar currentTime = new GregorianCalendar();
topic.setCreated(currentTime);
Authentication auth = SecurityContextHolder.getContext().getAuthentication();
User user = userStore.findUserByUsername(auth.getName());
topic.setAuthor(userStore.findUserById(user.getId()));
topicStore.save(topic);
return "redirect:/index";
}
UserService crude
@Repository
public interface UserServiceCrud extends JpaRepository<User, Long> {
User findUserByUsername(String username);
User findUserById(Long id);
}
TopicService Crude
@Repository
public interface TopicServiceCrud extends JpaRepository<Topic, Long> {
Topic findAllById(Long id);
List<Topic> findAllByAuthor(User user);
}
The reasone in scripts of schema.sql and insert. For Heroku you should use id auto creation SERIAL. For Models ID generation strategy @GeneratedValue(strategy = GenerationType.IDENTITY)
Samples below
schema.sql
insert.sql
sample of Model