SQL error with alfresco 1.4 on ingres database

[advaittrivedi]

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 ( id bigint not null, parent_node_id bigint with null, child_node_id bigint with null, type_qname varchar(255) not null, qname varchar(255) not null, child_node_name varchar(50) not null, child_node_name_crc bigint not null, is_primary tinyint with null, assoc_index integer with null, primary key (id), unique (parent_node_id, type_qname, child_node_name, child_node_name_crc) )
   Error: CREATE TABLE: A column in a UNIQUE constraint has been
defined as WITH NULL (on table 'alf_child_assoc').
All columns in a UNIQUE constraint MUST be created as NOT NULL.
—————————————————————————————
It is farily simple from above to find out that in create table script for alf_child_assoc has column in a UNIQUE constraint which is defined as WITH NULL. How can I change this? Alfresco creates a new sql file in temp directory of tomcat each time, with new name. So editing file will not solve problem.

———-
Thanks & Regards,
Advait

[derek]

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

[advaittrivedi]

thanks derek, will try that

[advaittrivedi]

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 ?

[derek]

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.

[advaittrivedi]

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?

[derek]

more than one root node in index: 2

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

[advaittrivedi]

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?

[derek]

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

[advaittrivedi]

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.