java org.hibernate.tool.schema.spi.CommandAcceptanceException：通过 JDBC 语句执行 DDL 时出错

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement

提问by Andrea Perelli

I really need help I searched in all the questions I found on stackoverflow and nothing works. I have never used hibernate before and I don't know what I am doing wrong. I generated tables but with insertions I have problems.

我真的需要帮助，我搜索了我在 stackoverflow 上发现的所有问题，但没有任何效果。我以前从未使用过休眠，我不知道我做错了什么。我生成了表格，但插入时遇到了问题。

I get this error:

我收到此错误：

20-May-2017 10:53:41.085 WARN [http-nio-8080-exec-1] 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.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:525)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:470)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:429)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:245)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:309)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:445)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:726)
    at gameplatform.servlet.IndexPage.init(IndexPage.java:46)
    at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1183)
    at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1099)
    at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:779)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:133)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    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)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Cannot add foreign key constraint
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2497)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2455)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 32 more

this is my configurations file:

这是我的配置文件：

<?xml version="1.0" encoding="UTF-8"?>

<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:8889/gameplatform</property>
        <property name="connection.username">root</property>
        <property name="connection.password">root</property>
        <property name="connection.pool_size">100</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL55Dialect</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.hbm2ddl.auto">update</property>

        <mapping class="db.table.template.Permesso"/>
        <mapping class="db.table.template.PermessoTemplate"/>
        <mapping class="db.table.template.PermessoTemplateId"/>
        <mapping class="db.table.template.Template"/>
        <mapping class="db.table.template.Gruppo"/>
        <mapping class="db.table.template.Utente"/>
        <mapping class="db.table.template.Livello"/>
        <mapping class="db.table.template.Gioco"/>
        <mapping class="db.table.template.Giocare"/>
        <mapping class="db.table.template.GiocareId"/>
        <mapping class="db.table.template.Trofeo"/>
    </session-factory>
</hibernate-configuration>

this is my test page:

这是我的测试页面：

package gameplatform.servlet
public class TestPage extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public TestPage() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        response.getWriter().append("Served at: ").append(request.getContextPath());

    Configuration conf = new Configuration().configure();
    Session session = conf.buildSessionFactory().getCurrentSession();
    session.beginTransaction();

    Permesso permesso = new Permesso();
    permesso.setNome("Index");
    permesso.setIndirizzo("gameplatform.op");

    Template template = new Template("slider.jsp");
    session.saveOrUpdate(template);

    PermessoTemplate permessoTemplate = new PermessoTemplate();
    permessoTemplate.setPermesso(permesso);
    permessoTemplate.setTemplate(template);
    permessoTemplate.setPriority(3);

    permesso.getPermessoTemplate().add(permessoTemplate);

    session.saveOrUpdate(permesso);

    Query query = session.createQuery("from Template template join template.permessoTemplate joinPage where joinPage.pk.permesso = 'Index' order by joinPage.priority");
    List<Template> temp = query.list();

    Iterator it = temp.iterator();
    while (it.hasNext()) { 
        Object[] obj = (Object[]) it.next();
        Template test = (Template) obj[0];
        response.getWriter().append(" " + test.getIndirizzo());          
    }

    Gruppo test = new Gruppo("Admin");
    session.saveOrUpdate(test);

    Utente user = new Utente("Romolo", "De Roma", 20, "Er Zezzo", "Prova", "[email protected]", 3000, 3);
    user.setGruppo(test);
    test.getUtente().add(user);
    test.getPermesso().add(permesso);

    session.saveOrUpdate(user);

    /*Recensione recensione = new Recensione();
    recensione.setDescrizione("Gioco Bello");
    recensione.setVoto(4);
    recensione.setUtente(user);

    session.saveOrUpdate(recensione);*/

    Query control = session.createQuery("from Livello");
    List<Livello> controlList = control.list();

    boolean bool = false;
    Iterator iter = controlList.iterator();
    while (iter.hasNext()) { 
        //Object[] obj = (Object[]) 
        Livello lv = (Livello) iter.next();

        if (lv.getLivello()==3 && lv.getUtente().getUsername().equals(user.getUsername())){
            bool=true;
        }
    }
    if (!bool){
        Livello livello = new Livello();
        livello.setLivello(3);
        livello.setDate(new GregorianCalendar(2017,5,20));
        livello.setUtente(user);

        session.saveOrUpdate(livello);
    }

    Gioco gioco = new Gioco();
    gioco.setNome("Zezzo");
    gioco.setDescrizione("Bel Gioco Di Merda");
    gioco.setSpecifiche("2Gb di Rom");

    Giocare giocare = new Giocare();
    giocare.setExp(3000);
    giocare.setMinuti(new Time(2000));
    giocare.setNumAccessi(30);
    giocare.setRecensione("Bello ma Brutto");
    giocare.setVoto(5);
    giocare.setUtente(user);
    giocare.setGioco(gioco);

    gioco.getGiocare().add(giocare);

    session.saveOrUpdate(gioco);

    Trofeo trofeo = new Trofeo();
    trofeo.setNome("Test");
    trofeo.setIcona("icona.jpg");
    trofeo.setObiettivo(5);
    trofeo.setGioco(gioco);
    user.getTrofeo().add(trofeo);

    session.saveOrUpdate(trofeo);

    session.getTransaction().commit();

}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
}}

