org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
22,129
Solution 1
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.
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
Solution 2
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
Author by
Andrea Perelli
Updated on July 09, 2022Comments
-
Andrea Perelli almost 2 years
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.
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:
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.
-
Andrea Perelli almost 7 yearsI had the errors for each one (table that had a foreign key); I changed the compiler and it works: Eclipse creates the db in the right way without problems. thank you for the help
-
Mathews Sunny over 5 yearsFormat your answer
-
zhrist over 5 yearsYou could add this in problem description, as the db log is misleading. The answer is still one possible problem and I see no reason to down-vote if it is not in your case.
-
Sateesh Kumar Anthapu over 5 yearsspring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect