OS X + Alfresco 2.0 + Postgres 8.2.3

[scott_anderson]

I am trying to get Alfresco 2.0 to work with PostgreSQL 8.2.3 on OS X. Extrapolating from the available docs and forum commentary I have gotten to this place with my configuration …

1) Created an 'alfresco' user and 'alfresco' database for PostgreSQL
2) Downloaded and extracted alfresco-community-tomcat-2.0.0.tar.gz
2) Added postgresql-8.0-318.jdbc3.jar to tomcat/common/lib
3) Removed the .sample extension from custom-repository-context.xml.sample & custom-repository.properties.sample in tomcat/shared/classes/alfresco/extension
4) Edited the following files in tomcat/shared/classes/alfresco/extension with the results of the edits being …

*** custom-db-connection.properties ***

db.name=alfresco
db.username=alfresco
db.password= alfresco
db.pool.initial=10
db.pool.max=100
db.driver=org.postgresql.Driver
db.url=jdbcostgresql://localhost:5432/alfresco

*** custom-hibernate-dialect.properties ***

org.hibernate.dialect.PostgreSQLDialect
hibernate.query.substitutions=true TRUE, false FALSE

*** custom-repository.properties ***

db.name=alfresco
db.username=alfresco
db.password= alfresco
db.pool.initial=10
db.pool.max=100
db.schema.update=true
db.driver=org.postgresql.Driver
db.url=jdbcostgresql://localhost:5432/alfresco

And the results I am seeing in the alfresco log are …

22:35:56,325 ERROR [org.alfresco.repo.content.transform.magick.AbstractImageMagickContentTransformer] ImageMagickContentTransformer not available: Failed to execute command: convert /opt/alfresco/tomcat/temp/Alfresco/ImageMagickContentTransformer_init_source_33819.gif  /opt/alfresco/tomcat/temp/Alfresco/ImageMagickContentTransformer_init_target_33820.png
22:35:57,735 WARN  [org.springframework.remoting.rmi.RmiRegistryFactoryBean] Could not detect RMI registry - creating new one
22:35:59,270 WARN  [org.alfresco.util.OpenOfficeConnectionTester] A connection to OpenOffice could not be established.
22:36:00,090 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Generating unmodified schema creation script: /opt/alfresco/tomcat/temp/Alfresco/AlfrescoSchemaCreate-org.hibernate.dialect.MySQLInnoDBDialect-33821.sql
22:36:00,204 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script: /opt/alfresco/tomcat/temp/Alfresco/AlfrescoSchemaCreate-org.hibernate.dialect.MySQLInnoDBDialect-33821.sql
22:36:00,219 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:  create table JBPM_ACTION ( ID_ bigint not null auto_increment, class char(1) not null, NAME_ varchar(255), ISPROPAGATIONALLOWED_ bit, ACTIONEXPRESSION_ varchar(255), ISASYNC_ bit, REFERENCEDACTION_ bigint, ACTIONDELEGATION_ bigint, EVENT_ bigint, PROCESSDEFINITION_ bigint, TIMERNAME_ varchar(255), DUEDATE_ varchar(255), REPEAT_ varchar(255), TRANSITIONNAME_ varchar(255), TIMERACTION_ bigint, EXPRESSION_ text, EVENTINDEX_ integer, EXCEPTIONHANDLER_ bigint, EXCEPTIONHANDLERINDEX_ integer, primary key (ID_) ) type=InnoDB
   Error: ERROR: syntax error at or near "auto_increment"
   File: /opt/alfresco/tomcat/temp/Alfresco/AlfrescoSchemaCreate-org.hibernate.dialect.MySQLInnoDBDialect-33821.sql
   Line: 23
22:36:00,222 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:659)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3763)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4211)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:809)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:698)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1122)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1021)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:709)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:551)
   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:585)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:294)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:432)
Caused by: java.sql.SQLException: ERROR: syntax error at or near "auto_increment"
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1501)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1283)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:186)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:314)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:306)
   at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:261)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeStatement(SchemaBootstrap.java:597)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.executeScriptFile(SchemaBootstrap.java:572)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:356)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:643)
   … 32 more

My assumption is that this MySQL initialization script shouldn't be getting called and perhaps a PostgreSQL version or generic version should be called in its place but I can't figure out why.

[derek]

Hi,

Get rid of custom-db-connection.properties.
Make sure you also put a value for dir.root into custom-repository.properties.

But the real problem might be that the dialect is not a property value, but just a dangling string in the file.  It needs to be hibernate.dialect=….

Regards

[scott_anderson]

> It needs to be hibernate.dialect=….

Duh…a result of my boss feeding me beers late at night to keep me working. Your instructions solved my problem of getting the server started w/o any errors. I did encounter a little bit of trouble getting the server started a second time. If I comment out the db.schema.update property in the custom-repository.properties file (the state I found it before I set it to true), I am seeing some errors related to the app re-attempting to 'auto-update' the schema …

02:59:54,277 ERROR [web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Schema auto-update failed
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:659)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:45)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:225)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:323)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3763)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4211)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:759)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:739)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:524)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:809)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:698)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:472)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1122)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:310)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1021)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:718)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1013)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:442)
   at org.apache.catalina.core.StandardService.start(StandardService.java:450)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:709)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:551)
   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:585)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:294)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:432)
Caused by: org.hibernate.exception.SQLGrammarException: could not get table metadata: JBPM_ACTION
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
   at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:100)
   at org.hibernate.cfg.Configuration.generateSchemaUpdateScript(Configuration.java:838)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:376)
   at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:643)
   … 32 more
Caused by: java.sql.SQLException: ERROR: function information_schema._pg_keypositions() does not exist
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1501)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1283)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:186)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:314)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:221)
   at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getImportedExportedKeys(AbstractJdbc2DatabaseMetaData.java:3240)
   at org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getImportedKeys(AbstractJdbc2DatabaseMetaData.java:3536)
   at org.hibernate.tool.hbm2ddl.TableMetadata.initForeignKeys(TableMetadata.java:119)
   at org.hibernate.tool.hbm2ddl.TableMetadata.<init>(TableMetadata.java:35)
   at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:85)
   … 35 more

This appears to be the same result I get when I try to access the foreign keys of the JBPM_ACTION table using NetBeans' db access tools. Is this something I should concern myself with? If I set the db.schema.update to false after the initial startup and shutdown, this auto-update process appears to be skipped on subsequent starts and I believe I am good to go. However, on the initial startup I am seeing this line in the catalina.out log which looks a little suspicious …

03:31:30,867 INFO  [domain.schema.SchemaBootstrap] Executing database script: classpath:alfresco/dbscripts/create/2.0/${db.script.dialect}/AlfrescoPostCreate-2.0-FKIndexes.sql

I can't find anywhere where db.script.dialect would get set.

Thanks again,
Scott

[derek]

Hi,

You should only have to set the dialect and the database connection details.  Messing about with the auto-update is not really recommended before searching for a solution to the issue.  Type this (information_schema._pg_keypositions) into a JIRA search.

Give the workaround a try and let me know if it fixed your issue.  It worked for me, but there might be other issues as a result.

Regards

[scott_anderson]

That sql patch seems to be working for me. I can leave the db.schema.update untouched for my installation. Thanks.