[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