title: Wix embedded mysql with spring boot DDL query error for an entity with embedded id
body: <p><strong>Solved it was a configuration mistake</strong>
  I want to test a test method of my spring boot application in mysql.</p>
  
  <p>So I added wix-embedded-mysql and configured embedded database but when I run the test It cannot create a table from one of my Entity objects with the following error:</p>
  
  <pre><code>create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB
  Hibernate: create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB
  2019-04-20 03:22:37.080  WARN 2186 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement
  
  org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
      at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.internal.SessionFactoryImpl.&lt;init&gt;(SessionFactoryImpl.java:312) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1758) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1695) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:573) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:495) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1089) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:859) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:780) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
      at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:412) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
      at org.springframework.boot.SpringApplication.run(SpringApplication.java:333) ~[spring-boot-2.0.5.RELEASE.jar:2.0.5.RELEASE]
      at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:139) ~[spring-boot-test-2.0.5.RELEASE.jar:2.0.5.RELEASE]
      at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:117) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:108) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:190) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:132) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:246) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:227) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) ~[junit-4.12.jar:4.12]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:246) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) ~[junit-4.12.jar:4.12]
      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) ~[junit-4.12.jar:4.12]
      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) ~[junit-4.12.jar:4.12]
      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) ~[junit-4.12.jar:4.12]
      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) ~[junit-4.12.jar:4.12]
      at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) ~[junit-4.12.jar:4.12]
      at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.junit.runners.ParentRunner.run(ParentRunner.java:363) ~[junit-4.12.jar:4.12]
      at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190) ~[spring-test-5.0.9.RELEASE.jar:5.0.9.RELEASE]
      at org.junit.runner.JUnitCore.run(JUnitCore.java:137) ~[junit-4.12.jar:4.12]
      at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68) ~[junit-rt.jar:na]
      at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47) ~[junit-rt.jar:na]
      at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242) ~[junit-rt.jar:na]
      at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) ~[junit-rt.jar:na]
  Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE USER_TICKETS (DATE_TIME DATETIME([*]6) NOT NULL, TICKET_ID BIGINT UNSIGNED NOT NULL, USER_ID INT NOT NULL, CREATED_DATE DATE, CREATED_TIME TIME, MODIFIED_DATE DATE, MODIFIED_TIME TIME, VERSION BIGINT, BODY VARCHAR(1000), TYPE VARCHAR(255), CREATED_BY_ID INT, MODIFIED_BY_ID INT, PRIMARY KEY (DATE_TIME, TICKET_ID, USER_ID)) ENGINE=INNODB "; expected "FOR, UNSIGNED, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
  create table user_tickets (date_time datetime(6) not null, ticket_id BIGINT UNSIGNED not null, user_id INT not null, created_date date, created_time time, modified_date date, modified_time time, version bigint, body varchar(1000), type varchar(255), created_by_id INT, modified_by_id INT, primary key (date_time, ticket_id, user_id)) engine=InnoDB [42001-197]
      at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.message.DbException.getSyntaxError(DbException.java:217) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.getSyntaxError(Parser.java:555) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.read(Parser.java:3518) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.readIfMore(Parser.java:950) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.parseCreateTable(Parser.java:6571) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.parseCreate(Parser.java:4615) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.parsePrepared(Parser.java:380) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.parse(Parser.java:335) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.parse(Parser.java:311) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.command.Parser.prepareCommand(Parser.java:278) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.engine.Session.prepareLocal(Session.java:611) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.engine.Session.prepareCommand(Session.java:549) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217) ~[h2-1.4.197.jar:1.4.197]
      at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205) ~[h2-1.4.197.jar:1.4.197]
      at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.9.jar:na]
      at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.9.jar:na]
      at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
      ... 58 common frames omitted
  </code></pre>
  
  <p>This is how I added dependency in POM:</p>
  
  <pre><code>&lt;dependency&gt;
              &lt;groupId&gt;com.wix&lt;/groupId&gt;
              &lt;artifactId&gt;wix-embedded-mysql&lt;/artifactId&gt;
              &lt;version&gt;4.2.0&lt;/version&gt;
              &lt;scope&gt;test&lt;/scope&gt;
  &lt;/dependency&gt;
  </code></pre>
  
  <p>And configured embedded database as follows:</p>
  
  <pre><code>MysqldConfig config = MysqldConfig.aMysqldConfig(v5_7_19)
                      .withPort(3397)
                  .withTimeZone(TimeZone.getTimeZone(ZoneId.of("UTC")))
                  .withUser("test", "test")
                  .build();
  
  
  
  SchemaConfig schemaConfig = SchemaConfig.aSchemaConfig("test_database")
                  .build();
  
  embeddedMysql = EmbeddedMysql.anEmbeddedMysql(config)
                  .addSchema(schemaConfig)
                  .start();
  </code></pre>
  
  <p>My entity has been defined as followings:</p>
  
  <pre><code>@Getter
  @Setter
  @NoArgsConstructor
  @AllArgsConstructor
  @Entity
  @Table(name = "user_tickets")
  public class UserTicket extends BaseModel implements BaseModelId&lt;UserTicketId&gt; {
      @EmbeddedId
      private UserTicketId id;
  
      @NotEmpty(groups = Group.class, message = "{validation.notnull}")
      @Column(length = 1000)
      private String body;
  
      @Enumerated(EnumType.STRING)
      private UserTicketType type;
  
      @ManyToOne(fetch = FetchType.LAZY, optional = false)
      @MapsId("userId")
      @JoinColumn(name = "user_id", columnDefinition = "INT", foreignKey =
      @ForeignKey(foreignKeyDefinition = "FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE"))
      private User user;
  
      @Valid
      @NotNull(groups = Group.class, message = "{validation.notnull}")
      @ManyToOne(fetch = FetchType.LAZY, optional = false)
      @MapsId("ticketId")
      @JoinColumn(name = "ticket_id", columnDefinition = "BIGINT UNSIGNED", foreignKey =
      @ForeignKey(foreignKeyDefinition = "FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE"))
      private Ticket ticket;
  
      @OneToMany(cascade = CascadeType.ALL, mappedBy = "userTicket", orphanRemoval = true)
      private Set&lt;UserTicketAttachment&gt; userTicketAttachments;
  }
  </code></pre>
  
  <p>And finally the embedded id:</p>
  
  <pre><code>@Getter
  @Setter
  @ToString(of = {"userId", "ticketId", "dateTime"})
  @EqualsAndHashCode(of = {"userId", "ticketId", "dateTime"})
  @NoArgsConstructor
  @AllArgsConstructor
  @Embeddable
  public class UserTicketId implements Serializable {
      @Column(name = "user_id")
      private Long userId;
      @Column(name = "ticket_id")
      private Long ticketId;
      @Column(name = "date_time")
      private LocalDateTime dateTime;
  }
  </code></pre>
  
  <p>All the other entities even those with embedded ids are created successfully in the embedded database. </p>
  
  <p>I even copied and pasted generated query for the table manually in the embedded database and it worked!</p>
  
  <p>I also need to mention that the setup works ok for the real database.</p>
  
  <p>Here is my application.properties datasource definition:</p>
  
  <pre><code>spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  spring.datasource.url=jdbc:mysql://localhost:3397/test_database?autoReconnect=true&amp;verifyServerCertificate=false&amp;useSSL=false&amp;requireSSL=false
  spring.datasource.username=test
  spring.datasource.password=test
  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
  spring.jpa.show-sql=true
  spring.jpa.hibernate.ddl-auto=create-drop 
  </code></pre>