Need help in database migration from 5x to 6.0

euler

unread,

Nov 9, 2016, 7:47:27 AM11/9/16

to DSpace Technical Support

Dear All,

I'm on the process of upgrading a dspace instance from 5.4 to 6.0. All went well but I noticed in my log file this error:

2016-11-09 20:36:42,308 FATAL org.dspace.core.Context @ Cannot obtain the bean which provides a database connection. Check previous entries in the dspace.log to find why the db failed to initialize.

and the page is just blank.

When I run dspace database info, here's the result:

Database Software: PostgreSQL version 9.5.1

Database Driver: PostgreSQL Native Driver version PostgreSQL 9.4.1211.jre7

PostgreSQL 'pgcrypto' extension installed/up-to-date? true (version=1.2)

+----------------+--------------------------------------------------+---------------------+---------+

+----------------+--------------------------------------------------+---------------------+---------+

| 1.6 | Initializing from DSpace 1.6 database schema | 2015-09-26 17:28:42 | Success |

| 1.7 | Upgrade to DSpace 1.7 schema | 2015-09-26 17:28:43 | Success |

| 1.8 | Upgrade to DSpace 1.8 schema | 2015-09-26 17:28:46 | Success |

| 3.0 | Upgrade to DSpace 3.x schema | 2015-09-26 17:28:52 | Success |

| 4.0 | Upgrade to DSpace 4.x schema | 2015-09-26 17:28:53 | Success |

| 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy | 2015-09-26 17:28:53 | Success |

| 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop constraint | 2015-09-26 17:28:54 | Success |

| 5.0.2014.09.26 | DS-1582 Metadata For All Objects | 2015-09-26 17:30:31 | Success |

| 5.6.2016.08.23 | DS-3097 | 2016-11-09 17:59:33 | Success |

| 6.0.2015.03.06 | DS 2701 Dso Uuid Migration | 2016-11-09 17:59:34 | Success |

| 6.0.2016.01.03 | DS-3024 | | Pending |

| 6.0.2016.07.21 | DS-2775 | | Pending |

| 6.0.2016.08.23 | DS-3097 | | Pending |

+----------------+--------------------------------------------------+---------------------+---------+

So I tried to run dspace database migrate and I received this error:

Migrating database to latest version... (Check dspace logs for details)

Migration exception:

java.sql.SQLException: Flyway migration error occurred

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:666)

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:574)

at org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:222)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:229)

at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:81)

Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:

Migration V6.0_2015.03.07__DS-2701_Hibernate_migration.sql failed

-----------------------------------------------------------------

SQL State : 23505

Error Code : 0

Message : ERROR: could not create unique index "epersongroup2eperson_pkey"

Detail: Key (eperson_group_id, eperson_id)=(550bb4c5-7f50-4db7-89ad-e7d6b11ab3c1, 505e83c3-1a4f-446e-9934-f5fcba38a76c) is duplicated.

Location : org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql ([dspace]\lib\dspace-api-6.0.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V6.0_2015.03.07__DS-2701_Hibernate_migration.sql

)

Line : 114

Statement : ALTER TABLE EPersonGroup2EPerson add primary key (eperson_group_id,eperson_id)

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)

at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)

at org.flywaydb.core.internal.command.DbMigrate.doMigrate(DbMigrate.java:352)

at org.flywaydb.core.internal.command.DbMigrate.access$1100(DbMigrate.java:47)

at org.flywaydb.core.internal.command.DbMigrate$4.doInTransaction(DbMigrate.java:308)

at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)

at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:305)

at org.flywaydb.core.internal.command.DbMigrate.access$1000(DbMigrate.java:47)

at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:230)

at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:173)

at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)

at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)

at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)

at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)

at org.flywaydb.core.Flyway.execute(Flyway.java:1373)

at org.flywaydb.core.Flyway.migrate(Flyway.java:917)

at org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:655)

... 8 more

Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "epersongroup2eperson_pkey"

Detail: Key (eperson_group_id, eperson_id)=(550bb4c5-7f50-4db7-89ad-e7d6b11ab3c1, 505e83c3-1a4f-446e-9934-f5fcba38a76c) is duplicated.

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)

at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:265)

at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)

at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)

at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)

... 24 more

Please help on how to resolve this error.

Thanks in advance.

euler

Bruno Nocera Zanette

unread,

