HSQL (in memmory) creation probelm

Help
Negroscuro
2013-04-16
2014-01-19
  • Negroscuro

    Negroscuro - 2013-04-16

    Hello,
    I am using this spring configuration:

     <beans:bean id="eo2heaven_dbs" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <beans:property name="driverClassName" value="org.hsqldb.jdbcDriver" />
            <beans:property name="url" value="jdbc:hsqldb:mem:eo2heaven_dbs" />
            <beans:property name="username" value="sa" />
            <beans:property name="password" value="" />
        </beans:bean>
    
        <jdbc:initialize-database data-source="eo2heaven_dbs">
            <jdbc:script location="classpath:db/create-db.txt" /> 
             <jdbc:script location="classpath:db/populate-db.txt" />
        </jdbc:initialize-database>
    

    But when I start my server I get:

    2013-04-16 11:55:09 DefaultListableBeanFactory [INFO] Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@493dca: defining beans [runMode,eo2heaven_dbs,org.springframework.jdbc.datasource.init.DataSourceInitializer#0,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor,downloadController,downloadManager,envParametersController,layersController,mailController,mapViewerController,roseModelController,userController,WMCController,WMCManager,WPSController,entityManagerFactory,jpaAdapter,org.springframework.beans.factory.config.PropertyPlaceholderConfigurer#0,mailManager,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,sessionFactory,genericDAO,userManager,roseModel,org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping#0,org.springframework.format.support.FormattingConversionServiceFactoryBean#0,org.springframework.validation.beanvalidation.LocalValidatorFactoryBean#0,org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter#0,org.springframework.web.servlet.handler.MappedInterceptor#0,org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#0,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#0,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#1,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#1,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#2,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#2,org.springframework.web.servlet.view.InternalResourceViewResolver#0]; parent: org.springframework.beans.factory.support.DefaultListableBeanFactory@1e0512a
    2013-04-16 11:55:09 DriverManagerDataSource [INFO] Loaded JDBC driver: org.hsqldb.jdbcDriver
    2013-04-16 11:55:09 ResourceDatabasePopulator [INFO] Executing SQL script from class path resource [db/create-db.txt]
    2013-04-16 11:55:09 DefaultListableBeanFactory [INFO] Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@493dca: defining beans [runMode,eo2heaven_dbs,org.springframework.jdbc.datasource.init.DataSourceInitializer#0,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.springframework.context.annotation.internalPersistenceAnnotationProcessor,downloadController,downloadManager,envParametersController,layersController,mailController,mapViewerController,roseModelController,userController,WMCController,WMCManager,WPSController,entityManagerFactory,jpaAdapter,org.springframework.beans.factory.config.PropertyPlaceholderConfigurer#0,mailManager,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,sessionFactory,genericDAO,userManager,roseModel,org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping#0,org.springframework.format.support.FormattingConversionServiceFactoryBean#0,org.springframework.validation.beanvalidation.LocalValidatorFactoryBean#0,org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter#0,org.springframework.web.servlet.handler.MappedInterceptor#0,org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#0,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#0,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#1,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#1,org.springframework.web.servlet.resource.ResourceHttpRequestHandler#2,org.springframework.web.servlet.handler.SimpleUrlHandlerMapping#2,org.springframework.web.servlet.view.InternalResourceViewResolver#0]; parent: org.springframework.beans.factory.support.DefaultListableBeanFactory@1e0512a
    2013-04-16 11:55:09 DispatcherServlet [ERROR] Context initialization failed
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: Failed to populate database; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [db/create-db.txt]: CREATE TABLE FIELD (  fieldID INT PRIMARY KEY,  name VARCHAR(200),  type VARCHAR(200) )
        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:291)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
        at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:442)
        at org.springframework.web.servlet.FrameworkServlet.createWebApplicationContext(FrameworkServlet.java:458)
        at org.springframework.web.servlet.FrameworkServlet.initWebApplicationContext(FrameworkServlet.java:339)
        at org.springframework.web.servlet.FrameworkServlet.initServletBean(FrameworkServlet.java:306)
        at org.springframework.web.servlet.HttpServletBean.init(HttpServletBean.java:127)
        at javax.servlet.GenericServlet.init(GenericServlet.java:212)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1173)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:993)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4420)
        at org.apache.catalina.core.StandardContext.start(StandardContext.java:4733)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
        at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
        at org.apache.catalina.core.StandardService.start(StandardService.java:525)
        at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
    Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to populate database; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [db/create-db.txt]: CREATE TABLE FIELD (  fieldID INT PRIMARY KEY,  name VARCHAR(200),  type VARCHAR(200) )
        at org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:92)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1477)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
        ... 32 more
    Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [db/create-db.txt]: CREATE TABLE FIELD (  fieldID INT PRIMARY KEY,  name VARCHAR(200),  type VARCHAR(200) )
        at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:183)
        at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:120)
        at org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:80)
        ... 34 more
    Caused by: java.sql.SQLSyntaxErrorException: nombre del objeto ya existe: FIELD in statement [CREATE TABLE FIELD (  fieldID INT PRIMARY KEY,  name VARCHAR(200),  type VARCHAR(200) )]
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
        at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:169)
        ... 36 more
    [b]Caused by: org.hsqldb.HsqlException: nombre del objeto ya existe: FIELD --> NAME OF THE OBJECT ALREADY EXISTS[/b]
        at org.hsqldb.error.Error.error(Unknown Source)
        at org.hsqldb.error.Error.error(Unknown Source)
        at org.hsqldb.SchemaObjectSet.checkAdd(Unknown Source)
        at org.hsqldb.SchemaManager.checkSchemaObjectNotExists(Unknown Source)
        at org.hsqldb.StatementSchema.setOrCheckObjectName(Unknown Source)
        at org.hsqldb.StatementSchema.getResult(Unknown Source)
        at org.hsqldb.StatementSchema.execute(Unknown Source)
        at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
        at org.hsqldb.Session.executeDirectStatement(Unknown Source)
        at org.hsqldb.Session.execute(Unknown Source)
        ... 39 more
    16-abr-2013 11:55:09 org.apache.catalina.core.ApplicationContext log
    GRAVE: StandardWrapper.Throwable
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: Failed to populate database; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [db/create-db.txt]: CREATE TABLE FIELD (  fieldID INT PRIMARY KEY,  name VARCHAR(200),  type VARCHAR(200) )
    

    I don't understand why if I am using memmory HSQL database do I have existing tables? I thought about being executting the script of tables creation twice but I didnt found any more reference to it.

    Any help will be really appreciated,
    thank you in advance

     
  • Fred Toussi

    Fred Toussi - 2013-04-16

    It is probably because you are running several tests in a single process. The tables were created in the last test. The database was not shutdown after the last test. You need to shutdown the database or drop the schemas containing the tables after each test.

     
  • Negroscuro

    Negroscuro - 2013-04-17

    Well i thought I was using a memmory configuration o HSQL:
    <beans:property name="url" value="jdbc:hsqldb:mem:eo2heaven_dbs" />
    So I thought there is no need for such a thing… anyway I tried doing:
    <beans:property name="url" value="jdbc:hsqldb:file:/systempath/eo2heaven_dbs" />
    And it doesnt work… in fact I changed the systempath I gave in the configuration sentence to have a new database being created but the error occurrs.
    Thank for your help.

     
  • Fred Toussi

    Fred Toussi - 2013-04-17

    Sure your configuration creates a memory database. You can continue using this type of database. But mem: databases do not disappear if your process continues to exist. You need to make sure the database schmea is created, used and dropped by either shutting down the mem: database or by dropping the schema.

     
  • Negroscuro

    Negroscuro - 2013-04-17

    I understand… anyway if I comment:

    <jdbc:initialize-database data-source="eo2heaven_dbs">
        <jdbc:script location="classpath:db/create-db.txt" /> 
         <jdbc:script location="classpath:db/populate-db.txt" />
    </jdbc:initialize-database>
    

    And I got this other error while trying to get logged in the deployed application:

    2013-04-17 13:41:48 EO2AuthenticationProcesingFilter [DEBUG] Authentication request failed: org.springframework.security.authentication.AuthenticationServiceException: PreparedStatementCallback; bad SQL grammar [select username,password,enabled from users where username = ?]; nested exception is java.sql.SQLSyntaxErrorException: usuario no tiene privilegios suficientes o objeto no encontrado: USERS
    2013-04-17 13:41:48 EO2AuthenticationProcesingFilter [DEBUG] Updated SecurityContextHolder to contain null Authentication
    

    I think this is because the USERS table doesnt exist so there is no chance to check if the user and password I am providing to determine allowing access or not to the application…
    I mean … there are no tables and no registers inserted in the HSQL database… since I checked it using the file configuration instead the mmemory configuration for HSQL and using the manager application I downloaded from HSQL site ("runmanager.bat").

     
  • Fred Toussi

    Fred Toussi - 2013-05-15

    You can start your script with:

    DROP SCHEMA PUBLIC CASCADE

    Then it will clear all the tables and other objects if they already exist and you won't get the error.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks