<html>Hi Elliot,<br><br>I just upgraded the Balsam Consortium from 2.0.9 up to 2.3.5. I can tell you that there are half a dozen places where that kind of thing happens. I suspect the moral is that we need to stay on top of our upgrades but, that said, my solution was to take snapshots after each major upgrade and hack out the offending duplicates when I encountered them. It took several restores and restarts to get thorugh all of the scripts but frequent snapshots made it much less painful.<br><br>I am not enough of a SQL wonk to know how one writes a script that checks update log first and conditionally executes the patch. In later scripts the devs have mercifully added notices that say, "this upgrade may fail since it is intended to provide a patch that may already exist" before they do the patch in a Begin transaction block. That is not consistant as you walk through the upgrade scripts however so don't count on it being "okay" if a block fails. Ultimately I found that, as painful as it was, walking through each upgrade script in sequence, restarts not withstanding, yields the best schema at the end of the day.<br><br>In sum, if misery loves company, you are well loved. :)<br>Steve Wills<br><div><font color="#0000ff" face="Verdana" size="2"></font> </div>
<blockquote style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px"><font face="Tahoma" size="2">-----Original Message-----<br><b>From:</b> Elliot Voris [mailto:evoris@slcconline.edu]<br><b>Sent:</b> Wednesday, April 17, 2013 07:50 PM<br><b>To:</b> open-ils-dev@list.georgialibraries.org, <br>evergreen-admin@list.evergreen-ils.org<br><b>Subject:</b> [Evergreen-admin] Evergreen 2.2.0-2.2.1 Upgrade Script - Actions Already Logged in config.upgrade_log<br><br></font>
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<table id="201179" link="#0563C1" vlink="#954F72" border="0" height="400" lang="EN-US" width="100%"><tbody><tr><td valign="top" width="100%">
<div class="WordSection1">
<p class="MsoNormal">Hey, All<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I?m working on upgrading my (currently 2.1.1 in production) Evergreen server up to the latest version 2.3.5, as of this morning when I started. I?m having a bit of trouble with one of the version-upgrade scripts (Open-ILS/src/sql/Pg/version-upgrade/2.2.0-2.2.1-upgrade-db.sql).
All the previous scripts worked (2.1.1-2.1.2, 2.1.2-2.1.3, 2.1-2.2) without anything of concern. When I try to run this script, I get the following output:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">postgres@evergreen-test:~/scripts/version-upgrade$<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">postgres@evergreen-test:~/scripts/version-upgrade$ psql -U evergreen -h localhost -f ./2.2.0-2.2.1-upgrade-db.sql old_evergreen<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">Password for user evergreen:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">BEGIN<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">INSERT 0 1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:10: ERROR: duplicate key value violates unique constraint "upgrade_log_pkey"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">DETAIL: Key (version)=(0722) already exists.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">CONTEXT: SQL statement "INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to)"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">PL/pgSQL function "upgrade_deps_block_check" line 17 at SQL statement<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:13: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:20: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:58: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:61: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:161: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:164: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:264: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:267: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">psql:./2.2.0-2.2.1-upgrade-db.sql:365: ERROR: current transaction is aborted, commands ignored until end of transaction block<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">ROLLBACK<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Courier New"">postgres@evergreen-test:~/scripts/version-upgrade$<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I looked into it, and sure enough 0722 already does exist in config.upgrade_log. It is not the most recent entry in that table, and it was ?applied_to? NULL. As I?m looking through the rest of the script, It looks as though upgrade operations
0725 and 0726 would likely throw the same error.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">What can I do to resolve the trouble with this particular script? My intention is to run through every one of the version-upgrade scripts between 2.1.1 and 2.3.5 (2.3.6 afterwards, as well), is that not the correct way to go about it?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks!<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Elliot J. Voris<o:p></o:p></p>
<p class="MsoNormal">IT Director<o:p></o:p></p>
<p class="MsoNormal">St. Louis Christian College<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><a href="mailto:evoris@slcconline.edu" mce_href="mailto:evoris@slcconline.edu" target="_blank">evoris@slcconline.edu</a><o:p></o:p></p>
<p class="MsoNormal"><a href="mailto:elliot@voris.me" mce_href="mailto:elliot@voris.me" target="_blank">elliot@voris.me</a><o:p></o:p></p>
<p class="MsoNormal">314.837.6777 x1250<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</td></tr></tbody></table></blockquote></html>