Nov 9, 2016, 9:08:58 AM11/9/16

to euler, DSpace Technical Support

Euler,
How are you trying to do this? Which commands?

For me, every time i've tried to update a version with "ant update" worked flawlessly. You seem to have compile, installed the files and then using "./bin/dspace database migrate" to do the migration. I've never tried to do it, so i don't know if there's any problem in it.

--
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To post to this group, send email to dspac...@googlegroups.com.
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.

--

Bruno Nocera Zanette
+55 41 9992-2508

euler

unread,

Nov 9, 2016, 9:18:24 AM11/9/16

to DSpace Technical Support, esn...@seafdec.org.ph

Hi Bruno,

Running "ant update" worked flawlessly for me too. It's just that automatic migration did not worked for me as shown when I run "dspace database info". Also, this did not happen in another dspace instance that I'm maintaining. The error that caught my attention is "ERROR: could not create unique index "epersongroup2eperson_pkey"

So I'm hoping if someone can help me on how to resolve this problem.

Thanks

Terry Brady

unread,

Nov 9, 2016, 11:43:20 AM11/9/16

to euler, DSpace Technical Support

This issue should be logged as a bug in DSpace Jira since others may encounter the issue in the future.

As a work around, you could run the following SQL to identify the records in error.

select eperson_group_id,eperson_id, count(*)

from epersongroup2eperson group by eperson_group_id, eperson_id

having count(*)>1;

If the number of records in error is small, you could clean them up in the database.

To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech+unsubscribe@googlegroups.com.

To post to this group, send email to dspac...@googlegroups.com.
Visit this group at https://groups.google.com/group/dspace-tech.
For more options, visit https://groups.google.com/d/optout.

--

Terry Brady

Applications Programmer Analyst

Georgetown University Library Information Technology

http://georgetown-university-libraries.github.io/

425-298-5498 (Seattle, WA)

euler

unread,

Nov 9, 2016, 6:14:20 PM11/9/16

to DSpace Technical Support, esn...@seafdec.org.ph, Terry Brady

Hi Terry,

Running the SQL returned me with just 3 records.

eperson_group_id | eperson_id | count

------------------+------------+-------

1 | 38 | 2

1 | 42 | 2

1 | 31 | 2

(3 rows)

Please advice what should I do with these 3 records when you said that if the number of records in error is small to just clean them up in the database. Can you also explain to me what is wrong with these 3 records?

Thank you so much in advance.

Best regards,

euler

helix84

unread,

Nov 9, 2016, 6:48:23 PM11/9/16

to euler, DSpace Technical Support, Terry Brady

Just delete one of each 3 duplicates manually.

