Error creating table with flyway using Maven

Question

Error creating table with flyway using Maven

Asked 8 years, 3 months ago

Viewed 3,473 times

1

I’m with this error below when creating the wine table using flyway, this is a Maven project, anyone knows why gives this error?

Estates

spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost/wine
spring.datasource.username=postgres
spring.datasource.password=root

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.show-sql=true




  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.3.6.RELEASE)

2016-08-08 13:42:53.337  INFO 6572 --- [  restartedMain] com.algaworks.wine.WineApplication       : Starting WineApplication on Manoel with PID 6572 (C:\CURSOS\spring-boot-template-projeto-master\target\classes started by Manoel in C:\CURSOS\spring-boot-template-projeto-master)
2016-08-08 13:42:53.346  INFO 6572 --- [  restartedMain] com.algaworks.wine.WineApplication       : No active profile set, falling back to default profiles: default
2016-08-08 13:42:53.847  INFO 6572 --- [  restartedMain] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@e66d742: startup date [Mon Aug 08 13:42:53 GMT-03:00 2016]; root of context hierarchy
2016-08-08 13:42:58.029  INFO 6572 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [class org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration$$EnhancerBySpringCGLIB$$417efcb6] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2016-08-08 13:42:59.087  INFO 6572 --- [  restartedMain] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2016-08-08 13:42:59.113  INFO 6572 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service Tomcat
2016-08-08 13:42:59.116  INFO 6572 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.0.36
2016-08-08 13:42:59.418  INFO 6572 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2016-08-08 13:42:59.418  INFO 6572 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 5582 ms
2016-08-08 13:43:00.313  INFO 6572 --- [ost-startStop-1] o.s.b.c.e.ServletRegistrationBean        : Mapping servlet: 'dispatcherServlet' to [/]
2016-08-08 13:43:00.319  INFO 6572 --- [ost-startStop-1] o.s.b.c.embedded.FilterRegistrationBean  : Mapping filter: 'characterEncodingFilter' to: [/*]
2016-08-08 13:43:00.320  INFO 6572 --- [ost-startStop-1] o.s.b.c.embedded.FilterRegistrationBean  : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2016-08-08 13:43:00.320  INFO 6572 --- [ost-startStop-1] o.s.b.c.embedded.FilterRegistrationBean  : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2016-08-08 13:43:00.320  INFO 6572 --- [ost-startStop-1] o.s.b.c.embedded.FilterRegistrationBean  : Mapping filter: 'requestContextFilter' to: [/*]
2016-08-08 13:43:00.860  INFO 6572 --- [  restartedMain] o.f.core.internal.util.VersionPrinter    : Flyway 3.2.1 by Boxfuse
2016-08-08 13:43:03.081  INFO 6572 --- [  restartedMain] o.f.c.i.dbsupport.DbSupportFactory       : Database: jdbc:postgresql://localhost/wine (PostgreSQL 9.4)
2016-08-08 13:43:03.185  INFO 6572 --- [  restartedMain] o.f.core.internal.command.DbValidate     : Validated 1 migration (execution time 00:00.038s)
2016-08-08 13:43:03.238  INFO 6572 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2016-08-08 13:43:03.239  INFO 6572 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 01 - criar tabela vinho
2016-08-08 13:43:03.262 ERROR 6572 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Migration of schema "public" to version 01 failed! Changes successfully rolled back.
2016-08-08 13:43:03.266  WARN 6572 --- [  restartedMain] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 
Migration V01__criar_tabela_vinho.sql failed
--------------------------------------------
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "("
  Posição: 36
Location   : db/migration/V01__criar_tabela_vinho.sql (C:\CURSOS\spring-boot-template-projeto-master\target\classes\db\migration\V01__criar_tabela_vinho.sql)
Line       : 1
Statement  : CREATE TABLE vinho (
    codigo BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    safra INT NOT NULL,
    volume INT NOT NULL,
    valor DECIMAL (10,2) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8

2016-08-08 13:43:03.297  INFO 6572 --- [  restartedMain] o.apache.catalina.core.StandardService   : Stopping service Tomcat
2016-08-08 13:43:03.324 ERROR 6572 --- [  restartedMain] o.s.boot.SpringApplication               : Application startup failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 
Migration V01__criar_tabela_vinho.sql failed
--------------------------------------------
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "("
  Posição: 36
Location   : db/migration/V01__criar_tabela_vinho.sql (C:\CURSOS\spring-boot-template-projeto-master\target\classes\db\migration\V01__criar_tabela_vinho.sql)
Line       : 1
Statement  : CREATE TABLE vinho (
    codigo BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    safra INT NOT NULL,
    volume INT NOT NULL,
    valor DECIMAL (10,2) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:296) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1054) ~[spring-context-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:829) ~[spring-context-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538) ~[spring-context-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:118) ~[spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:760) [spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.boot.SpringApplication.createAndRefreshContext(SpringApplication.java:360) [spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:306) [spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1185) [spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1174) [spring-boot-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at com.algaworks.wine.WineApplication.main(WineApplication.java:10) [classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_51]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_51]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_51]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_51]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) [spring-boot-devtools-1.3.6.RELEASE.jar:1.3.6.RELEASE]
Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 
Migration V01__criar_tabela_vinho.sql failed
--------------------------------------------
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "("
  Posição: 36
Location   : db/migration/V01__criar_tabela_vinho.sql (C:\CURSOS\spring-boot-template-projeto-master\target\classes\db\migration\V01__criar_tabela_vinho.sql)
Line       : 1
Statement  : CREATE TABLE vinho (
    codigo BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
    nome VARCHAR(50) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    safra INT NOT NULL,
    volume INT NOT NULL,
    valor DECIMAL (10,2) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=utf8

    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:284) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:282) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.execute(Flyway.java:1418) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-1.3.6.RELEASE.jar:1.3.6.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1637) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574) ~[spring-beans-4.2.7.RELEASE.jar:4.2.7.RELEASE]
    ... 23 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "("
  Posição: 36
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:406) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:398) ~[postgresql-9.4-1200-jdbc41.jar:9.4]
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238) ~[flyway-core-3.2.1.jar:na]
    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114) ~[flyway-core-3.2.1.jar:na]
    ... 40 common frames omitted

2016-08-08 13:43:03.329  INFO 6572 --- [  restartedMain] .b.l.ClasspathLoggingApplicationListener : Application failed to start with classpath: [file:/C:/CURSOS/spring-boot-template-projeto-master/target/classes/]

Try to glue the parentheses into DECIMAL. That way: valor DECIMAL(10, 2) NOT NULL. See if this works.

– Julian

2016/08/08 at 18:15
I put it the way you said, but the error continues, I think it should have something to do with that Error Creating bean with name 'flywayInitializer' defined in class path Resource [org/springframework/boot/autoconfigure/flyway/Flywayautoconfiguration$Flywayconfiguration.class]: Invocation of init method failed;

– Jose.Lemo

2016/08/08 at 18:25
Is your flyway folder structure correct? It should look like this: src/main/resources/db/migration/V01__criar_tabela_vinho.sql

– Julian

2016/08/08 at 18:28
Yes it is, the same way you glued up

– Jose.Lemo

2016/08/08 at 18:29
You added the properties to the file application.properties?

– Julian

2016/08/08 at 18:31
Yes I put these properties

– Jose.Lemo

2016/08/08 at 18:34
Well, try to review that part of the video again, the part he adds the Flyway is after the 30 min of the 2nd video.

– Julian

2016/08/08 at 18:35
Blaza will see again, edited the question, added the properties

– Jose.Lemo

2016/08/08 at 18:37
This business of engine=InnoDB is not Mysql/Mariadb?

– Jefferson Quesado

2018/02/19 at 11:33

Show 4 more comments

1 answer

Browser other questions tagged java web-application maven

You are not signed in. Login or sign up in order to post.

by Eduardo dos S Barbieri • **188** points · Answer 1 · 2016-08-10T00:04:36+00:00

I believe that your problem is in the creation script of the table V01__cria_tabela_vinho.sql, which is not compatible with Postgres, tries to change the script to:

CREATE TABLE vinho 
  ( 
     codigo serial primary key, 
     nome   VARCHAR(50) NOT NULL, 
     tipo   VARCHAR(50) NOT NULL, 
     safra  integer NOT NULL, 
     volume integer NOT NULL, 
     valor  NUMERIC (10, 2) NOT NULL 
  )