Audit jdbc mysql : syntax error at COM_AUDIT_TRAIL creation

[Marc MAURICE]

Hello all,

I found what may be a bug (in a library).

After configuring jdbc audit and starting cas for the first time, I got
the following error (see full exception below).

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your
SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near 'type=MyISAM' at line 11

engine=MyISAM should be used instead.


The workaround is easy : just run the SQL manually, replacing type= with
engine= .


Let me know if you have any advice on how to report that. It's not very
clear to me how to report issues on CAS.

Anyway, the workaround will be in mailing list archive.


Marc




2020-09-25 15:22:17,380 WARN
[org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl] -
<GenerationTarget encountered exception accepting command : Error
executing DDL "
    create table COM_AUDIT_TRAIL (
       id bigint not null auto_increment,
        AUD_ACTION longtext,
        APPLIC_CD varchar(255),
        AUD_CLIENT_IP varchar(255),
        AUD_DATE datetime not null,
        AUD_RESOURCE longtext,
        AUD_SERVER_IP varchar(255),
        AUD_USER longtext,
        primary key (id)
    ) type=MyISAM" via JDBC Statement>
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error
executing DDL "
    create table COM_AUDIT_TRAIL (
       id bigint not null auto_increment,
        AUD_ACTION longtext,
        APPLIC_CD varchar(255),
        AUD_CLIENT_IP varchar(255),
        AUD_DATE datetime not null,
        AUD_RESOURCE longtext,
        AUD_SERVER_IP varchar(255),
        AUD_USER longtext,
        primary key (id)
    ) type=MyISAM" via JDBC Statement
        at
org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1249)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        at
org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58)
~[spring-orm-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
~[spring-orm-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391)
~[spring-orm-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378)
~[spring-orm-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
~[spring-orm-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
~[spring-beans-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108)
~[spring-context-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868)
~[spring-context-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
~[spring-context-5.2.6.RELEASE.jar!/:5.2.6.RELEASE]
        at
org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)
~[spring-boot-2.2.8.RELEASE.jar!/:2.2.8.RELEASE]
        at
org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
~[spring-boot-2.2.8.RELEASE.jar!/:2.2.8.RELEASE]
        at
org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
~[spring-boot-2.2.8.RELEASE.jar!/:2.2.8.RELEASE]
        at
org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
~[spring-boot-2.2.8.RELEASE.jar!/:2.2.8.RELEASE]
        at
org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:140)
~[spring-boot-2.2.8.RELEASE.jar!/:2.2.8.RELEASE]
        at
org.apereo.cas.web.CasWebApplication.main(CasWebApplication.java:80)
~[cas-server-webapp-init-6.2.2.jar!/:6.2.2]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method) ~[?:?]
        at
jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[?:?]
        at
jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at
org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
~[cas.war:?]
        at
org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
~[cas.war:?]
        at
org.springframework.boot.loader.Launcher.launch(Launcher.java:51)
~[cas.war:?]
        at
org.springframework.boot.loader.WarLauncher.main(WarLauncher.java:58)
~[cas.war:?]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your
SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near 'type=MyISAM' at line 11
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at
com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at
com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at
com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
~[HikariCP-3.4.5.jar!/:?]
        at
com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
~[HikariCP-3.4.5.jar!/:?]
        at
org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
~[hibernate-core-5.4.16.Final.jar!/:5.4.16.Final]
        ... 41 more

[Shane Claggett]

Bumping this old thread because I'm encountering the same problem. As mentioned by Marc, the solution is to manually create the tables using the MySQL command line:

  create table locks (
    application_id varchar(255) not null,
    expiration_date datetime,
    unique_id varchar(255),
    lock_Ver integer DEFAULT 0 not null,
    primary key (application_id)
  ) engine=MyISAM;

  create table Cas_Tickets (
    id longtext not null,
    body text,
    creation_Time datetime not null,
    parent_Id longtext,
    principal_Id longtext,
    type longtext not null,
    primary key (id(200))
  ) engine=MyISAM;

Does the CAS project have an issue tracker? If so, I'd be happy to open an issue relating to this.