3.4a upgrade problem: relation 'avm_stores_seq' already exis

[jturmelle]

Sorry for the repeat-post, please see http://forums.alfresco.com/en/viewtopic.php?f=8&t=35327#p102607
The search functionality of the forums didn't show me the related message.

I just tried to upgrade my community edition from 3.3.0 to 3.4a and had the following error:
org.postgresql.util.PSQLException: ERROR: relation "avm_stores_seq" already exists

Can anyone tell me how to work around this?  I looked at the database, and indeed that table already exists, and is in use. In fact, the SQL that is trying to update the avm_sequences has multiple problems.  All the sequences it is trying to create already exist.  Any idea?

/local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-7149948438016598234.sql:

CREATE SEQUENCE avm_stores_seq START WITH ${hibernate_seq_next_value} INCREMENT BY 1;

CREATE SEQUENCE avm_store_properties_seq START WITH ${hibernate_seq_next_value} INCREMENT BY 1;

CREATE SEQUENCE avm_nodes_seq START WITH ${hibernate_seq_next_value} INCREMENT BY 1;

CREATE SEQUENCE avm_version_roots_seq START WITH ${hibernate_seq_next_value} INCREMENT BY 1;
‍‍‍‍‍‍‍‍‍

From alfresco.log

18:43:00,581 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.PostgreSQLDialect.
18:43:14,157 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-4810176798913650692
.sql (Copied from classpath:alfresco/dbscripts/upgrade/3.3/org.hibernate.dialect.PostgreSQLDialect/modify-index-permission_id.sql).
18:43:16,472 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-1604796588253281744
.sql (Copied from classpath:alfresco/dbscripts/upgrade/2.2/org.hibernate.dialect.PostgreSQLDialect/AddFKIndexes.sql).
18:43:25,064 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-9068363595966233054
.sql (Copied from classpath:alfresco/dbscripts/upgrade/3.3/org.hibernate.dialect.PostgreSQLDialect/fix-Repo-seqs.sql).
18:43:25,951 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-7149948438016598234
.sql (Copied from classpath:alfresco/dbscripts/upgrade/3.3/org.hibernate.dialect.PostgreSQLDialect/fix-AVM-seqs.sql).
18:43:25,953 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: CREATE SEQUENCE avm_stores_seq START WITH 83627 INCREMENT BY 1
   Error: ERROR: relation "avm_stores_seq" already exists
   File: /local/alfresco-3.4a/tomcat/temp/Alfresco/AlfrescoSchema-PostgreSQLDialect-Update-7149948438016598234.sql
   Line: 15
18:43:25,955 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed
org.postgresql.util.PSQLException: ERROR: relation "avm_stores_seq" already exists
…
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks for any help.

[derek]

Hi,
It appears that the patch is running when it should not.
Please post the bit of the startup log giving the Alfresco version from BEFORE the upgrade, so that we can fix this.  Better still, raise a JIRA ticket with those details and the details you gave here.  If all the sequences are already present, then you can bypass the patch by marking it as executed.
Locate the file that was being executed
Copy the bit at the bottom where the row is inserted into the alf_applied_patch table.
Go back to pre-upgrade data.
Run the SQL you copied i.e. mark the script as completed already.
Upgrade again and the script will think that it should not run (which it appears it should not).

Please report the issue in JIRA with appropriate details so that we can sort it out.

[derek]

Actually, I've already raised it: http://issues.alfresco.com/jira/browse/ALF-5344

[derek]

Please post the version details of the server from before the upgrade.  Those sequences were only introduced in 3.4 (before that the inserts used the 'hibernate_sequence') so we'll need a bit more history of the upgrades.

[jturmelle]

Is this the string you're looking for:

16:44:38,238  INFO  [service.descriptor.DescriptorService] Alfresco started (Community): Current version 3.3.0 (g 2860) schema 4100 - Originally installed version 3.2.0 (r2 2440) schema 3300
‍‍