Hi Brian, Thanks, actually that spreadsheet is very useful. It shows that this problem is one we may repeat in the future, as that DB has several non-standard names for constraints: $1, $2, all the way through $7.
I'll take your advice and try a 4.x -> 5.x upgrade after renaming that constraint to $1 ;) Seems like this should be a fixable bug by querying that key_column_usage table. - Tim On 5/14/2015 3:05 PM, Brian Freels-Stendel wrote: > Well, I was able to pull the column names out into a spreadsheet, which may > be helpful. I've also dumped our db structure. It's v. 5.1, so it won't > have the constraint name the v.5 upgrade tripped over, but going forward it > might be useful. > > I'm also wondering if you might rename the constraint in one of your > databases from the logical "metadatavalue_item_id_fkey" to "$1" for before > trying to upgrade with flyway. > > B-- > > -----Original Message----- > From: Tim Donohue [mailto:tdono...@duraspace.org] > Sent: Thursday, May 14, 2015 12:23 PM > To: dspace-tech@lists.sourceforge.net > Subject: Re: [Dspace-tech] flyway migration error > > Hi Brian, > > I definitely think a dump of what your constraints look like could be useful. > We definitely want to avoid this being a recurring problem in any flyway > migrations involving contraints. > > As noted in my previous message, you also might want to simply query your > "information_schema.key_column_usage" table for oddly named constraints, as > that seems to be the easiest way in PostgreSQL (that I can find) to query for > column constraint names: > > SELECT * from information_schema.key_column_usage; > > In my "newer" PostgreSQL databases (that I've tried), the constraint names > returned all are logically named based on the table and/or column names (as > they are by default these days). But, it sounds like in databases that have > been around for some time, some of these constraints may have less logical > names like $1 or $2 or something else. > > I'd just be curious to know if fixing our code to simply querying the > "key_column_usage" view seems like it'd resolve this issue long term? > This is a tough scenario in that I don't have any "test data" to try this out > from, even if I've found what *looks* to be the right solution. > > - Tim > > On 5/14/2015 12:42 PM, Brian Freels-Stendel wrote: >> Good morning, >> >> This is a problem from way back, see: >> http://sourceforge.net/p/dspace/mailman/message/28781652/. If I remember >> right, it was because In The Beginning, the constraints weren't being given >> names, so they were auto-generated by the db engine. For those of us with >> old databases, this will be a recurring problem. If it would help, I can >> grab a schema dump (I know that's not the right terminology, but I'm hoping >> the meaning will be clear) from our db to compare with a current one. We've >> been up since 1.3, so we should have all of the offenders. >> >> B-- >> >> -----Original Message----- >> From: Tim Donohue [mailto:tdono...@duraspace.org] >> Sent: Thursday, May 14, 2015 11:17 AM >> To: Peter Dietz; Wally Grotophorst >> Cc: dspace-tech >> Subject: Re: [Dspace-tech] flyway migration error >> >> Hi all, >> >> Just a note to say I've now logged this as a bug: >> https://jira.duraspace.org/browse/DS-2577 >> >> In that ticket, I've tracked down the area of the code that makes the >> assumption on the name of a PostgreSQL constraint (but it makes that >> assumption based on the default PostgreSQL naming scheme for constraints). >> So, we may just need to enhance that area of the code to no longer assume >> any particular name for a PostgreSQL constraint. >> >> In the meantime, the "workaround" is also documented in that ticket (thanks >> to you all in this thread!). >> >> - Tim >> >> On 5/14/2015 12:04 PM, Peter Dietz wrote: >>> Virtual Beer for all as well. I've just ran into this, (Tim pointed >>> me to it from IRC). >>> >>> My version of the SQL, wrapping in a transaction, to be safe, is: >>> >>> BEGIN; >>> >>> ALTER TABLE metadatavalue DROP CONSTRAINT "$1"; ALTER TABLE >>> metadatavalue ADD CONSTRAINT metadatavalue_item_id_fkey FOREIGN >>> KEY(item_id) REFERENCES item(item_id); >>> >>> COMMIT; >>> >>> >>> >>> We're not sure of what the cause is. >>> 12:47 PM <tdonohue> yea, I'm not sure either. :) It's definitely a >>> "glitch" that happens sometimes (since others have seen it too). But, >>> according to the PostgreSQL docs, these contraints are SUPPOSED to be >>> named [table]_[column]_fkey >>> 12:47 PM <tdonohue> So, it seems like something has changed in >>> Postgres, or Postgres doesn't always follow it's own "rules" >>> >>> ________________ >>> Peter Dietz >>> Longsight >>> www.longsight.com <http://www.longsight.com> pe...@longsight.com >>> <mailto:pe...@longsight.com> >>> p: 740-599-5005 x809 >>> >>> On Wed, Apr 1, 2015 at 12:26 PM, Wally Grotophorst <wal...@gmu.edu >>> <mailto:wal...@gmu.edu>> wrote: >>> >>> BINGO! A virtual beer for you! >>> >>> Thanks for the clear statement (no pun intended) of what I needed to >>> do. Worked like a charm. >>> >>> - Wally >>> >>> >>> >>> Bill Tantzen wrote: >>> > Wally, >>> > Here's what I did: >>> > >>> > dspace=> \d metadatavalue >>> > Table >>> "public.metadatavalue" >>> > ... >>> > ... >>> > ... >>> > >>> > Foreign-key constraints: >>> > "$1" FOREIGN KEY (item_id) REFERENCES item(item_id) >>> > "$2" FOREIGN KEY (metadata_field_id) REFERENCES >>> > metadatafieldregistry(metadata_field_id) >>> > >>> > If your Foreign-key constraints look like the above, do the >>> following: >>> > >>> > dspace=> ALTER TABLE metadatavalue DROP CONSTRAINT "$1"; >>> > ALTER TABLE >>> > dspace=> ALTER TABLE metadatavalue ADD CONSTRAINT >>> > metadatavalue_item_id_fkey FOREIGN KEY(item_id) REFERENCES >>> > item(item_id); >>> > ALTER TABLE >>> > >>> > Then, try the conversion! >>> > Cheers, >>> > Bill >>> > >>> > On Wed, Apr 1, 2015 at 9:47 AM, Wally Grotophorst<wal...@gmu.edu >>> <mailto:wal...@gmu.edu>> wrote: >>> >> Trying to install 5.1 on a 4.1 database...on a new server (as a >>> test) >>> >> but using my 4.1 database that's imported into Postgres before I >>> launch >>> >> tomcat/dspace. >>> >> >>> >> Starts to make the schema conversion, then bombs. This part of >>> the log >>> >> looks like it's trying to tell me the problem: >>> >> >>> >> at org.flywaydb.core.Flyway.migrate(Flyway.java:811) >>> >> at >>> >> >>> >>> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:419) >>> >> ... 21 more >>> >> Caused by: org.postgresql.util.PSQLException: ERROR: constraint >>> >> "metadatavalue_item_id_fkey" of relation "metadatavalue" does >>> not exist >>> >> at >>> >> >>> >>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103) >>> >> at >>> >> >>> >>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836) >>> >> at >>> >> >>> >>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) >>> >> >>> >> >>> >> I saw some comment about this issue in a few earlier posts but >>> couldn't >>> >> figure out how to fix this from the info in the message traffic. >>> Is >>> >> there a simple SQL fix I can run prior to trying again? Or maybe >>> >> something else? >>> >> >>> >> Platform: OSX 10.10.1 >>> >> tomcat: 7.0.59 >>> >> postgres: 9.3 >>> >> dspace (trying 5.1) >>> >> >>> >> - Wally >>> >> >>> >> Wally Grotophorst >>> >> Associate University Librarian >>> >> George Mason University >>> >> Fairfax, Virginia 22030 >>> >> (703) 993-9005 >>> >> >>> >> >>> >> >>> >> >>> >> >>> >>> ------------------------------------------------------------------------------ >>> >> Dive into the World of Parallel Programming The Go Parallel >>> Website, sponsored >>> >> by Intel and developed in partnership with Slashdot Media, is >>> your hub for all >>> >> things parallel software development, from weekly thought >>> leadership blogs to >>> >> news, videos, case studies, tutorials and more. Take a look and >>> join the >>> >> conversation now. http://goparallel.sourceforge.net/ >>> >> _______________________________________________ >>> >> DSpace-tech mailing list >>> >> DSpace-tech@lists.sourceforge.net >>> <mailto:DSpace-tech@lists.sourceforge.net> >>> >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >>> >> List Etiquette: >>> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >>> >>> >>> ------------------------------------------------------------------------------ >>> Dive into the World of Parallel Programming The Go Parallel Website, >>> sponsored >>> by Intel and developed in partnership with Slashdot Media, is your >>> hub for all >>> things parallel software development, from weekly thought leadership >>> blogs to >>> news, videos, case studies, tutorials and more. Take a look and join >>> the >>> conversation now. http://goparallel.sourceforge.net/ >>> _______________________________________________ >>> DSpace-tech mailing list >>> DSpace-tech@lists.sourceforge.net >>> <mailto:DSpace-tech@lists.sourceforge.net> >>> https://lists.sourceforge.net/lists/listinfo/dspace-tech >>> List Etiquette: >>> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >>> >>> >>> >>> >>> --------------------------------------------------------------------- >>> - >>> -------- One dashboard for servers and applications across >>> Physical-Virtual-Cloud Widest out-of-the-box monitoring support with >>> 50+ applications Performance metrics, stats and reports that give you >>> Actionable Insights Deep dive visibility with transaction tracing >>> using APM Insight. >>> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y >>> >>> >>> >>> _______________________________________________ >>> DSpace-tech mailing list >>> DSpace-tech@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/dspace-tech >>> List Etiquette: >>> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >>> >> >> ---------------------------------------------------------------------- >> -------- One dashboard for servers and applications across >> Physical-Virtual-Cloud Widest out-of-the-box monitoring support with 50+ >> applications Performance metrics, stats and reports that give you Actionable >> Insights Deep dive visibility with transaction tracing using APM Insight. >> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y >> _______________________________________________ >> DSpace-tech mailing list >> DSpace-tech@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >> >> ---------------------------------------------------------------------- >> -------- One dashboard for servers and applications across >> Physical-Virtual-Cloud Widest out-of-the-box monitoring support with >> 50+ applications Performance metrics, stats and reports that give you >> Actionable Insights Deep dive visibility with transaction tracing >> using APM Insight. >> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y >> _______________________________________________ >> DSpace-tech mailing list >> DSpace-tech@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >> > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications Performance > metrics, stats and reports that give you Actionable Insights Deep dive > visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > DSpace-tech mailing list > DSpace-tech@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/dspace-tech > List Etiquette: > https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette > ------------------------------------------------------------------------------ One dashboard for servers and applications across Physical-Virtual-Cloud Widest out-of-the-box monitoring support with 50+ applications Performance metrics, stats and reports that give you Actionable Insights Deep dive visibility with transaction tracing using APM Insight. http://ad.doubleclick.net/ddm/clk/290420510;117567292;y _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
