Problem with connection to MySql

[jjbueno]

Hi all,

I have the following script:

project --topLevelPackage com.nomina

persistence setup --provider HIBERNATE --database MYSQL --databaseName nomina --userName root --password 123456

entity --class ~.Estado --testAutomatically --table estado

field string --fieldName stEstaCodigo --column esta_Codigo --notNull --sizeMin 1 --sizeMax 4
field string --fieldName stEstaDescripcion --column esta_Descripcion --notNull --sizeMin 1 --sizeMax 20
field string --fieldName stEstaObservacion --column esta_Observacion --notNull --sizeMax 2000


controller all --package ~.web

database.properties file:
#Updated at Tue Nov 16 22:20:30 COT 2010
#Tue Nov 16 22:20:30 COT 2010
database.password=123456
database.url=jdbc:mysql://localhost:3306/nomina
database.username=root
database.driverClassName=com.mysql.jdbc.Driver


I also tested:

#Updated at Tue Nov 16 22:20:30 COT 2010
#Tue Nov 16 22:20:30 COT 2010
database.password=123456
database.url=jdbc\:mysql\://localhost\:3306/nomina
database.username=root
database.driverClassName=com.mysql.jdbc.Driver

persistence.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence </provider>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
<!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
<property name="hibernate.hbm2ddl.auto" value="validate"/>
<property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
<!-- Uncomment the following two properties for JBoss only -->
<!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
<!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
</properties>
</persistence-unit>
</persistence>



Compile it all, and when I run it from the browser loads the welcome page and trying to add a record to me the following error:

http://localhost:8080/nomina


Exception Message:
org.hibernate.exception.SQLGrammarException: could not insert: [com.nomina.Estado]; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [com.nomina.Estado]

Exception Stack Trace:
org.springframework.orm.jpa.EntityManagerFactoryUt ils.convertJpaAccessExceptionIfPossible(EntityMana gerFactoryUtils.java:311)
org.springframework.orm.jpa.aspectj.JpaExceptionTr anslatorAspect.ajc$afterThrowing$org_springframewo rk_orm_jpa_aspectj_JpaExceptionTranslatorAspect$1$ 18a1ac9(JpaExceptionTranslatorAspect.aj:15)
com.nomina.Estado_Roo_Entity.ajc$interMethod$com_n omina_Estado_Roo_Entity$com_nomina_Estado$persist( Estado_Roo_Entity.aj:58)
com.nomina.Estado.persist(Estado.java:1)
com.nomina.Estado_Roo_Entity.ajc$interMethodDispat ch1$com_nomina_Estado_Roo_Entity$com_nomina_Estado $persist(Estado_Roo_Entity.aj)
com.nomina.web.EstadoController_Roo_Controller.ajc $interMethod$com_nomina_web_EstadoController_Roo_C ontroller$com_nomina_web_EstadoController$create(E stadoController_Roo_Controller.aj:36)
com.nomina.web.EstadoController.create(EstadoContr oller.java:1)
sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.web.bind.annotation.support.Ha ndlerMethodInvoker.invokeHandlerMethod(HandlerMeth odInvoker.java:176)
org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.invokeHandlerMethod(An notationMethodHandlerAdapter.java:426)
org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.handle(AnnotationMetho dHandlerAdapter.java:414)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:790)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:719)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:644)
org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:560)
javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.web.filter.CharacterEncodingFi lter.doFilterInternal(CharacterEncodingFilter.java :88)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.springframework.orm.jpa.support.OpenEntityMana gerInViewFilter.doFilterInternal(OpenEntityManager InViewFilter.java:113)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)
org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)
org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:127)
org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)
org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:298)
org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:852)
org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:588)
org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:489)
java.lang.Thread.run(Thread.java:619)


thanks for the quick responses.

[jjbueno]

Hi All,

I forgot I'm using Spring Roo 1.1.0 Release

[krimple]

I see you've set the ddl option to validate - that will just make sure the tables are already created properly, and not actually create the table. I assume then the table is already created?

Maybe you can turn up logging for the database and get more details. That may help diagnose it. I doubt the root user is disabled from creating entries in a table with an INSERT.

You can ramp logging up for the ORM layer, but I don't think it will tell you the problem:
logging setup --level DEBUG --package PERSISTENCE

Edit your log4j.properties file and add these - the first one will debug all SQL statements going out of your server. You can remove .SQL and get everything Hibernate is doing.

The second one will log parameter bindings so you can see the datatype.

# log sql statements
log4j.logger.org.hibernate.SQL=debug

# log sql bindings
log4j.logger.org.hibernate.type = debug


That should help a bit with showing what is causing the problem. I tried the same setup and it didn't give me an error, but I also didn't change my hbm2ddl.auto away from create to validate. I suspect if the table doesn't have a version and a long primary key it is going to fail an insert, but likely it wouldn't deploy the entity manager because the verification step would fail too...

Ken

[jjbueno]

Hi Ken

First, thank you very much for your help so quickly.

I clarified that the tables are already created in the database and has records and field esta_Codigo is the primary key.

I set the parameters that you told me, and when you run the program tells me the following:



The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.)
2010-11-17 12:52:32,447 [http-8080-5] DEBUG org.hibernate.SQL - insert into estado (esta_codigo, esta_descripcion, esta_observacion, version) values (?, ?, ?, ?)
2010-11-17 12:52:32,509 [http-8080-5] ERROR org.hibernate.util.JDBCExceptionReporter - Unknown column 'version' in 'field list'

Jairo

[MiB]

Can you change the DB or is it in use already? It seems to me there's likely a mismatch between the Hibernate schema and the MySQL db. Change to

<property name="hibernate.hbm2ddl.auto" value="update"/>

in persistence.xml if you have valuable data in the db or to

<property name="hibernate.hbm2ddl.auto" value="create"/>

if the data can be lost.

If you have valuable data in there you should of course back up the content before you make changes to the schema structure.

[MiB]

Btw, What version of MySQL 5 are you using?

[rav09]

I had similar problem. Ensure, if you have empty table 'nomina' in your db