<div dir="ltr">Dear all,<div><br></div><div>After a lot of brain-ache here we have found the problem and it seems to be related to upgrading from SQLite v2 to v3 along with an assumption(/bug?) within ZendTo…</div><div><br>
</div><div>This support page describes the tables and their elements used by ZendTo:</div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div style><a href="http://www.zend.to/technical.php">http://www.zend.to/technical.php</a></div>
</blockquote><div><div class="gmail_extra"><br></div><div class="gmail_extra">Note the magical element <b>rowId</b> which <i>"is referenced from several other tables and gives the unique number of each complete dropoff".</i></div>
<div class="gmail_extra"><i><br></i></div><div class="gmail_extra">Although described here, rowId is not a field of the table created by ZendTo, but is instead a hidden auto-incrementing value maintained by SQLite itself. It starts off with the value 1 and auto-increments for each insertion.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">When the v2 database is dumped out the hidden rowId field is not included for each record. So when the dumped data is re-imported into SQLite v3 to get a database in the new format the rowId starts at 1 again.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">This means each imported record gets a different rowId from what it had before.</div><div class="gmail_extra"><br></div><div class="gmail_extra">Unfortunately ZendTo uses the rowId to tie the data in various tables together and, to do this, stores it within some records. But because each record now has a brand new rowId in the v3 database the linkage is broken, and results in dropoffs showing as containing no files.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra" style>(It's a bit like renumbering a BASIC program but the renumber command not updating the line numbers within program's GOTO statements!)</div><div class="gmail_extra">
<br></div><div class="gmail_extra">A solution would be for ZendTo to generate its own unique key number for each record in the tables and use this to link them together rather than the auto-generated one.</div><div class="gmail_extra">
<br></div><div class="gmail_extra">(Or there may be some other way we've not been able to find of exporting the data such that it includes the hidden rowId values?)</div><div class="gmail_extra"><br></div><div class="gmail_extra">
We've worked around it by updating the hidden rowId values in the new v3 database to match the ones from the old v2 database, which has made all the files in the dropoffs spring back into existence. (We just hope these entries will be expired/removed before the auto-incrementing counter works its way up there again!)</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">Cheers,</div><div class="gmail_extra">Mike B-)<br><br><div class="gmail_quote">On 12 March 2013 09:44, Mike Brudenell <span dir="ltr"><<a href="mailto:mike.brudenell@york.ac.uk" target="_blank">mike.brudenell@york.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 dir="ltr">Hi, all!<div><br></div><div>We're in the middle of upgrading ZendTo our live DropOff Service from 4.08-something to the latest 4.11-9, and from Ubuntu 10.4 LTS to 12.04 LTS.</div>
<div><br></div><div>We had no problems when we tried did this same upgrade on our test server a few weeks back. However today we've been left with a ZendTo that has lost sight of the contents of the dropoffs that existed within it.</div>
<div><br></div><div>Specifically, logging in as an Admin and showing all the dropoffs lists them with their dropoff IDs, but shows 0 bytes against each. Clicking on any of the dropoffs lists no files as being present.</div>
<div><br></div><div>Uploading a new file into the service enters it properly, its dropoff shows the bytecount and the filenames are listed within the dropoff.</div><div><br></div><div>We're confident that we went through the same upgrade procedure, including exporting the SQLite 2 database and importing it into SQLite 3 to convert/upgrade it, so are rather baffled.</div>
<div><br></div><div>Can anyone shed any light/have anything to try to relink the dropoffs' files (which still exist on disk) into their corresponding dropoffs?</div><div><br></div><div>Cheers,</div><div>Mike B-)<span class=""><font color="#888888"><br clear="all">
<div><br></div>-- <br><font size="1"><font face="'arial narrow', sans-serif"><span style="font-size:small">IT Services, The University of York, Heslington, York YO10 5DD, UK<br>Tel: <a href="tel:%2B44-1904-323811" value="+441904323811" target="_blank">+44-1904-323811</a></span><span style="font-size:small"><br>
Disclaimer: <</span><a href="http://www.york.ac.uk/docs/disclaimer/email.htm" target="_blank"><span style="font-size:small">http://www.york.ac.uk/docs/disclaimer/email.htm</span></a><span style="font-size:small">></span></font><br>
</font>
</font></span></div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br><font size="1"><font face="'arial narrow', sans-serif"><span style="font-size:small">IT Services, The University of York, Heslington, York YO10 5DD, UK<br>
Tel: +44-1904-323811</span><span style="font-size:small"><br>Disclaimer: <</span><a href="http://www.york.ac.uk/docs/disclaimer/email.htm" target="_blank"><span style="font-size:small">http://www.york.ac.uk/docs/disclaimer/email.htm</span></a><span style="font-size:small">></span></font><br>
</font>
</div></div></div>