This happens to me regularly on a 5.x instance (I'm using LDAP auth)
and I couldn't figure out why. I started checking for this condition
each night, because a large number (low thousands) of such duplicate
entries appears overnight once every couple of days. The number then
seems to grow exponentially until it completely overwhelms DSpace
which then stops responding.

As a workaround, I'm running this script nightly from cron:

cleanup_eperson.sh:

#!/bin/sh

total=`psql -A -t -c "SELECT count(1) FROM epersongroup2eperson;"`
distinct=`psql -A -t -c "SELECT count(DISTINCT (eperson_group_id,
eperson_id)) FROM epersongroup2eperson;"`

# for testing only:
#total=$(($total+1))

echo $total
echo $distinct

if [ "$total" -gt "$distinct" ] ; then

echo "Cleaning up..." | mail -s "digilib eperson table cleanup
($total/$distinct)" dspac...@example.com

report=`psql 2>&1 <<THE_END
SELECT MIN(ctid) as ctid, eperson_group_id, eperson_id
FROM epersongroup2eperson
GROUP BY eperson_group_id, eperson_id HAVING
COUNT(*) > 1;
THE_END`

# perform the cleanup
# capture the output and running time
cleanuptime=`time psql 2>&1 <<THE_END
DELETE FROM epersongroup2eperson a USING (
SELECT MIN(ctid) as ctid, eperson_group_id, eperson_id
FROM epersongroup2eperson
GROUP BY eperson_group_id, eperson_id HAVING
COUNT(*) > 1
) b
WHERE a.eperson_group_id = b.eperson_group_id
AND a.eperson_id = b.eperson_id
AND a.ctid <> b.ctid;
THE_END`

# check again and report the status by email
out=`psql <<THE_END
SELECT count(1) FROM epersongroup2eperson;
SELECT count(DISTINCT (eperson_group_id, eperson_id))
FROM epersongroup2eperson;
THE_END`
echo "$report\n\n\n\n$cleanuptime\n\n$out" | mail -s "digilib
eperson table cleanup complete" dspac...@example.com

echo "email sent: $out"

else
echo "" | mail -s "digilib eperson table clean" dspac...@example.com
fi

Regards,
~~helix84

Compulsory reading: DSpace Mailing List Etiquette
https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette

helix84

unread,

Nov 9, 2016, 6:49:41 PM11/9/16

to euler, DSpace Technical Support, Terry Brady

Same script without mangled newlines:

euler

unread,

Nov 9, 2016, 9:17:37 PM11/9/16

to DSpace Technical Support, esn...@seafdec.org.ph, terry...@georgetown.edu, hel...@centrum.sk

Dear Helix and Terry,

I am running this dspace instance in a windows environment so I just manually run the SQL commands from your script and it worked. Running the migrate command though I encountered another error this time from the table group2group (Message : ERROR: could not create unique index "group2group_pkey" Detail: Key (parent_id, child_id)=(e226cfe7-385e-4547-977e-3de9e16a4b3e, a1ef01c6-0420-47de-ba1c-50c695644bcf) is duplicated., so I just run your SQL again but this time replacing eperson_group_id and eperson_id by parent_id and child_id respectively. Now when I run the migrate command, it is successful.

Thank you so much for this!

Best regards,

euler

Terry Brady

unread,

Nov 10, 2016, 6:24:29 PM11/10/16

to euler, DSpace Technical Support, Ivan Masár

Is this issue captured in a Jira ticket?

Should the migration script trap this error or attempt to clean up the issue?

helix84

unread,

Nov 11, 2016, 7:08:12 AM11/11/16

to Terry Brady, euler, DSpace Technical Support

On Fri, Nov 11, 2016 at 12:24 AM, Terry Brady <Terry...@georgetown.edu> wrote:

Is this issue captured in a Jira ticket?

No, so far I thought I had a broken customization of the LDAP module and I couldn't find the root cause. This is the first time I've seen a similar problem in the wild.

euler, are the duplicate netids coming from LDAP auth plugin? Can you post your authentication-ldap.cfg (excluding any passwords, obviously)?

euler

unread,

Nov 12, 2016, 6:45:48 AM11/12/16

to DSpace Technical Support, Terry...@georgetown.edu, esn...@seafdec.org.ph, hel...@centrum.sk

On Friday, November 11, 2016 at 8:08:12 PM UTC+8, helix84 wrote:

On Fri, Nov 11, 2016 at 12:24 AM, Terry Brady <Terry...@georgetown.edu> wrote:
Is this issue captured in a Jira ticket?

No, so far I thought I had a broken customization of the LDAP module and I couldn't find the root cause. This is the first time I've seen a similar problem in the wild.

euler, are the duplicate netids coming from LDAP auth plugin? Can you post your authentication-ldap.cfg (excluding any passwords, obviously)?

Hi helix,

I inherited this DSpace instance that is running 1.6 that I upgraded to 5.x. I checked its old config files and I believed that LDAP authentication was not or never enabled (ldap.enable = false and most of its settings were commented). Right now, the only authentication enabled is the default password authentication. I have no idea how the duplicate entries were generated.

Regards,

euler

helix84

unread,

Nov 12, 2016, 7:30:07 AM11/12/16

to euler, DSpace Technical Support, Terry Brady

Interesting. I have both LDAP and password auth enabled, but password
is only for a couple of admins and all the duplicates are in users
registered via LDAP.

So I suspect the problem is not in any particular auth plugin, but
probably in some common registration code.

Though as far as I can see, the code to create EPerson only calls auth
method-specific code:

https://github.com/DSpace/DSpace/blob/dspace-5_x/dspace-api/src/main/java/org/dspace/authenticate/AuthenticationManager.java#L269

I guess there is either a race condition when checking whether a user
already exists or this happens when users submit the
login/registration form multiple times.

One suspect thing that could cause one of these conditions is the context cache:

https://github.com/DSpace/DSpace/blob/dspace-5_x/dspace-api/src/main/java/org/dspace/eperson/EPerson.java#L199

Reply all

Reply to author

Forward