thermann78 commented 8 years ago

I open http://wheelmap.org/nodes/321597100 and see on both Safari and Firefox just placeholders in the section "Fotos von diesem Ort". Clicking on them leads to an error message prompting "The image could not be loaded". This only seems to occur with this place. No idea, why.

holgerd commented 8 years ago

idea what to test: maybe the uploader removed the photo afterwards and we do not do a proper cleanup afterwards (or the page is cached)?

lennerd commented 8 years ago

I'm quite sure that has something todo with he job queue. So pictures are not uploaded and transformed to other sizes immediately but instead are stored in a queue. If this queue is corrupted (e.g. by failing sync with the OSM import) it's possible that we also loose pictures in the process.

Maybe we can just clean up this node (via admin panel) and see if it happens again in the future.

holgerd commented 7 years ago

The 2 photos are still missing on wheelmap live as of today. These are not provided by streetspotr, so it may be a different problem.

next step: verify the generation of resized images work properly.

Hoverbear commented 7 years ago

If resizing is not working then we should still be able to access the originals at an address like https://asset3.wheelmap.org/system/uploads/photo/000/063/136/original.jpg

1000miles commented 7 years ago

Update, 2017, June 8

Research results so far:

14.733 photos between 2014-01-07 17:44:38 and 2015-12-17 18:35:22 in production
- have the attribute image_processing=true (1)
- have null resized versions (e.g. image_width: null, image_height: null)
- were uploaded by user_id: 1 (wheelmap_visitor)
the non-resized images also effect pois, not only provided pois => we can exclude that it has something to do with the streetspotr rake task (but since streetspotr also uses the anonymous account wheelmap_visitor this could have been effected by this bug)
in the past the accounts wheelmap_visitor and wheelmap_android were supposed to NOT be able to upload images but somehow it started the Carrierwave backgrounder delayed job, created a image paths and also the original.jpg which isn't there => we recently introduced the flag anonymous to all users (false by default). when we now upload images with these two accounts, it is not possible anymore.

Examples: https://wheelmap.org/nodes/2739053220 https://wheelmap.org/nodes/355922690

| 73991 | original.jpg |                1 | 2739053220 |       1 | 2015-12-17 18:35:20 |        NULL |
| 73992 | original.jpg |                1 | 2739053220 |       1 | 2015-12-17 18:35:20 |        NULL |
| 73938 | original.jpg |                1 |  355922690 |       1 | 2015-12-17 18:35:13 |        NULL |         NULL |

URLs to original.jpg for poi 2739053220:

https://asset2.wheelmap.org/system/uploads/photo/000/073/991/original.jpg => not found
https://asset0.wheelmap.org/system/uploads/photo/000/073/992/original.jpg => not found
https://asset1.wheelmap.org/system/uploads/photo/000/073/938/original.jpg => not found

Some stats:

mysql> SELECT id, image, image_processing, poi_id, user_id, created_at, image_width FROM photos WHERE image_width IS NULL AND user_id = 1;

+-------+--------------+------------------+------------+---------+---------------------+-------------+--------------+
| id    | image        | image_processing | poi_id     | user_id | created_at          | image_width | image_height |
+-------+--------------+------------------+------------+---------+---------------------+-------------+--------------+
| 21627 | original.jpg |                1 |  314401618 |       1 | 2014-01-07 17:44:38 |        NULL |         NULL | 
| 73991 | original.jpg |                1 | 2739053220 |       1 | 2015-12-17 18:35:20 |        NULL |         NULL |
| 73992 | original.jpg |                1 | 2739053220 |       1 | 2015-12-17 18:35:20 |        NULL |         NULL |
| ..... | ............ |                . | .......... |       . | ................... |        .... |         .... | 
| 74005 | original.jpg |                1 | 1869122958 |       1 | 2015-12-17 18:35:22 |        NULL |         NULL |
+-------+--------------+------------------+------------+---------+---------------------+-------------+--------------+
14733 rows in set (0.03 sec)

Conclusions:

According to Carrierwave backgrounder docs true for image_processing means that the image resizing process is incomplete while false indicates it is complete. Since we are using that gem for delayed jobs to resize images this could have been the reason why the image processing did not pass through for those photos uploaded by wheelmap_visitor
When we now upload images with user accounts other than wheelmap_visitor (which is not possible anymore) in production they appear immediately. Our hunch here is that the image resizing bug was fixed in the past but the 14733 broken images have not been corrected/removed so far. The 14733 effected images are useless since we do not have even the originals of them. Deleting them would be the most obvious next step?

holgerd commented 7 years ago

The 14733 effected images are useless since we do not have even the originals of them. Deleting them would be the most obvious next step?

@1000miles Are you sure we do not have the originals? Then please delete the invalid references.

cc @Svenyo

Svenyo commented 7 years ago

@1000miles @holgerd Were those Streetspotr pics? Then I could look if I find the corresponding CSV files...

1000miles commented 7 years ago

@Svenyo cc @holgerd Here is an excerpt of 100 photos that are corrupt on production.

