Hibernate error when executing JDBC statements - unexpected token 'UNIQUE'

Question

I searched around and the only similar StackOverflow thread doesn't address what I'm looking for.

I have defined an auxillary database object in Hibernate like so:

<database-object>
    <create>ALTER TABLE project ADD CONSTRAINT UNIQUE `unique_id` (`userId`);</create>
    <drop></drop>
</database-object>

Upon setting up my database and running against passing tests, I get this error thrown:

01:56:04.337> WARN   ExceptionHandlerLoggedImpl:27 GenerationTarget encountered exception accepting command : Error executing DDL "ALTER TABLE project ADD CONSTRAINT UNIQUE `unique_id` (`userId`);" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "ALTER TABLE project ADD CONSTRAINT UNIQUE `unique_id` (`userId`);" via JDBC Statement
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:458)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:442)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:406)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:169)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:138)
        at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:124)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:164)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:81)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:728)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:746)
        at com.ekotrope.server.jdo.BaseJdoTestClass.startServices(BaseJdoTestClass.java:221)
        at com.ekotrope.server.jdo.BaseJdoTestClass.beforeClass(BaseJdoTestClass.java:112)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
        at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
        at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
        at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
        at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
        at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
        at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:367)
        at org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:274)
        at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:238)
        at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:161)
        at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:290)
        at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:242)
        at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:121)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: UNIQUE
        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
        ... 31 more
Caused by: org.hsqldb.HsqlException: unexpected token: UNIQUE
        at org.hsqldb.error.Error.parseError(Unknown Source)
        at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
        at org.hsqldb.ParserBase.checkIsNonCoreReservedIdentifier(Unknown Source)
        at org.hsqldb.ParserDQL.checkIsSchemaObjectName(Unknown Source)
        at org.hsqldb.ParserDQL.readNewSchemaObjectName(Unknown Source)
        at org.hsqldb.ParserDQL.readNewDependentSchemaObjectName(Unknown Source)
        at org.hsqldb.ParserDDL.compileAlterTable(Unknown Source)
        at org.hsqldb.ParserDDL.compileAlter(Unknown Source)
        at org.hsqldb.ParserCommand.compilePart(Unknown Source)
        at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
        at org.hsqldb.Session.executeDirectStatement(Unknown Source)
        at org.hsqldb.Session.execute(Unknown Source)
        ... 35 more

This is incredibly confusing because executing the above statement within my database (MariaDB) is perfectly valid, yet JDBC doesn't recognize a perfectly fine statement.

I think the name of the constrain should be *before* the UNIQUE statement? Like `ALTER TABLE table ADD CONSTRAINT name UNIQUE (id);` — Jelmen, May 18 '22 at 21:30
Which Hibernate dialect are you using? ([What is the MariaDB dialect class name for Hibernate?](https://stackoverflow.com/q/37066024/12567365)) — andrewJames, May 18 '22 at 21:34
@Jelmen After changing it, the error changes to Caused by: org.hsqldb.HsqlException: unexpected token: — Joseph Stefanik, May 21 '22 at 23:23
@andrewJames we are using org.hibernate.dialect.MariaDB102Dialect with a10.2 MariaDB — Joseph Stefanik, May 21 '22 at 23:24

Hibernate error when executing JDBC statements - unexpected token 'UNIQUE'

0 Answers0