this is my IndexPage:

这是我的索引页：

package gameplatform.servlet;

    public class IndexPage extends HttpServlet {
    private static final long serialVersionUID = 1L;

    private String pageName;
    private String[] template;

/**
 * @see HttpServlet#HttpServlet()
 */
public IndexPage() {
    super();
    // TODO Auto-generated constructor stub
}

public void init(ServletConfig config) throws ServletException {

    // TODO Auto-generated method stub
    super.init(config);
    this.pageName = getInitParameter("pageIndex");

    Configuration conf = new Configuration().configure();
    Session session = conf.buildSessionFactory().getCurrentSession();
    session.beginTransaction();

    String query = "from Template template join template.permessoTemplate joinPage where joinPage.pk.permesso = ? order by joinPage.priority";
    List execute = session.createQuery(query).setString(0, pageName).list();

    session.getTransaction().commit();

    Iterator it = execute.iterator();
    int i = 0;
    this.template = new String[execute.size()];
    while (it.hasNext()) { 
        Object[] obj = (Object[]) it.next();
        Template temp = (Template) obj[0];  ;
        this.template[i] = temp.getIndirizzo();
        i++;
    }

}

/**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 */
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    process(request, response);
}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    process(request, response);
}

private void process(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    request.setAttribute("template", this.template);

    RequestDispatcher view = request.getRequestDispatcher("JSP/index.jsp");
    view.forward(request, response);
}}

This is my SQL logs:

这是我的 SQL 日志：

    2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."giocare" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."gioco" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."gruppo" but the required 
system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."permessogruppo" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."trofeo" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."utente" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 10:53:45 70000ed64000 InnoDB: Error: Fetch of persistent statistics requested for table "gameplatform"."utentetrofeo" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2017-05-20 11:54:17 70000ecdc000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2017-05-20 11:54:46 70000ec54000 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 

回答by Vivek Gajbhiye

The problem is in your dialect engine change to your mysql dialect add the following lines, particularly if you are using spring boot, in your application.properties located under src/main/resources folder.

问题在于您的方言引擎更改为您的 mysql 方言，在位于 src/main/resources 文件夹下的 application.properties 中添加以下行，特别是如果您使用的是 spring boot。

    spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

回答by Sateesh Kumar Anthapu

I had this exception with SprintBoot 2.x, by changing dialect it got resolved. org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement

我在 SprintBoot 2.x 中遇到了这个异常，通过改变方言它得到了解决。org.hibernate.tool.schema.spi.CommandAcceptanceException：通过 JDBC 语句执行 DDL 时出错

回答by zhrist

To be precise, your Hibernate JDBC driver has a problem when executing a script(DDL) toward mysql database.

准确地说，您的 Hibernate JDBC 驱动程序在对 mysql 数据库执行脚本 (DDL) 时存在问题。

In the error, there is proper explanation: there is exception on the SQL query with error "Cannot add foreign key constraint".

在错误中，有正确的解释：SQL 查询出现异常，错误为“无法添加外键约束”。

You have multiple Tables mapped, so I suggest to go one by one and see which table generation is having the Error. This will guide you to understand the Constrain Error that you have.

你有多个表映射，所以我建议一个一个地去看看哪个表生成有错误。这将引导您了解您所拥有的约束错误。

Error log that you provided is not relevant as it shows statistical tables errors that are replaces with other statistic for the db engine execution.

您提供的错误日志不相关，因为它显示了被数据库引擎执行的其他统计信息替换的统计表错误。

Thanks

谢谢

回答by ArifMustafa

I was facing the same issue, and what bring me out from this exception, has been illustrated in below steps -

我遇到了同样的问题，下面的步骤说明了是什么让我摆脱了这个异常——

1. Changed <hibernate-configuration name="">tag to <hibernate-configuration>inside hibernate.cfg.xml file.
2. Always mention connection tag along with the localhost, port and the schema name or database name like - <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/protein_tracker</property>

1. 将<hibernate-configuration name="">标签更改为<hibernate-configuration>hibernate.cfg.xml 文件内。
2. 始终提及连接标记以及本地主机、端口和架构名称或数据库名称，例如 - <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/protein_tracker</property>

and these makes the execution free of exception, it works for me, hope it will also helps you a bit!

这些使得执行没有异常，它对我有用，希望它也能帮助你！