Hi! So your query just remove duplicated rows no matter ID attribute (what I was thinking) The problem was solved. thanks a lot
2017년 7월 6일 목요일 오후 4시 20분 41초 UTC+8, Tom Desair (Atmire) 님의 말: > > Hi, > > Please try this: > > 1. Take a backup of your database > 2. Before starting the Flyway migration (bin/dspace database migrate) > execute these two SQL statements on your database: > - delete from group2group where id in (select id from group2group > g1 where exists (select 1 from group2group g2 where g2.parent_id = > g1.parent_id and g2.child_id = g1.child_id and g2.id < g1.id)); > - delete from epersongroup2eperson where id in (select id from > epersongroup2eperson g1 where exists (select 1 from > epersongroup2eperson g2 > where g2.eperson_group_id = g1.eperson_group_id and g2.eperson_id = > g1.eperson_id and g2.id < g1.id)); > 3. Run the Flyway migration and start Tomcat > > Best regards, > Tom > > > [image: logo] Tom Desair > 250-B Suite 3A, Lucius Gordon Drive, West Henrietta, NY 14586 > Gaston Geenslaan 14, Leuven 3001, Belgium > www.atmire.com > <http://atmire.com/website/?q=services&utm_source=emailfooter&utm_medium=email&utm_campaign=tomdesair> > > 2017-07-05 9:56 GMT+02:00 danielKim <hango...@gmail.com <javascript:>>: > >> Thanks for watching this question. >> I am migrating old server which is running dspace 6.0 to new server which >> is running CRIS dspace 6.0 >> So far I have done "mvn package" and "ant fresh_install" successfully. >> After that, I backup database from the old server use pg_dump and restore >> it in a new server using psql -f backupfile command. >> >> And then, I started the tomcat9 service and it is failed to start service >> I guess this is database problem. >> I stopped tomcat9 service and try to run command "[dspace]/bin/dspace >> database info" and I got the following result. >> >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> | Version | Description | >> Installed on | State | >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> | 1.1 | Initial DSpace 1.1 database schema | >> | <Baseln | >> | 1.2 | Upgrade to DSpace 1.2 schema | >> | <Baseln | >> | 1.3 | Upgrade to DSpace 1.3 schema | >> | <Baseln | >> | 1.3.9 | Drop constraint for DSpace 1 4 schema | >> | <Baseln | >> | 1.4 | Upgrade to DSpace 1.4 schema | >> | <Baseln | >> | 1.5 | Upgrade to DSpace 1.5 schema | >> | <Baseln | >> | 1.5.9 | Drop constraint for DSpace 1 6 schema | >> | <Baseln | >> | 1.6 | Initializing from DSpace 1.6 database schema | >> 2017-07-05 15:39:55 | Success | >> | 1.7 | Upgrade to DSpace 1.7 schema | >> 2017-07-05 15:39:55 | Success | >> | 1.8 | Upgrade to DSpace 1.8 schema | >> 2017-07-05 15:39:55 | Success | >> | 1.8.2.0 | DSpaceCRIS-crismodule database schema | >> 2017-07-05 15:39:56 | Success | >> | 1.8.2.1 | DSpaceCRIS-crispubmed database schema | >> 2017-07-05 15:39:58 | Success | >> | 1.8.2.2 | DSpaceCRIS-subscription database schema | >> 2017-07-05 15:39:58 | Success | >> | 3.0 | Upgrade to DSpace 3.x schema | >> 2017-07-05 15:39:58 | Success | >> | 3.2.1.0 | DSpaceCRIS-Upgrade-crismodule 182 to 321 | >> 2017-07-05 15:39:58 | Success | >> | 4.0 | Upgrade to DSpace 4.x schema | >> 2017-07-05 15:39:58 | Success | >> | 4.1.0.0 | DSpaceCRIS-Upgrade-crismodule 321 to 4 | >> 2017-07-05 15:39:59 | Success | >> | 4.3.0.0 | DSpaceCRIS-Upgrade-crismodule 4 to 43 | >> 2017-07-05 15:39:59 | Success | >> | 4.9.2015.10.26 | DS-2818 registry update | >> 2017-07-05 15:39:59 | Success | >> | 5.0.2014.08.08 | DS-1945 Helpdesk Request a Copy | >> 2017-07-05 15:39:59 | Success | >> | 5.0.2014.09.25 | DS 1582 Metadata For All Objects drop constraint | >> 2017-07-05 15:39:59 | Success | >> | 5.0.2014.09.26 | DS-1582 Metadata For All Objects | >> 2017-07-05 15:39:59 | Success | >> | 5.3.0.0 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.1 | FIX-SNAPSHOT-DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.2 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.3 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.4 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.5 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:07 | Success | >> | 5.3.0.6 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:08 | Success | >> | 5.3.1.0 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:08 | Success | >> | 5.3.1.1 | DSpaceCRIS-Upgrade-crismodule 5 to 53 | >> 2017-07-05 15:40:08 | Success | >> | 5.4.0.0 | DSpaceCRIS-Upgrade-crismodule 5 to 54 | >> 2017-07-05 15:40:08 | Success | >> | 5.4.0.1 | DSpaceCRIS-Upgrade-crismodule 5 to 54 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.0.1 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.1.0 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.1.1 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.1.2 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.1.3 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.5.1.4 | DSpaceCRIS-Upgrade-crismodule 5 to 55 | >> 2017-07-05 15:40:08 | Success | >> | 5.6.0.0 | DSpaceCRIS-Upgrade-crismodule 5 to 56 | >> 2017-07-05 15:40:08 | Success | >> | 5.6.0.1 | DSpaceCRIS-Upgrade-crismodule 5 to 56 | >> 2017-07-05 15:40:08 | Success | >> | 5.6.2.0 | DSpaceCRIS-Upgrade-crismodule 5 to 56 | >> 2017-07-05 15:40:09 | Success | >> | 5.6.2016.08.23 | DS-3097 | >> 2017-07-05 15:40:09 | Success | >> | 5.7.2017.04.11 | DS-3563 Index metadatavalue resource type id column | >> 2017-07-05 15:40:09 | Success | >> | 6.0.2015.03.06 | DS 2701 Dso Uuid Migration | >> 2017-07-05 15:40:09 | Success | >> | 6.0.2015.03.07 | DS-2701 Hibernate migration | >> | Pending | >> | 6.0.2015.08.31 | DS 2701 Hibernate Workflow Migration | >> | Pending | >> | 6.0.2016.01.03 | DS-3024 | >> | Pending | >> | 6.0.2016.01.26 | DS 2188 Remove DBMS Browse Tables | >> | Pending | >> | 6.0.2016.02.25 | DS-3004-slow-searching-as-admin | >> | Pending | >> | 6.0.2016.04.01 | DS-1955 Increase embargo reason | >> | Pending | >> | 6.0.2016.04.04 | DS-3086-OAI-Performance-fix | >> | Pending | >> | 6.0.2016.04.14 | DS-3125-fix-bundle-bitstream-delete-rights | >> | Pending | >> | 6.0.2016.05.10 | DS-3168-fix-requestitem item id column | >> | Pending | >> | 6.0.2016.07.21 | DS-2775 | >> | Pending | >> | 6.0.2016.07.26 | DS-3277 fix handle assignment | >> | Pending | >> | 6.0.2016.08.23 | DS-3097 | >> | Pending | >> | 6.0.2017.05.04 | D4CRIS-2 | >> | Pending | >> | 6.0.2017.06.15 | D4CRIS-2 | >> | Pending | >> | 6.0.2017.06.16 | D4CRIS-2 | >> | Pending | >> | 6.0.2017.06.26 | D4CRIS-2 | >> | Pending | >> >> +----------------+-----------------------------------------------------+---------------------+---------+ >> >> So I guess some are succeed maybe the database tried to migrated to new >> server when i start tomcat service. >> Now I run command "[dspace]/bin/dspace database migerate" and got the >> following error message, which showing there is duplicated keys so it can't >> create primary key on the column. >> >> >> PS C:\dspace\bin> ./dspace database migrate >> Using DSpace installation in: C:\dspace >> Java HotSpot(TM) 64-Bit Server VM warning: ignoring option >> MaxPermSize=2048m; support was removed in 8.0 >> >> Database URL: jdbc:postgresql://localhost:5432/dspace >> 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:669) >> at >> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:577) >> at >> org.dspace.storage.rdbms.DatabaseUtils.main(DatabaseUtils.java:224) >> 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:230) >> at >> org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:82) >> 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 "group2group_pkey" >> Detail: Key (parent_id, >> child_id)=(a393424d-eb09-4a5c-bb6d-f226f0a27637, >> 70e3dd59-cbc2-4c21-94a6-b11928b2bac7) is duplicated. >> Location : >> org/dspace/storage/rdbms/sqlmigration/postgres/V6.0_2015.03.07__DS-2701_Hibernate_migration.sql >> >> (C:\dspace\file:\C:\dspace\lib\dspace-api-CRIS-6.0.0-SNAPSHOT.jar!\org\dspace\storage\rdbms\sqlmigration\postgres\V6.0_2015.03.07__DS-2701_Hibernate_migration.sql) >> Line : 130 >> Statement : ALTER TABLE Group2Group add primary key (parent_id,child_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:658) >> ... 8 more >> Caused by: org.postgresql.util.PSQLException: ERROR: could not create >> unique index "group2group_pkey" >> Detail: Key (parent_id, >> child_id)=(a393424d-eb09-4a5c-bb6d-f226f0a27637, >> 70e3dd59-cbc2-4c21-94a6-b11928b2bac7) 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.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 >> >> I have noticed the group2group table has ID, parent_id, child_id and "ID" >> column doesn't have any duplicated keys but 'parent_id' and "child_id" are >> duplicated same as picture below. >> >> >> <https://lh3.googleusercontent.com/-sZfiIoGopEc/WVyakWGEksI/AAAAAAAAGnY/Gsfs-8P4JK0zHTYyph9T4Nh1batqK_v1ACLcBGAs/s1600/Untitled.png> >> >> >> Of course I can run query which can delete the duplicated rows. However, >> I want to know what is table 'group2group' and how this work in dspace. If >> I remove duplicated parent_id and child_id column, there will be empty ID >> keys as you can see in the picture. Assume we remove ID 100 because of >> duplicated keys of parent_id and child_id, there will be no 100 between id >> 999 and id 101. >> >> >> I am waiting your guys suggestion how to solve this database problem and >> please give me some idea :( >> >> >> I asked the student who handled old dspace server before and he said he >> did some modification to do certain tasks. I think this is the main cause >> of the problem that "dspace database migration" failed. >> >> Please save me. Thanks in advance... >> >> -- >> 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 <javascript:>. >> To post to this group, send email to dspac...@googlegroups.com >> <javascript:>. >> Visit this group at https://groups.google.com/group/dspace-tech. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to dspace-tech@googlegroups.com. Visit this group at https://groups.google.com/group/dspace-tech. For more options, visit https://groups.google.com/d/optout.