Steps to reproduce:

Please pick up any of the poi_id out of the list,
Go to their poi_id address, e.g. wheelmap.org/nodes/314401618
Right click on one of the broken images displaying gallery_preview
Take the copied URL and check if it's broken, then replace e.g. https://asset3.wheelmap.org/system/uploads/photo/000/021/627/gallery_preview.jpg with https://asset3.wheelmap.org/system/uploads/photo/000/021/627/original.jpg.

If the original.jpg also causes 500 Internal Error, then the original does not exist and the poi can be deleted.

+-------+------------+---------+----------+---------------------+------------------+-------------+--------------+
| id    | poi_id     | user_id | taken_at | created_at          | image_processing | image_width | image_height |
+-------+------------+---------+----------+---------------------+------------------+-------------+--------------+
| 21627 |  314401618 |       1 | NULL     | 2014-01-07 17:44:38 |                1 |        NULL |         NULL |
| 59274 |  330263916 |       1 | NULL     | 2015-12-17 17:44:13 |                1 |        NULL |         NULL |
| 59275 |  330263916 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59276 | 1545210729 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59277 | 1545210729 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59278 |  701363351 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59279 |  245495351 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59280 | 1414848452 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59281 |  321799596 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59282 |  321799596 |       1 | NULL     | 2015-12-17 17:44:14 |                1 |        NULL |         NULL |
| 59283 |  321799596 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59284 | 1732699011 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59285 | 1732699011 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59286 | 1732699011 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59287 |  311407209 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59288 |  311407209 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59289 |   36425649 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59290 |   36425649 |       1 | NULL     | 2015-12-17 17:44:15 |                1 |        NULL |         NULL |
| 59291 |   36425647 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59292 |  345758417 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59293 |  345758417 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59294 |  345758417 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59295 | 2194127561 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59296 | 2194138322 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59297 | 2194127557 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59298 | -209176543 |       1 | NULL     | 2015-12-17 17:44:16 |                1 |        NULL |         NULL |
| 59299 | 2194127567 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59300 |  -60039579 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59301 |  -60039582 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59302 |  759497436 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59303 |  614236441 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59304 |  614236441 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59305 | 2356351844 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59306 | 2192755332 |       1 | NULL     | 2015-12-17 17:44:17 |                1 |        NULL |         NULL |
| 59307 | 2192755332 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59308 |  441277745 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59309 |  441277745 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59310 |  892559153 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59311 |  892559153 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59312 |  280727791 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59313 |  280727791 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59314 |  893020131 |       1 | NULL     | 2015-12-17 17:44:18 |                1 |        NULL |         NULL |
| 59315 |  893020131 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59316 |  293129395 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59317 | 1495573995 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59318 | 1239475815 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59319 |  791236350 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59320 | 1051721977 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59321 | 1051721977 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59322 |  341004458 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59323 | 1027320577 |       1 | NULL     | 2015-12-17 17:44:19 |                1 |        NULL |         NULL |
| 59324 |  748927003 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59325 | 3371931701 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59326 | 3371931701 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59327 | 1993648123 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59328 |  258051477 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59329 |  258051477 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59330 |  258051477 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59331 |  602593303 |       1 | NULL     | 2015-12-17 17:44:20 |                1 |        NULL |         NULL |
| 59332 |  805226428 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59333 | 1313823255 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59334 |  373252133 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59335 |  373258837 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59336 |  373258842 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59337 |  373258842 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59338 |  -88871132 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59339 |  915677161 |       1 | NULL     | 2015-12-17 17:44:21 |                1 |        NULL |         NULL |
| 59340 | 1263264151 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59341 |  665482208 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59342 |  665482208 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59343 |  767116879 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59344 |  767116879 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59345 | 1315862764 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59346 |  587098396 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59347 |  665482209 |       1 | NULL     | 2015-12-17 17:44:22 |                1 |        NULL |         NULL |
| 59348 |  665482209 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59349 |  336596958 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59350 |  336596958 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59351 | -202207358 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59352 | 3389749093 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59353 | 3389749422 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59354 | 3389749422 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59355 | 3389749093 |       1 | NULL     | 2015-12-17 17:44:23 |                1 |        NULL |         NULL |
| 59356 |  566696177 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59357 |  566696177 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59358 | 1253965642 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59359 | 1253965642 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59360 |  352717513 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59361 |  250380570 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59362 |  250380570 |       1 | NULL     | 2015-12-17 17:44:24 |                1 |        NULL |         NULL |
| 59363 | 1253965641 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59364 | 1253965641 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59365 | -202207358 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59366 | 1852081106 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59367 | 1852081106 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59368 |  567334311 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59369 |  408340919 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59370 | 3389748792 |       1 | NULL     | 2015-12-17 17:44:25 |                1 |        NULL |         NULL |
| 59371 |  665482208 |       1 | NULL     | 2015-12-17 17:44:26 |                1 |        NULL |         NULL |
| 59372 |  665482208 |       1 | NULL     | 2015-12-17 17:44:26 |                1 |        NULL |         NULL |
+-------+------------+---------+----------+---------------------+------------------+-------------+--------------+
100 rows in set (0.03 sec)

