<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;background-color:#FFFFFF;font-family:Calibri,Arial,Helvetica,sans-serif;">
<p>Hi Jason,</p>
<p><br>
</p>
<p>You need to try this on the database pre-migration...the migration is creating the job and job_input_files and moving data from the import_job and import_job_input_file tables into those new tables then droping the import_* tables. Phew, does that make sense?</p>
<p><br>
</p>
<p>So, it looks like when it's moving a row from import_job_input_file into job_input_file it's hitting a DB constraint that the job foreign key is missing. </p>
<p><br>
</p>
<p>Try looking in a version of the DB prior to attempting the migration...</p>
<p>b,chris. <br>
</p>
<p><br>
</p>
<p><br>
</p>
<div id="Signature">
<div name="divtagdefaultwrapper" style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:; margin:0">
<div class="BodyFragment"><font size="2">
<div class="PlainText">Chris Fitzpatrick | <font size="2">Developer, ArchivesSpace</font><br>
Skype: chrisfitzpat | Phone: 918.236.6048<br>
http://archivesspace.org/<br>
</div>
</font></div>
</div>
</div>
<br>
<br>
<div style="color: rgb(0, 0, 0);">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b>From:</b> Jason Brock <Jason.Brock@georgetown.edu><br>
<b>Sent:</b> Wednesday, July 22, 2015 7:47 PM<br>
<b>To:</b> Chris Fitzpatrick<br>
<b>Cc:</b> Archivesspace Users Group<br>
<b>Subject:</b> Re: [Archivesspace_Users_Group] Upgrade to 1.3.0 fails with db_error</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div><br>
</div>
Thanks for your response, Chris. Based on the error I had been looking at the relationship job_input_file(job_id) and job(id), but in either case I cannot find an orphaned row. Each of your suggested queries returns an empty set, as does a similar query on
the job_input_file table
<div><br>
</div>
<div>
<p class=""><span class=""><font face="monospace, monospace">mysql> select * from job_input_file where job_id NOT IN ( SELECT id from job);</font></span></p>
<p class=""><span class=""><font face="monospace, monospace">Empty set (0.00 sec)</font></span></p>
<p class=""><br>
</p>
<p class="">I will continue to investigate and begin deleting rows to see if I can make some progress with a little trial and error.</p>
<p class="">- Jason</p>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Wed, Jul 22, 2015 at 9:10 AM, Chris Fitzpatrick <span dir="ltr">
<<a href="mailto:Chris.Fitzpatrick@lyrasis.org" target="_blank">Chris.Fitzpatrick@lyrasis.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left-width:1px; border-left-color:rgb(204,204,204); border-left-style:solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; background-color:rgb(255,255,255)">
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p>Hi Jason,</p>
<p><br>
</p>
<p>Hm, looks like maybe there's a import_job_input_file row that's been orphaned from its associated job. </p>
<p><br>
</p>
<p>Can you see if these queries return anything:</p>
<p><br>
</p>
<p>mysql> select * from import_job_input_file where job_id NOT IN ( SELECT id from import_job);<br>
mysql> select * from import_job_input_file where job_id IS NULL; <br>
</p>
<p><br>
</p>
<p><br>
</p>
<p>If it does, you probably should delete these rows.</p>
<p><br>
</p>
<p>b,chris. <br>
</p>
<div>
<div name="divtagdefaultwrapper">
<div><font size="2">
<div>Chris Fitzpatrick | <font size="2">Developer, ArchivesSpace</font><br>
Skype: chrisfitzpat | Phone: <a href="tel:918.236.6048" value="+19182366048" target="_blank">
918.236.6048</a><br>
<a href="http://archivesspace.org/" target="_blank">http://archivesspace.org/</a><br>
</div>
</font></div>
</div>
</div>
<br>
<br>
<div style="color:rgb(0,0,0)">
<hr style="display:inline-block; width:98%">
<div dir="ltr"><font style="font-size:11pt" face="Calibri, sans-serif" color="#000000"><b>From:</b>
<a href="mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org" target="_blank">
archivesspace_users_group-bounces@lyralists.lyrasis.org</a> <<a href="mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org" target="_blank">archivesspace_users_group-bounces@lyralists.lyrasis.org</a>> on behalf of KNOWLES Claire <<a href="mailto:Claire.Knowles@ed.ac.uk" target="_blank">Claire.Knowles@ed.ac.uk</a>><br>
<b>Sent:</b> Wednesday, July 22, 2015 9:09 AM<br>
<b>To:</b> Jason Brock<br>
<b>Cc:</b> <a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">
archivesspace_users_group@lyralists.lyrasis.org</a>
<div>
<div class="h5"><br>
<b>Subject:</b> Re: [Archivesspace_Users_Group] Upgrade to 1.3.0 fails with db_error</div>
</div>
</font>
<div> </div>
</div>
<div>
<div class="h5">
<div>
<div>
<div>
<div>Hi Jason,</div>
<div><br>
</div>
<div>Not all of my jobs are being migrated to the new job table causing the same issue. I’ve not resolved it yet.</div>
<div><br>
</div>
<div>Claire</div>
<div><br>
</div>
<div>
<div>--</div>
<div>Claire Knowles</div>
<div>Library Digital Development Manager</div>
<div>Library and University Collections, Information Services</div>
<div>University of Edinburgh</div>
<div>Tel: 0131 6503023</div>
<div>Email: <a href="mailto:claire.knowles@ed.ac.uk" target="_blank">claire.knowles@ed.ac.uk</a></div>
<div><br>
</div>
</div>
</div>
</div>
<div><br>
</div>
<span>
<div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; border-width:1pt medium medium; border-style:solid none none; padding:3pt 0in 0in; border-top-color:rgb(181,196,223)">
<span style="font-weight:bold">From: </span>Jason Brock <<a href="mailto:Jason.Brock@georgetown.edu" target="_blank">Jason.Brock@georgetown.edu</a>><br>
<span style="font-weight:bold">Date: </span>Tuesday, 21 July 2015 15:51<br>
<span style="font-weight:bold">To: </span>KNOWLES Claire <<a href="mailto:Claire.Knowles@ed.ac.uk" target="_blank">Claire.Knowles@ed.ac.uk</a>><br>
<span style="font-weight:bold">Cc: </span>"<a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">archivesspace_users_group@lyralists.lyrasis.org</a>" <<a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">archivesspace_users_group@lyralists.lyrasis.org</a>><br>
<span style="font-weight:bold">Subject: </span>Re: [Archivesspace_Users_Group] Upgrade to 1.3.0 fails with db_error<br>
</div>
<div><br>
</div>
<div>
<div>
<div dir="ltr">Yes, Claire, we are running the setup-database.sh migration script in all cases. Here is the error we receive:
<div><br>
</div>
<div>
<div><font face="monospace,monospace"># archivesspace-1.3.0/scripts/setup-database.sh</font></div>
<div><font face="monospace,monospace">Loading ArchivesSpace configuration file from path: /opt/archivesspace-1.3.0/config/config.rb</font></div>
<div><font face="monospace,monospace">Loading ArchivesSpace configuration file from path: /opt/archivesspace-1.3.0/config/config.rb</font></div>
<div><font face="monospace,monospace">Running migrations against jdbc:mysql://localhost:3306/archivesspace?useUnicode=true&characterEncoding=UTF-8</font></div>
<div><font face="monospace,monospace">*** ADDING SOME ENUMS</font></div>
<div><font face="monospace,monospace">*** Genre Form to note_index_item_type enum list</font></div>
<div><font face="monospace,monospace">Triggering reindex of resource</font></div>
<div><font face="monospace,monospace">Triggering reindex of archival_object</font></div>
<div><font face="monospace,monospace">Triggering reindex of digital_object</font></div>
<div><font face="monospace,monospace">Making location_temporary list editble</font></div>
<div><font face="monospace,monospace">Sequel::ForeignKeyConstraintViolation: Java::ComMysqlJdbcExceptionsJdbc4::MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`archivesspace`.`job_input_file`, CONSTRAINT
`job_input_file_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`))</font></div>
<div><font face="monospace,monospace"> raise_error at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/database/misc.rb:423</font></div>
<div><font face="monospace,monospace"> statement at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/adapters/jdbc.rb:648</font></div>
<div><font face="monospace,monospace"> statement at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/adapters/jdbc.rb:646</font></div>
<div><font face="monospace,monospace"> execute at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/adapters/jdbc.rb:245</font></div>
<div><font face="monospace,monospace"> hold at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/connection_pool/threaded.rb:98</font></div>
<div><font face="monospace,monospace"> hold at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/connection_pool/threaded.rb:87</font></div>
<div><font face="monospace,monospace"> synchronize at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:255</font></div>
<div><font face="monospace,monospace"> execute at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/adapters/jdbc.rb:244</font></div>
<div><font face="monospace,monospace"> execute_insert at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/adapters/jdbc.rb:276</font></div>
<div><font face="monospace,monospace"> execute_insert at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:927</font></div>
<div><font face="monospace,monospace"> insert at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:336</font></div>
<div><font face="monospace,monospace"> (root) at /opt/archivesspace-1.3.0/lib/common.jar!/db/migrations/037_generalized_job_table.rb:75</font></div>
<div><font face="monospace,monospace"> each at org/jruby/RubyArray.java:1613</font></div>
<div><font face="monospace,monospace"> (root) at /opt/archivesspace-1.3.0/lib/common.jar!/db/migrations/037_generalized_job_table.rb:73</font></div>
<div><font face="monospace,monospace"> instance_eval at org/jruby/RubyBasicObject.java:1533</font></div>
<div><font face="monospace,monospace"> apply at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:100</font></div>
<div><font face="monospace,monospace"> run at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:528</font></div>
<div><font face="monospace,monospace"> checked_transaction at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:466</font></div>
<div><font face="monospace,monospace"> run at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:527</font></div>
<div><font face="monospace,monospace"> each at org/jruby/RubyArray.java:1613</font></div>
<div><font face="monospace,monospace"> run at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:524</font></div>
<div><font face="monospace,monospace"> run at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/extensions/migration.rb:391</font></div>
<div><font face="monospace,monospace"> setup_database at file:/opt/archivesspace-1.3.0/lib/common.jar!/db/db_migrator.rb:184</font></div>
<div><font face="monospace,monospace"> (root) at ../scripts/rb/migrate_db.rb:43</font></div>
<div><font face="monospace,monospace"> connect at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:69</font></div>
<div><font face="monospace,monospace"> connect at /opt/archivesspace-1.3.0/gems/gems/sequel-4.20.0/lib/sequel/core.rb:95</font></div>
<div><font face="monospace,monospace"> (root) at ../scripts/rb/migrate_db.rb:28</font></div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Jul 21, 2015 at 4:54 AM, KNOWLES Claire <span dir="ltr">
<<a href="mailto:Claire.Knowles@ed.ac.uk" target="_blank">Claire.Knowles@ed.ac.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left-width:1px; border-left-color:rgb(204,204,204); border-left-style:solid; padding-left:1ex">
<div style="word-wrap:break-word; color:rgb(0,0,0); font-size:11px; font-family:Verdana,sans-serif">
<div>
<div>
<div>Hi Jason,</div>
<div><br>
</div>
<div>Did you run the setup-database.sh script to update your database tables? I had that error too. Now my setup-database.sh is failing to migrate jobs, has anyone else had problems updating their database for 1.3.0?</div>
<div><br>
</div>
<div>Cheers,</div>
<div><br>
</div>
<div>Claire</div>
<div><br>
</div>
<div>
<div>--</div>
<div>Claire Knowles</div>
<div>Library Digital Development Manager</div>
<div>Library and University Collections, Information Services</div>
<div>University of Edinburgh</div>
<div>Tel: 0131 6503023</div>
<div>Email: <a href="mailto:claire.knowles@ed.ac.uk" target="_blank">claire.knowles@ed.ac.uk</a></div>
<div><br>
</div>
</div>
</div>
</div>
<div><br>
</div>
<span>
<div style="font-family:Calibri; font-size:11pt; text-align:left; color:black; border-width:1pt medium medium; border-style:solid none none; padding:3pt 0in 0in; border-top-color:rgb(181,196,223)">
<span style="font-weight:bold">From: </span><<a href="mailto:archivesspace_users_group-bounces@lyralists.lyrasis.org" target="_blank">archivesspace_users_group-bounces@lyralists.lyrasis.org</a>> on behalf of Jason Brock <<a href="mailto:Jason.Brock@georgetown.edu" target="_blank">Jason.Brock@georgetown.edu</a>><br>
<span style="font-weight:bold">Reply-To: </span>Archivesspace Users Group <<a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">archivesspace_users_group@lyralists.lyrasis.org</a>><br>
<span style="font-weight:bold">Date: </span>Monday, 20 July 2015 19:54<br>
<span style="font-weight:bold">To: </span>"<a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">archivesspace_users_group@lyralists.lyrasis.org</a>" <<a href="mailto:archivesspace_users_group@lyralists.lyrasis.org" target="_blank">archivesspace_users_group@lyralists.lyrasis.org</a>><br>
<span style="font-weight:bold">Subject: </span>[Archivesspace_Users_Group] Upgrade to 1.3.0 fails with db_error<br>
</div>
<div>
<div>
<div><br>
</div>
<div>
<div>
<div dir="ltr">When attempting to upgrade our ArchivesSpace instance either directly from 1.1.0 or incrementally from 1.1.0 to 1.1.2 to 1.2.0 to 1.3.0 we receive the following error when trying to start the service:
<div><br>
</div>
<div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">Connection to backend failed ({"error":{"db_error":["Database integrity constraint conflict: Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: Unknown column
'position' in 'order clause'"]}}</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">). Retrying...</font></div>
</div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace"><br>
</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">We are running ArchivesSpace on RHEL 6 against a MySQL 5.6 database.</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace"><br>
</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">Any thoughts on this error are appreciated!</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace"><br>
</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">- Jason</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace"><br>
</font></div>
<div style="color:rgb(0,0,0); font-size:12.8000001907349px"><font face="monospace,monospace">
<div dir="ltr">
<div><span style="font-family:arial,sans-serif; font-size:13px"><br>
</span></div>
<span style="font-family:arial,sans-serif; font-size:13px">-- </span>
<div style="font-family:arial,sans-serif; font-size:13px">Jason Brock<br>
Sr. Systems Administrator<br>
Georgetown University Library<br>
<a href="tel:202.687.5735" value="+12026875735" target="_blank">202.687.5735</a></div>
</div>
<br>
</font></div>
</div>
</div>
</div>
</div>
</div>
</span></div>
<br>
The University of Edinburgh is a charitable body, registered in<br>
Scotland, with registration number SC005336.<br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</span></div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>