Integrity constraint violation when uploading

[lcx]

After upgrading from 3.0.6 to 4.0.0-rc6 and running all DB upgrades manually, all was fine. I uploaded several images but all of a sudden I can't upload anymore and always get this error:

[Sun Aug 31 06:06:41 2014] [error] [client 213.143.121.75] {severity:warn, description:"Uncaught exception (/var/www/openphoto/src/libraries/external/epi/EpiException.php:13): Query error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'cristian@example.com-1' 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,tags,albums,groups,title,description,latitude,longitude,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,:tags,:albums,:groups,:title,:description,:latitude,:longitude,:owner,:actor,:id,:exif,:dateSortByDay)", additional:}, referer: http://openphoto.lcx.at/photos/upload

but I only have this entry in the DB:

mysql> select distinct(owner) from photo;
+-----------------+
| owner           |
+-----------------+
| cristian@example.com |
+-----------------+
1 row in set (0.03 sec)

[jmathai]

What do the following queries give?

mysql> select extra from user where id='cristian@example.com';

mysql> select id from photo where id='cristian@example.com' ORDER by dateUploaded DESC LIMIT 1;

[lcx]

| extra                                                                     |
+---------------------------------------------------------------------------+
| {"lastPhotoId":"1","lastActionId":"","lastGroupId":"","lastWebhookId":""} |
+---------------------------------------------------------------------------+

mysql> select id from photo where id='cristian@example.com' ORDER by dateUploaded DESC LIMIT 1;
Empty set (0.00 sec)

but I assume you meant owner=cristian...

mysql> select id from photo where owner='cristian@example.com' ORDER by dateUploaded DESC LIMIT 1;
+----+
| id |
+----+
| 20 |
+----+
1 row in set (0.02 sec)

[jmathai]

You'll need to update that extra column. Assuming you haven't created a group or webhook you should be fine with just setting the lastPhotoId to one more than what's in the database.

{"lastPhotoId":"21","lastActionId":"","lastGroupId":"","lastWebhookId":""}

Try that and see if you can upload photos.

[lcx]

still the same. But the id 21 can't be right.

select count(*) from photo where owner="cristian@example.com";
+----------+
| count(*) |
+----------+
|     2579 |
+----------+

I already have a photo with ID 22 which is quite old. But I got the idea. Looking at my last uploaded photo and searching the DB a little bit I found the correct last id which was "2p8"

set the extra field to that and it now works again. But I have no idea how and why this happened. Will continue to upload photos from my iPhone and get back if it happens again.

[lcx]

I could reproduce the issue. From the iPhone App I selected a bunch of photos to upload and checked two albums. Once I hit upload the app crashes. It also seems that after this issue I'm logged out from the web interface then after logging in, the extra field is reset and the issue occurs. Mystery solved :) it seems to be after all #1471

[jmathai]

@lcx Did you have the fix for #1471? That should have kept this from happening. Please confirm by logging out and logging back in to make sure it doesn't wipe out the user.extra column. You can save the current value in case it gets wiped :).

[lcx]

I was on tag 4.0.0-rc6 so actually the fix should have been there. I just tried master and the issue still happens. Once I log in the extra field is blanked out.