topic SQL error with alfresco 1.4 on ingres database in Alfresco Archive

SQL error with alfresco 1.4 on ingres database

advaittrivedi — Tue, 24 Jul 2007 11:33:38 GMT

Hi, I am trying to run alfresco 1.4 with ingres 2006 database on tomcat. I have setup driver and all other connection properties. I am getting this error:—————————————————————————————16:51:20,779 ERROR [domain.schema.SchemaBootstrap] Statement execution failed: SQL: create table alf_child_assoc (

Re: SQL error with alfresco 1.4 on ingres database

derek — Wed, 25 Jul 2007 01:11:08 GMT

Hi,

Take a copy of the temp file generated, clean your database out, fix the script and run it manually. Then you can start the server against that.

Regards

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Wed, 25 Jul 2007 10:22:59 GMT

thanks derek, will try that

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Wed, 25 Jul 2007 10:47:23 GMT

Hi Derek, I tried your suggestion but unfortunately now the list of exceptions has changed, about this one I am totally at loss :?: :
—————–
15:38:33,998 WARN [org.springframework.jdbc.support.SQLErrorCodesFactory] Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Could not access DatabaseMetaData method 'getDatabaseProductName'; nested exception is java.lang.IllegalAccessException: Class org.springframework.jdbc.support.JdbcUtils$1 can not access a member of class com.ingres.gcf.jdbc.JdbcDBMD with modifiers "public"
java.lang.IllegalAccessException: Class org.springframework.jdbc.support.JdbcUtils$1 can not access a member of class com.ingres.gcf.jdbc.JdbcDBMD with modifiers "public"
   at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)
   at java.lang.reflect.Method.invoke(Method.java:578)
   at org.springframework.jdbc.support.JdbcUtils$1.processMetaData(JdbcUtils.java:209)
   at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:174)
   at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:204)
   at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:185)
   at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:126)
…………..
Caused by: org.alfresco.repo.search.SearcherException: More than one root node in index: 2
—————–

Any idea what's going wrong ?

Re: SQL error with alfresco 1.4 on ingres database

derek — Wed, 25 Jul 2007 11:01:54 GMT

When I said clean your database out, I should have added remove the alf_data directory as well. Start again, but with a clean system.

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Wed, 25 Jul 2007 12:12:37 GMT

I deleted alf_data directory, also i dropped the database and created a new one. than i ran the script. but still identical exceptions are coming!! any idea?

Re: SQL error with alfresco 1.4 on ingres database

derek — Wed, 25 Jul 2007 12:44:42 GMT

more than one root node in index: 2

This indicates that the Lucene indexes have been left lying around from a previous attempted bootstrap.

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Thu, 26 Jul 2007 12:57:42 GMT

i got rid of that error. but now i am getting following exceptions:

17:12:36,831 WARN [org.springframework.jdbc.support.SQLErrorCodesFactory] Error while extracting database product name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Could not access DatabaseMetaData method 'getDatabaseProductName'; nested exception is java.lang.IllegalAccessException: Class org.springframework.jdbc.support.JdbcUtils$1 can not access a member of class com.ingres.gcf.jdbc.JdbcDBMD with modifiers "public"
java.lang.IllegalAccessException: Class org.springframework.jdbc.support.JdbcUtils$1 can not access a member of class com.ingres.gcf.jdbc.JdbcDBMD with modifiers "public"
   at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)
   at java.lang.reflect.Method.invoke(Method.java:578)
   at org.springframework.jdbc.support.JdbcUtils$1.processMetaData(JdbcUtils.java:209)
…………….
Caused by: org.alfresco.service.cmr.view.ImporterException: Failed to import package at line 75; column 25 due to error: Hibernate operation: could not execute update query; uncategorized SQLException for SQL [update alf_child_assoc set child_node_name=?, child_node_name_crc=? where id=?]; SQL state [40001]; error code [4708]; The query has been aborted.; nested exception is com.ingres.gcf.util.SqlEx: The query has been aborted.
   at org.alfresco.repo.importer.view.ViewParser.parse(ViewParser.java:171)
   at org.alfresco.repo.importer.ImporterComponent.parserImport(ImporterComponent.java:344)
   at org.alfresco.repo.importer.ImporterComponent.importView(ImporterComponent.java:211)
   at org.alfresco.repo.importer.ImporterBootstrap.bootstrap(ImporterBootstrap.java:429)
————————————————————————–
As you can see, it seems to be caused by a failed update query. SQL state and error code are written out.

Any idea?

Re: SQL error with alfresco 1.4 on ingres database

derek — Thu, 26 Jul 2007 13:26:35 GMT

Hi,

The first WARN message can be dealt with by specifying an exception translator for your database: http://forum.springframework.org/showthread.php?p=36804.

Then you'll have to translate the error codes for Ingres into the common ones handled by Spring, Hibernate and, by extension, Alfresco. If you don't, you (a) won't know what caused the error and (b) won't have certain errors handled as well as they could be.

You'll see that it's a straightforward update statement. I'm no Ingres expert - perhaps someone else can help with translating the error message into something concrete to work with. Hunting around the Hibernate-related sites might help. Once you have a working translator, you can send it to us for inclusion in the distribution.

Regards

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Fri, 27 Jul 2007 10:06:43 GMT

Derek, thanks for your help. Are you trying to say that to solve this problem I will have to write exception translator first? Without which there is no other way to find out what went wrong?

Also, I tried the same setup with alfresco-community-tomcat-2.1.0R1, but this time its giving following exception about invalid username/password during tomcat startup:
———————————————————–
15:02:01,896 ERROR [org.alfresco.repo.transaction.TransactionUtil] Error rolling back transaction
java.lang.IllegalStateException: No user transaction is active
   at org.alfresco.util.transaction.SpringAwareUserTransaction.rollback(SpringAwareUserTransaction.java:479)
   at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:214)
……………..
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failure: invalid username/password.)
   at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
   at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
   at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
   at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)
   … 54 more
Caused by: com.ingres.gcf.util.SqlEx: Login failure: invalid username/password.
———————————————————–

Normally, when connecting to ingres we dont need to specify username/password in custom-repository.properties, ingres driver picks up OS's current username/password and uses it connect to database. This approach worked with Alfresco 1.4.

Re: SQL error with alfresco 1.4 on ingres database

derek — Fri, 27 Jul 2007 10:30:47 GMT

Hi,

What I'm saying is that SQL state [40001]; error code [4708] means nothing to the system.

You database connection is established by the DBCP connection pool now. Check the settings and possibly override the datasource bean.

I'm sorry, but we have put no effort into getting Ingres to work and we're not likely to do so. Hopefully a fellow Ingres user will be able to help. Also, DBCP is a widely used component and there's nothing special about what the way Alfresco uses it. You might find the solution in other products' forums as well.

Regards

Re: SQL error with alfresco 1.4 on ingres database

advaittrivedi — Fri, 27 Jul 2007 11:18:24 GMT

Hi Derek,

to get the description for SQL state [40001] i went through Ingres product documentation and found the description, it is: "Serialization failure". And as far as [warn] message is concerned i am OK with it, since it is just a warning.

Further, I am trying to print values of query parameters in log, by adding following:

log4j.logger.org.hibernate.type=debug to log4j.properties of alfresco.

But it is not printing values of parameters. Hibernate documentation also suggest the same, any idea on how to print query parameter values will be helpful.