Categories » Questions and Answers » MySQL DB Script
Combination View Flat View Tree View
Threads [ Previous | Next ]
MySQL DB Script
toggle
MySQL DB Script
9/9/10 8:57 AM
i would like to know where can i find the DB script to create the necessary tables in klaros schema created in MySQL DB.
Flag Flag
RE: MySQL DB Script
configraton
9/7/10 10:05 PM as a reply to Nandula Bala.
Thank you for trying Klaros and sharing your experiences!

Klaros creates all tables and indices on its own when started with an emtpy DB.

After changing the DB-settings, you can just start klaros, which will take a bit longer than
usual. After that all tables and indices are in place.

The documentation does not state that explicitly, there is only this hint:
The database user needs permissions to create, drop and alter tables to properly bootstrap the Klaros-Testmanagement database instance.


We will try to clarify that paragraph for the next release.

Best regards,

Heiner
Flag Flag
RE: MySQL DB Script
9/8/10 8:29 AM as a reply to Heiner Westphal.
Hi Heiner,

Thank you for your response. I am with an impression that a script needs to be run manually from a MySQL Query Browser to create necessary tables. However i have seen in the configuration file within user home directory for hibernate related properties. The additional parameter as part of "hibernate.connection.url" "has create\=true" for DerbyDialect. So should this parameter be included as mandate if we want the schema to be created in MySQL DB. Can you please confirm. The user name and password that i am using for MySQL DB is that of the root user.

Regards
Bala
Flag Flag
RE: MySQL DB Script
9/8/10 9:05 AM as a reply to Nandula Bala.
Hi Nandula,

for a MySQL DB you don't need the url parameter "create=true" (this is a derby specific parameter to create the database file if it doesn't exists), but I suggest that you use the parameter "autoReconnect=true" to avoid timeout problems with Klaros and your MySQL DB.

The complete connection url should look like this:
hibernate.connection.url=jdbc:mysql://localhost:3306/klaros?autoReconnect=true

You have to make sure that the database (in this case "klaros") is already created. Like we mention in the user manual Klaros will not create the database itself. If you now start the Klaros-Testmanagement server Klaros will create all needed tables and the default users so you are able to login.


Regards,

Tobia Schmitt
Flag Flag
RE: MySQL DB Script
9/8/10 12:16 PM as a reply to Tobias Schmitt.
Hi Tobias,

I had created a blank schema with the name "klaros" and the made the changes as suggested and restarted the tomcat instance. I am getting the following when tomcat server is restarted.

INFO LocalSessionFactoryBean:730 - Building new Hibernate SessionFactory
ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect
ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect
ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect

When i use MySQL Query Browser , a connection is established with the server user id / password being the same. Not sure why the connection is failing. If the MySQL service was not running i would not have got connected to the server using Query Browser. Where is the link missing? Obviously the tables are not getting created in the DB.

Regards
Bala
Attachments: Tomcat_Console.txt (3.1k)
Flag Flag
RE: MySQL DB Script
9/8/10 12:26 PM as a reply to Nandula Bala.
Hi,

can you please send your "catalina.out" from the log folder of your klaros installation and the "hibernate.properties" from the klaros home folder to support@verit.de? Maybe the log file contains more information about the problem.

Regards,

Tobias Schmitt
Flag Flag
RE: MySQL DB Script
9/8/10 3:24 PM as a reply to Tobias Schmitt.
Hi

Sent the details to the email id mentioned by you.

Regards
Bala
Flag Flag
RE: MySQL DB Script
9/8/10 3:52 PM as a reply to Nandula Bala.
Hi again,

I checked the "hibernate.properties" you sent us and found a douplicated entry. You have two "hibernate.connection.password" entries with different values in your configuration file. I tested it with my local installation and got the same error as you. So please remove one of the "hibernate.connection.password" entries and try again.

Regards,

Tobias Schmitt
Flag Flag
RE: MySQL DB Script
9/8/10 5:25 PM as a reply to Tobias Schmitt.
Hi Tobias

I did comment the second password and restarted the tomcat server. But i am still getting the same issue. Just to show the stack trace on tomcat server console:
*********************************************************************************************************************************************************************

