Error creating Hibernate tables

Question

I’m getting the following Exception when creating tables for my application:

Hibernate: create table UserConfig (id bigint not null, dateReg datetime, status varchar(255), accountActivationCurrentToken varchar(10000), forgotPasswordCurrentToken varchar(10000), loginCurrentToken varchar(10000), primary key (id)) engine=MyISAM
06-Feb-2018 15:00:22.208 WARN [http-nio-80-exec-25] org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl.handleException 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)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:313)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
    at br.com.wsbasestructure.dao.abstracts.Connection.<init>(Connection.java:35)
    at br.com.wsbasestructure.app.listener.AppContextListener.contextInitialized(AppContextListener.java:29)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4853)
Hibernate: alter table Credit_Agreements add constraint UK_i7e61das28j9ypun0u5guh4ai unique (agreements_id)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5314)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:145)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:753)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:729)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
    at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:587)
    at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:461)
    at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1612)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:300)
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
    at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
    at org.apache.catalina.manager.ManagerServlet.check(ManagerServlet.java:1451)
    at org.apache.catalina.manager.ManagerServlet.deploy(ManagerServlet.java:897)
Hibernate: alter table Credit_Assurances add constraint UK_jrxtlgawg0bf8a40tgt1p6fe5 unique (assurances_id)
    at org.apache.catalina.manager.ManagerServlet.doGet(ManagerServlet.java:335)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:616)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
Hibernate: alter table Credit_Receipts add constraint UK_2knjb2x4ydv4thrskys915uv5 unique (receipts_id)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
    at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:891)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:795)
    at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 57 more

Here is my hibernate.cfg:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mezinimi?useSSL=false&amp;useTimezone=true&amp;serverTimezone=UTC</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">123456</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.hbm2ddl.auto">create</property>
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.min_size">3</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.timeout">1800</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">10</property>
        <mapping class="br.com.wsbasestructure.domain.impl.Notification"/>
        <mapping class="br.com.wsbasestructure.domain.impl.UserConfig"/>
        <mapping class="br.com.wsbasestructure.domain.impl.Change"/>
        <mapping class="br.com.mezinimi.domain.impl.Collaborator"/>
        <mapping class="br.com.mezinimi.domain.impl.Address"/>
        <mapping class="br.com.mezinimi.domain.impl.Customer"/>
        <mapping class="br.com.mezinimi.domain.impl.Credit"/>
        <mapping class="br.com.mezinimi.domain.impl.Document"/>
    </session-factory>
</hibernate-configuration>

I’m using Maven, this is my pom.xml:

<dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-c3p0</artifactId>
            <version>5.2.12.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.12.Final</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.jersey.bundles</groupId>
            <artifactId>jaxrs-ri</artifactId>
            <version>2.26</version>
            <type>tar.bz2</type>
            <classifier>src</classifier>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>
        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>javax.mail</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>io.jsonwebtoken</groupId>
            <artifactId>jjwt</artifactId>
            <version>0.9.0</version>
        </dependency>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>8.0</version>
        </dependency>
    </dependencies>

My class UserConfig:

@Entity
public class UserConfig extends br.com.wsbasestructure.domain.abstracts.Entity{

    private String forgotPasswordCurrentToken;
    private String accountActivationCurrentToken;
    private String loginCurrentToken;

    @Column(length = 10000)
    public String getForgotPasswordCurrentToken() {
        return forgotPasswordCurrentToken;
    }

    public void setForgotPasswordCurrentToken(String forgotPasswordCurrentToken) {
        this.forgotPasswordCurrentToken = forgotPasswordCurrentToken;
    }

    @Column(length = 10000)
    public String getAccountActivationCurrentToken() {
        return accountActivationCurrentToken;
    }

    public void setAccountActivationCurrentToken(String accountActivationCurrentToken) {
        this.accountActivationCurrentToken = accountActivationCurrentToken;
    }

    @Column(length = 10000)
    public String getLoginCurrentToken() {
        return loginCurrentToken;
    }

    public void setLoginCurrentToken(String loginCurrentToken) {
        this.loginCurrentToken = loginCurrentToken;
    }

}

I’ve tried to change the dialect but I was unsuccessful, I have the same application running on a linux, and functioning normally.

Answer 1

That is the mistake:

Row size Too large. The Maximum Row size for the used table type, not Counting Blobs, is 65535. This includes Storage overhead, check the manual. You have to change some Columns to TEXT or Blobs

You are exceeding the maximum size of a tuple which, according to the documentation of Mysql, is 65.535 bytes.

The space used by a VARCHAR, according to the documentation Mysql, depends on its length (length) and of Character set used. If you are using the Character set UTF-8, for example, each character can occupy up to 3 bytes, plus 1 or 2 bytes which store the size of the String. This means that each of your columns of 10,000 characters in length can occupy up to 32,002 bytes.

To circumvent this limitation, you should use columns of the type BLOB or TEXT, since they only occupy a value between 9 and 12 bytes because they are not stored in the tuple.

In this case your class notes UserConfig would look like this:

@Entity
public class UserConfig extends br.com.wsbasestructure.domain.abstracts.Entity{

    private String forgotPasswordCurrentToken;
    private String accountActivationCurrentToken;
    private String loginCurrentToken;

    @Column(length = 10000, columnDefinition = "TEXT")
    public String getForgotPasswordCurrentToken() {
        return forgotPasswordCurrentToken;
    }

    public void setForgotPasswordCurrentToken(String forgotPasswordCurrentToken) {
        this.forgotPasswordCurrentToken = forgotPasswordCurrentToken;
    }

    @Column(length = 10000, columnDefinition = "TEXT")
    public String getAccountActivationCurrentToken() {
        return accountActivationCurrentToken;
    }

    public void setAccountActivationCurrentToken(String accountActivationCurrentToken) {
        this.accountActivationCurrentToken = accountActivationCurrentToken;
    }

    @Column(length = 10000, columnDefinition = "TEXT")
    public String getLoginCurrentToken() {
        return loginCurrentToken;
    }

    public void setLoginCurrentToken(String loginCurrentToken) {
        this.loginCurrentToken = loginCurrentToken;
    }

}