Upgrade Alfresco 2.1 to Alfresco Labs 3a with Postgresql

[fpozo]

Hello guys,

I am trying to upgrade Alfresco 2.1 to Alfresco Labs 3a using Postgresql 8.2. However, I found problems I need someone who can answers these questions:

1) I noticed that do not  exist upgrade scripts to 2.2 Alfresco Schema, ¿Why?

2) Nevertheless, I have translated the scripts from Oracle to Postgrsql, but after the shcema upgrade a get the next message:

08:51:07,561 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.PostgreSQLDialect.
08:51:09,803 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] No changes were made to the schema.
08:52:01,467 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Bootstrap failed
   at org.alfresco.repo.importer.ImporterBootstrap.bootstrap(ImporterBootstrap.java:328)
   at org.alfresco.repo.importer.ImporterBootstrap.onBootstrap(ImporterBootstrap.java:691)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:77)
   at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:75)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:241)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:349)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:156)
   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:3764)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4216)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:831)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:720)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
   at org.apache.catalina.core.StandardService.start(StandardService.java:448)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
   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:295)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
Caused by: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
   at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:636)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:699)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:570)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:654)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:624)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:307)
   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:431)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:256)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:172)
   at org.alfresco.repo.importer.ImporterBootstrap.bootstrap(ImporterBootstrap.java:324)
   … 35 more
Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:315)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:175)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:562)
   … 42 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into alf_access_control_list (version, acl_id, latest, acl_version, inherits, inherits_from, type, inherited_acl, is_versioned, requires_version, acl_change_set, id) values (0, 46a7b5a4-14cd-42ee-bd6c-4e2a4cd117f9, 1, 1, 1, NULL, 1, NULL, 0, 0, 83113, 83114) was aborted.  Call getNextException to see the cause.
   at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2534)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:352)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2669)
   at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
   … 50 more
08:52:01,479 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/alfresco]] Excepción enviando evento inicializado de contexto a instancia de escuchador de clase org.springframework.web.context.ContextLoaderListener
org.alfresco.error.AlfrescoRuntimeException: Bootstrap failed
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Maybe some bugs into the alfresco code executed after shema upgraded, for example, a bad use of Postgres JDBC?

Please, I need your help!! .



THANKYOU VERY MUCH AND HAVE A NICE DAY.

[matafagafo]

Hi, we have the same situation here, did you solved the problems in the upgrade ?
If so, can you send your scripts ?

[stegbth]

hi,

is this problem still unsolved? also with Alfresco-Labs-3c-Linux-x86-Install

i am currently testing alfresco and prefer postgres over mysql, but if there are more trouble's. i am thinking about using mysql instead.

greetings
thomas

[nuttinjeff]

hi,

is this problem still unsolved? also with Alfresco-Labs-3c-Linux-x86-Install

i am currently testing alfresco and prefer postgres over mysql, but if there are more trouble's. i am thinking about using mysql instead.

greetings
thomas

Hi, unfortunally the script isnt present in Labs-3c release either.

Dont know why they didnt added it, but it is missing a lot.

No one have knowledge to make the script?

[iflores]

Moving 2.1 to 3c also fails, but error message is like:

21:01:26,694  INFO  [domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.PostgreSQLDialect.
21:01:26,872  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
org.alfresco.error.AlfrescoRuntimeException: The schema script could not be found at location classpath:alfresco/dbscripts/upgrade/2.2/${db.script.dialect}/AlfrescoSchemaUpdate-2.2-ACL.sql
‍‍‍‍‍

which is weird cause its 2.1, no 2.2, the version it should upgrade from, and in the 2.1 dir (alfresco/dbscripts/upgrade/2.1 there is a org.hibernate.dialect.PostgreSQLDialect dir .

And… running those scripts manually also doesnt work.

So, right now, apart from starting from scratch with the users and roles, and get the content from a tar of the repository taken via SMB or FTP or something, no idea how to migrate.

[nuttinjeff]

Moving 2.1 to 3c also fails, but error message is like:

21:01:26,694  INFO  [domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.PostgreSQLDialect.
21:01:26,872  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
org.alfresco.error.AlfrescoRuntimeException: The schema script could not be found at location classpath:alfresco/dbscripts/upgrade/2.2/${db.script.dialect}/AlfrescoSchemaUpdate-2.2-ACL.sql
‍‍‍‍‍

which is weird cause its 2.1, no 2.2, the version it should upgrade from, and in the 2.1 dir (alfresco/dbscripts/upgrade/2.1 there is a org.hibernate.dialect.PostgreSQLDialect dir .

And… running those scripts manually also doesnt work.

So, right now, apart from starting from scratch with the users and roles, and get the content from a tar of the repository taken via SMB or FTP or something, no idea how to migrate.

Yes, I have the same error. I Think its cause the scripts are not cumulative. The 2.1 script only updates it to 2.2 and then you need the 2.2 script to update to 3.0…. Other way you would need various scripts like "2.1_to_2.2", "2.1_to_3.0" and so on.

[bpsommerville]

Hi,

We ran into this same problem upgrading 2.2 to 3.0b on Postgres.  I managed to alter the oracle scripts & patch the codebase to make it work.
You can get my scripts from http://www.eintel.com.au/3b-postgres-upgrade.zip.

You need to place the sql files in
server/webapps/alfresco/WEB-INF/classes/alfresco/dbscripts/upgrade/2.2/org.hibernate.dialect.PostgreSQLDialect/
and the jar file in
server/webapps/alfresco/WEB-INF/lib

The jar is necessary because the standard Alfresco distribution had an issue with nested transaction running the updates on Postgres.

IMPORTANT: These scripts have only been tested on 2 installations.  Use at your own risk.  Make a backup before attempting anything.  No liability accepted for any issues, etc ,etc.

good luck
Ben

[rvntone]

Hi, do you still have the file? 3b-postgres-upgrade.zip

Could you publish it again?

I really need a solution for a old alfresco server (2.1)

[stegbth]

Hello everybody,

alfresco seems to handle other databases as mysql with a lower priority

as looking to known issues in the release-notes of alfresco-labs 3 stable.
http://wiki.alfresco.com/wiki/Alfresco_Labs_3_Stable_Release_Notes

i am lighly disappointed

greetings
thomas

[m_r_]

You are aware that PostgreSQL is not supported in 3 until Patch 1?

http://www.alfresco.com/services/support/stacks/3.0/