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
