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

Rich Hutchinson r.hutchinson at ucl.ac.uk
Fri Feb 14 14:24:28 GMT 2014


Artyom,

Thank you! I would buy you a virtual pint if it were possible! We just 
upgraded an older release to the latest and encountered this very issue 
with the change from Sqlite2 to 3.

I tried running a standard conversion with "sqlite source.db 
.dump|sqlite3 new.db" which succeeded but of course we then had 
zero-sized drop-offs in the database. I then found your instructions and 
followed them to the letter and it worked perfectly.

Thanks again!

Rich.

On 24/06/13 12:23, Artyom Aleksandrov wrote:
> Hi,
> I also got the same bug. Here is my short soluthion for correct 
> migrate from sqlite2 to sqlite3.
> http://mailman.ecs.soton.ac.uk/pipermail/zendto/2013-June/002138.html
>
>
>
> Step by step migration:
>
> # Stop service
> # Backup your database
> $ cp zendto.sqlite zentdo.sqlite_bkp
> $ sqlite zentdo.sqlite_bkp
>
> # Remember last rowid for check it after migration.
> SELECT rowid,* from dropoff;
>
> # Create new temporary table with PK (private key)
> CREATE TABLE dropoff_tmp (
>   claimID             character varying(16) not null,
>   claimPasscode       character varying(16),
>
>   authorizedUser      character varying(16),
>
>   senderName          character varying(32) not null,
>   senderOrganization  character varying(32),
>   senderEmail         text not null,
>   senderIP            character varying(255) not null,
>   confirmDelivery     boolean default FALSE,
>   created             timestamp with time zone not null,
>   note                text,
>   PK              INTEGER PRIMARY KEY ASC
> );
>
> # Copy table context to new table with PK.
> INSERT INTO dropoff_tmp select *,rowid from dropoff;
>
> # Convert sqlite2 to sqlite3
> $ sqlite zendto.sqlite_bkp .dump | sqlite3  zendto.sqlite3
> $ sqlite3 zendto.sqlite3
>
> # Replace orig table (broke) by tmp one.
> DROP table dropoff;
> CREATE TABLE dropoff (
>   claimID             character varying(16) not null,
>   claimPasscode       character varying(16),
>
>   authorizedUser      character varying(16),
>
>   senderName          character varying(32) not null,
>   senderOrganization  character varying(32),
>   senderEmail         text not null,
>   senderIP            character varying(255) not null,
>   confirmDelivery     boolean default FALSE,
>   created             timestamp with time zone not null,
>   note                text
> );
>
> INSERT INTO dropoff 
> (claimID,claimPasscode,authorizedUser,senderName,senderOrganization,senderEmail,senderIP,confirmDelivery,created,note,rowid) 
> select * from dropoff_tmp;
>
> # Check that you have the same last rowid
> SELECT rowid,* from dropoff;
>
> # Drop tmp table
> DROP table dropoff_tmp;
>
>
> # Change you sqlite db, start service and finish ckeck
>
>
> Good luck. =)
>
>
> On Tue, Mar 12, 2013 at 4:49 PM, Jules <Jules at zend.to 
> <mailto:Jules at zend.to>> wrote:
>
>     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  <mailto: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  <http://www.Zend.To>
>>     Twitter: @JulesFM
>>     PGP footprint: EE81 D763 3DB0 0BFD E1DC 7222 11F6 5947 1415 B654
>
>     _______________________________________________
>     ZendTo mailing list
>     ZendTo at zend.to <mailto:ZendTo at zend.to>
>     http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto
>
>
>
>
> _______________________________________________
> ZendTo mailing list
> ZendTo at zend.to
> http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/zendto/attachments/20140214/812b7748/attachment-0001.html 


More information about the ZendTo mailing list