trovebox uploads fail with " Integrity constraint violation:"

[ajft]

I suspect I've managed to trigger #1471 or #1543, after successfully uploading 1710 photos I'm now unable to upload any more. Troveox is running on a new (four day old) AMI instance that I created following the "migration" email from trovebox.com. Photo imports fail with:

Processing 1 of 1 20120418T115249-200.json ... FAILED: Code 500: Query error:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
'trovebox@ajft.org-d' for key 'owner' - INSERT INTO photo
(appId,host,views,status,permission,license,hash,size,filenameOriginal,width,height,dateTaken,dateTakenDay,dateTakenMonth,dateTakenYear,dateUploaded,dateUploadedDay,dateUploadedMonth,dateUploadedYear,pathOriginal,pathBase,description,longitude,latitude,title,rotation,tags,owner,actor,id,exif,dateSortByDay)
VALUES
(:appId,:host,:views,:status,:permission,:license,:hash,:size,:filenameOriginal,:width,:height,:dateTaken,:dateTakenDay,:dateTakenMonth,:dateTakenYear,:dateUploaded,:dateUploadedDay,:dateUploadedMonth,:dateUploadedYear,:pathOriginal,:pathBase,:description,:longitude,:latitude,:title,:rotation,:tags,:owner,:actor,:id,:exif,:dateSortByDay)
Results. Processed: 0. Errored: 1.

DB queries as per $1543 show:

mysql> select count() from photo where owner="trovebox@ajft.org";
+----------+
| count() |
+----------+
| 1710 |
+----------+
1 row in set (0.00 sec)

mysql> select extra from user where id='trovebox@ajft.org';
| {"lastPhotoId":"e","lastActionId":"","lastGroupId":"","lastWebhookId":"","attrtutorialAll":"1","lastActivityId":"ti","attrprofileName":"ajft","attrtutorialPhotos":"4","attrtutorialAlbums":"1","attrtutorialUpload":"2"} |
1 row in set (0.00 sec)

mysql> select id from photo ORDER by dateUploaded DESC LIMIT 1;
+-----+
| id |
+-----+
| 1o1 |

(although 1o5 is the highest I can find when I go to http://.../p/1o1 and press "next" until it stops.)

[jmathai]

How did you upload the 1.7k photos?

[ajft]

Yes, there are 1710 photos in the system, I can see their icons and modify titles and descriptions through the web interface, but I can't upload any new photos either through the web interface or through the "import.py" scripts.

[ajft]

Jaisen,

The 7010 photos were uploaded by "import.py" in batches of from 10 .. 250
using the JSON files produced by "export-openphoto". I was manually
checking the JSON files due to previous tagging problems and didn't want to
try and upload all 7000+ photos in one go. (See footnote)

Adrian

footnote:

Proof checking of the JSON needed as there are problems with titles and
tags in a lot of them, from where the original imports into trovebox.com
produced bogus entries on reaching utf-8/non-ascii characters. (eg I had
photos where the four tags "castle, Córdoba, Andalusia, Spain" but had been
imported to become only two tags, "castle" and "C", everything from ó
onwards being dropped, photos where the "Original FIle Name" was of the
form 123-456_01.jpg being chopped to "123", everything from "-" onwards
being dropped)

On Tue, Jun 2, 2015 at 2:59 PM, Jaisen Mathai notifications@github.com
wrote:

How did you upload the 1.7k photos?

—
Reply to this email directly or view it on GitHub
#1581 (comment).

Adrian Tritschler
Melbourne, Australia

[youphyun]

Can the "migration" email from trovebox.com be found somewhere online? I am using the Amazon Ubuntu Server 14.04 LTS (HVM) image now and I am just curious how to prepare the Amazon Linux AMI 2015.03 (HVM) image so it can run Trovebox.

[ajft]

Fixed on 02-Jun-2015 with email help from Jaisen: (reply below, with real domains and names repalced by xyz)

Thank you. I've not used mysql before (other than to create the database), but some reading of the other github issues and man pages and the following did the trick:

mysql> update user set extra='{"lastPhotoId":"1o5","lastActionId":"","lastGroupId":"","lastWebhookId":"","attrtutorialAll":"1","lastActivityId":"17t","attrprofileName":"xyz","attrtutorialPhotos":"4","attrtutorialAlbums":"1","attrtutorialUpload":"2"}' where id='xyz@xyz.com";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Tested it by uploading a photo through the web interface -- went to http://xyz.com/p/1o6 -- then a further two with the import.py script (to URLs ending in 1o7 and 1o8 respectively)

[jmathai]

@joephein The link from the email points to https://trovebox.com/archive/migrate --- We're taking the site offline this weekend :)