Announcement Announcement Module
Collapse
No announcement yet.
Unknown table 'sequences' in information_schema Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Unknown table 'sequences' in information_schema

    Hi all,

    I'm trying to execute a little demo of Spring + Hibernate using MySQL as database and I'm not able to run it (same code using H2 works perfect).

    app-context.xml
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    	xsi:schemaLocation="
    		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
    		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
    		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    
    	 <import resource="hibernate.xml" />  
    
    	<tx:annotation-driven transaction-manager="transactionManager" />
    	
    	<context:component-scan base-package="edu.elpasmo.spring" />
    
    </beans>
    hibernate.xml
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    	xsi:schemaLocation="
    		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
    		
    	<bean
            class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
            <property name="location">
                <value>META-INF/spring/application.properties</value>
            </property>
        </bean>
    
        <bean id="dataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="${jdbc.driverClassName}" />
            <property name="url" value="${jdbc.url}" />
            <property name="username" value="${jdbc.username}" />
            <property name="password" value="${jdbc.password}" />
        </bean>
    
        <bean id="sessionFactory"
            class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    
            <property name="dataSource" ref="dataSource" />
            <property name="annotatedClasses">
                <list>
                    <value>edu.elpasmo.spring.hibernate.model.Element</value>
                </list>
            </property>
            <property name="hibernateProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
                    <prop key="hibernate.show_sql">true</prop>
                    <prop key="hibernate.hbm2ddl.auto">create</prop>
                </props>
            </property>
        </bean>
    </beans>
    Element.java
    Code:
    @Entity
    public class Element {
    	private Integer elementId;
    	
    	public Element() {
    
    	}
    	
    	@Id
    	@GeneratedValue(strategy = GenerationType.AUTO)
    	@Column(name = "ID")
    	public Integer getElementId() {
    		return elementId;
    	}
    	public void setElementId(Integer elementId) {
    		this.elementId = elementId;
    	}
    }
    The table is created right. The problem is in the first insert: Unknown table 'sequences' in information_schema.

    My system:
    Ubuntu 11.04 64 bits
    STS 2.8.0
    mysql-connector-java 5.1.10 (I'd also tried with version 5.10.18).
    MySQL Version 5.1.54
    openjdk-6-jdk Version 6b22-1.10.2

    I've tried with all the GenerationType for Element.id (SEQUENCE, IDENTITY, etc...).
    I've tried switching to a H2 database and works perfectly.
    I've tried also upgrading Hibernate from 3.6.0 to 3.6.7 with no joy.

    I'll post the console output in the first reply due the post length limitation.
    Last edited by elpasmo; Oct 24th, 2011, 04:57 AM. Reason: Added test with hibernate 3.6.7

  • #2
    Console
    Code:
    Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'elementDao': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire method: public void edu.elpasmo.spring.hibernate.dao.ElementDAO.setSessionFactory(org.hibernate.SessionFactory); nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactoryBean' defined in class path resource [edu/elpasmo/spring/HibernateConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC operation; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'sequences' in information_schema
    	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:288)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1074)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:517)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293)
    	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290)
    	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
    	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:585)
    	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
    	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
    	at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
    	at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
    	at edu.elpasmo.spring.hibernate.app.RunHibernate.main(RunHibernate.java:17)
    Caused by: org.springframework.beans.factory.BeanCreationException: Could not autowire method: public void edu.elpasmo.spring.hibernate.dao.ElementDAO.setSessionFactory(org.hibernate.SessionFactory); nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactoryBean' defined in class path resource [edu/elpasmo/spring/HibernateConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC operation; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'sequences' in information_schema
    	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:595)
    	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:84)
    	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:285)
    	... 13 more
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactoryBean' defined in class path resource [edu/elpasmo/spring/HibernateConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC operation; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'sequences' in information_schema
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1420)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
    	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:293)
    	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
    	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:290)
    	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:192)
    	at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:848)
    	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:790)
    	at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:707)
    	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:553)
    	... 15 more
    Caused by: org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC operation; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'sequences' in information_schema
    	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:94)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:442)
    	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:414)
    	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
    	at org.springframework.orm.hibernate3.LocalSessionFactoryBean.updateDatabaseSchema(LocalSessionFactoryBean.java:935)
    	at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterSessionFactoryCreation(LocalSessionFactoryBean.java:883)
    	at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:213)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
    	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
    	... 25 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'sequences' in information_schema
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    	at com.mysql.jdbc.Util.getInstance(Util.java:386)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
    	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
    	at org.hibernate.tool.hbm2ddl.DatabaseMetadata.initSequences(DatabaseMetadata.java:151)
    	at org.hibernate.tool.hbm2ddl.DatabaseMetadata.<init>(DatabaseMetadata.java:69)
    	at org.hibernate.tool.hbm2ddl.DatabaseMetadata.<init>(DatabaseMetadata.java:62)
    	at org.springframework.orm.hibernate3.LocalSessionFactoryBean$1.doInHibernate(LocalSessionFactoryBean.java:940)
    	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    	... 31 more

    Comment


    • #3
      For starters use the correct MySQL dialect (Mysql5InnoDB) also make sure you have a correct jdbc url which includes the database you want to use, which shouldn't be the default or mysql system tables (which is what it currently seems to do). Also what is that HibernateConfiguration class you use?!

      Comment


      • #4
        Yes, I've tried also with org.hibernate.dialect.MySQLInnoDBDialect and with org.hibernate.dialect.MySQLMyISAMDialect with no results.

        I'm sure the database is the correct one because it creates the table Element.

        And I'm pretty new with Spring and Hibernate so I'm not sure I understand correctly your third question (what is that HibernateConfiguration class you use?). I have the database connection in a resource file (application.properties), I've extracted the hibernate configuration to a separate XML file (hibernate.xml) for clarity. Is it wrong?

        Comment


        • #5
          Check your stack trace

          Code:
          Error creating bean with name 'sessionFactoryBean' defined in class path resource [edu/elpasmo/spring/HibernateConfiguration.class]
          That class. It looks like you have duplicate configuration. So imho there is something seriously wrong with your configuration, before starting to cut everything in pieces and you don't know what you are doing (yet) simply create a single xml file (there isn't much in there yet). Saves you a lot of headaches.

          Comment


          • #6
            Thank you a lot Marten. That was the problem. HibernateConfiguration is a class created by the template "Simple Spring Hibernate Utility Project" and I forgot it. Removing it from the project solves the problem.

            Again, thank you. And forgive my ignorance, pretty new in all that stuff

            Comment

            Working...
            X