20:44:30,651 ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
20:45:39,136 ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
20:45:39,142 ERROR JDBCExceptionReporter:101 - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
20:45:39,219 INFO XmlBeanDefinitionReader:323 - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
20:45:39,252 INFO SQLErrorCodesFactory:128 - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
20:46:46,268 WARN SQLErrorCodesFactory:230 - Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not ge
t JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times.
Giving up.)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:293)
at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:216)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:140)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:103)
at org.springframework.orm.hibernate3.SessionFactoryUtils.newJdbcExceptionTranslator(SessionFactoryUtils.java:145)
at org.springframework.orm.hibernate3.HibernateAccessor.getDefaultJdbcExceptionTranslator(HibernateAccessor.java:453)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1055)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:1048)
at de.verit.klaros.core.dao.hibernate.KlarosLocalSessionFactoryBean$PropertyDefDao.getAll(KlarosLocalSessionFactoryBean.java:51)
at de.verit.klaros.core.dao.hibernate.KlarosLocalSessionFactoryBean.postProcessMappings(KlarosLocalSessionFactoryBean.java:81)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:672)
at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211)
at de.verit.klaros.core.dao.hibernate.KlarosLocalSessionFactoryBean.afterPropertiesSet(KlarosLocalSessionFactoryBean.java:140)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1369)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
at java.security.AccessController.doPrivileged(Native Method)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:269)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:104)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1245)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1010)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:472)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
at java.security.AccessController.doPrivileged(Native Method)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
at org.jboss.seam.ioc.spring.ContextLoader.startupContextLoader(ContextLoader.java:64)
at org.jboss.seam.ioc.spring.ContextLoader.create(ContextLoader.java:43)
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.jboss.seam.util.Reflections.invoke(Reflections.java:22)
at org.jboss.seam.util.Reflections.invokeAndWrap(Reflections.java:144)
at org.jboss.seam.Component.callComponentMethod(Component.java:2275)
at org.jboss.seam.Component.callCreateMethod(Component.java:2198)
at org.jboss.seam.Component.newInstance(Component.java:2158)
at org.jboss.seam.contexts.Contexts.startup(Contexts.java:304)
at org.jboss.seam.contexts.Contexts.startup(Contexts.java:278)
at org.jboss.seam.contexts.ServletLifecycle.endInitialization(ServletLifecycle.java:143)
at org.jboss.seam.init.Initialization.init(Initialization.java:747)
at org.jboss.seam.servlet.SeamListener.contextInitialized(SeamListener.java:36)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4135)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4630)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:546)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:905)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:740)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:500)
at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
at org.apache.catalina.core.StandardHost.start(StandardHost.java:785)
at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:445)
at org.apache.catalina.core.StandardService.start(StandardService.java:519)
at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
at org.apache.catalina.startup.Catalina.start(Catalina.java:581)
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)

Regards
Bala
Flag Flag
RE: MySQL DB Script
Answer Answer (Unmark)
9/9/10 7:17 AM as a reply to Tobias Schmitt.
Hi Tobias,

It was strange and i did couple of changes as listed below:

  1. Modified my.cnf file from MySQL server installation folder regarding skip-external-locking. Not sure if this was the reason.


Ensured that 127.0.0.1 is placed in hosts file under Windows\system32 folder.Verified from command line if mysql comma was working correctly and it did.Restarted the machine and started the tomcat server.

I instantly found on the console that the exception stack trace from tomcat console did not have what i had listed in my earlier post but had one error listed mentioning that "k_cfg_prop_def" table was not found and then the application started normally.

I have accessed the application and logged in using admin. It is working fine with MySQL DB.

Thanks to you and Westphal for responding instantly to my queries.

Regards
Balaemoticon
Flag Flag
RE: MySQL DB Script
Answer Answer (Unmark)
9/9/10 8:57 AM as a reply to Nandula Bala.
Hi,

I'm glad you found the solution and Klaros-Testmanagement works now. Also thank you for posting your solution to our forum.


Regards,

Tobias Schmitt
Flag Flag