1000miles commented 7 years ago

@Svenyo cc @holgerd

IMPORTANT: We need to delete the broken images first before we can do new imports from csv via the rake task, otherwise the photo duplicate checker will not import the photos because it thinks the photo already exists (even though it is broken). We will wait for your check and final approval to remove the corrupt 14733 effected images before we import new csv data from streetspotr.

holgerd commented 7 years ago

@1000miles Yes please delete the broken images but please keep a log so we will know what places had missing photos. That way we could later analyze.

cc @Svenyo

Svenyo commented 7 years ago

@holgerd @1000miles Agreed!

ghost commented 7 years ago

@holgerd @Svenyo would a list including photo_id, poi_id, created_at be enough for the log, or would you need any other column?

holgerd commented 7 years ago

no, that would be fine.

1000miles commented 7 years ago

@Svenyo cc @holgerd @anngreenberg

Please find attached the full list of photos (14.733) taken from production AND to be deleted on production before re-import them via the import rake task

At the moment it is just a plain text list which you can open with any text editor.

Date: 2017-06-26 wheelmap_prod_photos_full_list_deletion_20170626.zip

1000miles commented 7 years ago

@TODO

On production server:

Done! 2017-06-26

[x] backup a list of the 14.733 photos that will be deleted
[x] delete 14733 broken photos
[x] execute dry-run for re-import of photos from csv file (14997 records/campaign 2015)

POIS not found: 677
POIS and OSM_ID nil: 996
CREATED: Photos: 12978 
CREATED: Provided Pois: 1422 
EXISTING BEFORE IMPORT: ProvidedPois: 18625, Photos: 69297. 
EXISTING AFTER IMPORT: ProvidedPois: 20047, Photos: 82279. 

# time measurement for import:
real    167m39.244s user    
        7m56.482s sys    
        2m17.689s

IMPORTANT: At the moment not all images are displayed because we need to wait until the delayed_job finalizes the image_processing in the background. As soon as this is done and the pois are ready to be reviewed, we will leave a note here.

1000miles commented 7 years ago

Update, 2017-06-28

@Svenyo

All images from the streetspotr csv 11/2015 have been fully reconstructed by Carrierwave's image_processing delayed_job now (please remember that only photos were imported where the pois still exist). Together with parts of the new streetspotr csv 06/2017 (#630) we imported 17447 images yesterday.

Can you please do a random test and confirm the fix is done?

Please check that all broken images have been removed
Please check that the images of random examples were reconstructed
Please log into the admin dashboard to see the photos

cc @holgerd @anngreenberg


# Examples
+--------+---------+------------+---------------------+---------------------+------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
| id     | user_id | poi_id     | taken_at            | created_at          | image_processing | image_width | image_height | source_url                                                                                                         |
+--------+---------+------------+---------------------+---------------------+------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
|  84838 |   23371 |  485762627 | 2015-11-04 16:48:24 | 2017-06-27 08:52:27 |                0 |        5248 |         3936 | https://download.streetspotr.com/e66da1c8ec5ff1fbef47ffd2d30fd2f4b77c4fe0/48e828a044825600e263f4cfdecc6d41111bd19c |
|  84839 |   23371 |  485762627 | 2015-11-04 16:48:41 | 2017-06-27 08:52:28 |                0 |        5248 |         3936 | https://download.streetspotr.com/330b5e1375bc8069e66f10bb05aadbce6899a1eb/3f0533732d67f4909432fa0da34dfa0ce3428734 |
| ...... |   ......|  ......... | ....................| ....................|                . |       ..... |         .... | .................................................................................................................. |
+--------+---------+------------+---------------------+---------------------+------------------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------+
17447 rows in set (0.10 sec)

Some screen examples:

00000350

00000351

00000352

Svenyo commented 7 years ago

@1000miles @anngreenberg @holgerd In the review I made a random check of the photos as recommended.

I can confirm the broken photos have been deleted.
I can confirm that the preview is fixed and photos have been reconstructed.
I can confirm that I see them in the dashboard.

Remaining question:

A lot of the photos are shown rotated or shown upside down like here: https://wheelmap.org/nodes/-42755444 Is there a way to fix this?

ghost commented 7 years ago

@Svenyo Thank you for checking. Currently there is no way of fixing this. Either the users upload the pictures right or there needs to be a feature implemented that lets users change the direction of the picture by hand.

edit: Or the premium provider has to check the pictures before sending us the data.

Svenyo commented 7 years ago

@anngreenberg Ok, thanks.

ghost commented 7 years ago

Import is done. Therefore closing.

sozialhelden / wheelmap-classic

Photo view broken #167

Some stats:

Conclusions:

On production server:

Done! 2017-06-26

Next

Part I: streetspotr csv 11/2015 - #59

Update, 2017-06-28

Some screen examples: