Table alternativepersistentidentifier is missing

[4tikhonov]

At the moment it's not possible to migrate from Dataverse 4.9.2 to 4.9.4 as table alternativepersistentidentifier is missing:

"Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "alternativepersistentidentifier" does not exist
Position: 131
Error Code: 0
Call: SELECT ID, AUTHORITY, GLOBALIDCREATETIME, IDENTIFIER, IDENTIFIERREGISTERED, PROTOCOL, STORAGELOCATIONDESIGNATOR, DVOBJECT_ID FROM ALTERNATIVEPERSISTENTIDENTIFIER WHERE (DVOBJECT_ID = ?)
bind => [1 parameter bound]
Query: ReadAllQuery(name="alternativePersistentIndentifiers" referenceClass=AlternativePersistentIdentifier sql="SELECT ID, AUTHORITY, GLOBALIDCREATETIME, IDENTIFIER, IDENTIFIERREGISTERED, PROTOCOL, STORAGELOCATIONDESIGNATOR, DVOBJECT_ID FROM ALTERNATIVEPERSISTENTIDENTIFIER WHERE (DVOBJECT_ID = ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)"

[pdurbin]

@4tikhonov hi! Deploying Dataverse 4.9.3 (the version the table/entity was added in via pull request #5064) or newer should create the table. See also b802c85 where I wrote "If you are creating a new database table (which maps to an @Entity in JPA), you do not need to create or update a SQL upgrade script."

[4tikhonov]

Ok, but that's exactly the problem: we've migrated from 4.9.2 to 4.9.4 on Google Cloud and faced this error message. I think SQL patch should be published together with release.

[pdurbin]

@4tikhonov can you please post the full output of asadmin deploy?

[4tikhonov]

2018-10-17T12:31:16.140+0000] [glassfish 4.1] [SEVERE] [] [javax.enterprise.resource.webcontainer.jsf.application] [tid: _ThreadID=26 _ThreadName=http-listener-1(1)] [timeMillis: 1539779476140] [levelValue: 1000] [[
Error Rendering View[/dataset.xhtml]
javax.el.ELException: /dataset.xhtml @336,142 rendered="#{!empty DatasetPage.thumbnailString}": Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "alternativepersistentidentifier" does not exist
Position: 131
Error Code: 0

[pdurbin]

@4tikhonov hi! That's the stace trace. I'm talking about output that looks like this:

PER01000: Got SQLException executing statement "CREATE TABLE ALTERNATIVEPERSISTENTIDENTIFIER (ID SERIAL NOT NULL, AUTHORITY VARCHAR(255), GLOBALIDCREATETIME TIMESTAMP, IDENTIFIER VARCHAR(255), IDENTIFIERREGISTERED BOOLEAN, PROTOCOL VARCHAR(255), STORAGELOCATIONDESIGNATOR BOOLEAN, DVOBJECT_ID BIGINT NOT NULL, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: relation "alternativepersistentidentifier" already exists

I'll attach some complete output because it's too long to paste here.

deploy.txt

The output always ends with "Command deploy completed with warnings" because Glassfish tries to create all the tables (such as alternativepersistentidentifier in the output above) and most of them already exist.

Am I making sense? Can you please provide the output of adsamin deploy?

Here's the one-liner I often use to deploy the latest code on a server:

git pull && mvn clean && scripts/installer/custom-build-number && mvn package && /usr/local/glassfish4/glassfish/bin/asadmin deploy --force target/dataverse-4.9.4.war

[4tikhonov]

I don't have any problem with deployment, it doesn't show any warnings. Dataverse on website also looks okay but if someone creates new dataset it's complaining about missing table.

[pdurbin]

@4tikhonov are you saying there is no output at all from asadmin deploy? I've never seen this. There are always warnings about tables that always exist when I perform an upgrade. Can you please try to redeploy the war file (you'll need to add --force as above when the war is already deployed) and capture the output?

[4tikhonov]

@pdurbin, no, I just said there are no additional warning, only about tables that already exist.
I'm pretty convinced that the patch should contain just definition of new table alternativepersistentidentifier.

[pdurbin]

@4tikhonov can you please email [email protected] about this to create a support ticket? We aren't making any progress here, unfortunately. This problem needs some fresh eyes. Sorry I couldn't help. 😞

[pdurbin]

@4tikhonov thanks for opening https://help.hmdc.harvard.edu/Ticket/Display.html?id=268390

While you wait for a reply and while upgrading is on your mind, please take a look at #4980 and leave a comment with any feedback if you have time. Thanks!

[qqmyers]

FWIW: I see a call to create the alternativepersistentidentifier table when deploying 4.9.4 -it shows the error below. (On this machine, we did run 4.9.3 for a while, but I can delete the table and see that reinstalling 4.9.4 recreates it.)

[#|2018-10-20T20:02:48.767+0000|WARNING|glassfish 4.1|javax.org.glassfish.persistence.org.glassfish.persistence.common|_ThreadID=52;_ThreadName=admin-listener(2);_TimeMillis=1540065768767;_LevelValue=900;|PER01000: Got SQLException executing statement "CREATE TABLE ALTERNATIVEPERSISTENTIDENTIFIER (ID SERIAL NOT NULL, AUTHORITY VARCHAR(255), GLOBALIDCREATETIME TIMESTAMP, IDENTIFIER VARCHAR(255), IDENTIFIERREGISTERED BOOLEAN, PROTOCOL VARCHAR(255), STORAGELOCATIONDESIGNATOR BOOLEAN, DVOBJECT_ID BIGINT NOT NULL, PRIMARY KEY (ID))": org.postgresql.util.PSQLException: ERROR: relation "alternativepersistentidentifier" already exists|#]
[#|2018-10-20T20:02:48.821+0000|WARNING|glassfish 4.1|javax.org.glassfish.persistence.org.glassfish.persistence.common|_ThreadID=52;_ThreadName=admin-listener(2);_TimeMillis=1540065768821;_LevelValue=900;|PER01000: Got SQLException executing statement "ALTER TABLE ALTERNATIVEPERSISTENTIDENTIFIER ADD CONSTRAINT FK_ALTERNATIVEPERSISTENTIDENTIFIER_DVOBJECT_ID FOREIGN KEY (DVOBJECT_ID) REFERENCES DVOBJECT (ID)": org.postgresql.util.PSQLException: ERROR: constraint "fk_alternativepersistentidentifier_dvobject_id" for relation "alternativepersistentidentifier" already exists|#]

[4tikhonov]

We fixed that issue by upgrading Dataverse database manually on local server. After we copied new database to Google Cloud.