[ZendTo] Re: [BUG?] Upgrading ZendTo SQLite v2 database to v3 loses dropoffs' contents

Tue Mar 12 12:49:04 GMT 2013

Mike,

Well done for finding this one. This was a problem I inherited from the 
original UDel Dropbox package. I didn't really like the way they had 
done it either, but changing it would have meant quite a few changes to 
the source, which at that point wasn't well enough organised to be able 
to make the change reliably and consistently. However, the source is in 
a rather better state now, so it should be possible, the problem being 
issuing an upgrade that requires everyone to upgrade their SQLite 
database file as well.

There are almost certainly people on this list who know far more about 
database design than I do (that's not hard!) and could tell me the best 
way to do this; not only how to put in a proper counter but also how to 
migrate existing databases from their current (old UDel Dropbox) method 
to this new counter.

So I'm open to all useful suggestions!

Jules.

On 12/03/2013 11:58, Mike Brudenell wrote:
> Dear all,
>
> 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...
>
> This support page describes the tables and their elements used by ZendTo:
>
>     http://www.zend.to/technical.php
>
>
> Note the magical element *rowId* which /"is referenced from several 
> other tables and gives the unique number of each complete dropoff"./
> /
> /
> 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.
>
> 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.
>
> This means each imported record gets a different rowId from what it 
> had before.
>
> 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.
>
> (It's a bit like renumbering a BASIC program but the renumber command 
> not updating the line numbers within program's GOTO statements!)
>
> 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.
>
> (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?)
>
> 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!)
>
> Cheers,
> Mike B-)
>
> On 12 March 2013 09:44, Mike Brudenell <mike.brudenell at york.ac.uk 
> <mailto:mike.brudenell at york.ac.uk>> wrote:
>
>     Hi, all!
>
>     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.
>
>     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.
>
>     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.
>
>     Uploading a new file into the service enters it properly, its
>     dropoff shows the bytecount and the filenames are listed within
>     the dropoff.
>
>     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.
>
>     Can anyone shed any light/have anything to try to relink the
>     dropoffs' files (which still exist on disk) into their
>     corresponding dropoffs?
>
>     Cheers,
>     Mike B-)
>
>     -- 
>     IT Services, The University of York, Heslington, York YO10 5DD, UK
>     Tel: +44-1904-323811 <tel:%2B44-1904-323811>
>     Disclaimer: <http://www.york.ac.uk/docs/disclaimer/email.htm>
>
>
>
>
> -- 
> IT Services, The University of York, Heslington, York YO10 5DD, UK
> Tel: +44-1904-323811
> Disclaimer: <http://www.york.ac.uk/docs/disclaimer/email.htm>
>
>
> _______________________________________________
> ZendTo mailing list
> ZendTo at zend.to
> http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto
>
> Jules
>
> -- 
> Julian Field MEng MBCS CITP CEng
>
> 'Ever since the dawn of civilization, people have craved for an
>   understanding of the underlying order of the world: why it is as
>   it is, and why it exists at all. But even if we do find a complete
>   theory of everything, it is just a set of rules and equations. What
>   is it that breathes fire into the equations, and makes a universe
>   for them to describe?' - Stephen Hawking
>
> www.Zend.To
> Twitter: @JulesFM
> PGP footprint: EE81 D763 3DB0 0BFD E1DC 7222 11F6 5947 1415 B654
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/zendto/attachments/20130312/25cdc0a4/attachment-0001.html 