IP guessing on import fails silently, leaving some traces in error log

[juanignaciosl]

Geocoding fails importing this file. Rollbar displays these errors, which seems related:

https://rollbar.com/vizzuality/CartoDB/items/7045/occurrences/5699232468/

Sequel::DatabaseError: PG::Error: ERROR: permission denied for schema public

... 18 non-project frames
19
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/table-geocoder/lib/internal_geocoder.rb", line 79 in create_temp_table
20
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/table-geocoder/lib/internal_geocoder.rb", line 47 in run
21
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/app/models/geocoding.rb", line 120 in run_geocoding!
22
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb", line 250 in block in geocode
23
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/importer_stats.rb", line 33 in block in timing
... 1 non-project frame
25
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/importer_stats.rb", line 31 in timing
26
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb", line 227 in geocode
27
File "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb", line 222 in geocode_ips

https://rollbar.com/vizzuality/CartoDB/items/3153/occurrences/5699232763/ --> failed geocoding reporting

https://rollbar.com/vizzuality/CartoDB/items/4503/occurrences/5699232903/ --> retry

https://rollbar.com/vizzuality/CartoDB/items/7536/occurrences/5699233028/

create_the_geom_from_ip_guessing failed: No such file or directory - /tmp/d20150603-16680-132tmbt

"/usr/lib/ruby/1.9.1/fileutils.rb:696:in `lstat'", 
        "/usr/lib/ruby/1.9.1/fileutils.rb:696:in `remove_entry_secure'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/table-geocoder/lib/internal_geocoder.rb:56:in `ensure in run'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/table-geocoder/lib/internal_geocoder.rb:56:in `run'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb:256:in `rescue in block in geocode'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb:244:in `block in geocode'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/importer_stats.rb:33:in `block in timing'", 
        "/home/ubuntu/www/production.cartodb.com/shared/bundle/ruby/1.9.1/gems/statsd-client-0.0.7/lib/statsd.rb:26:in `timing'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/importer_stats.rb:31:in `timing'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb:227:in `geocode'", 
        "/home/ubuntu/www/production.cartodb.com/releases/20150603142311/services/importer/lib/importer/georeferencer.rb:222:in `geocode_ips'", 

Please note explicit geocoding works, it fails only during importing.

cc @rafatower @Kartones @zenitraM

[Kartones]

uhmm first one smells to organization users + unqualified table name, because of PG::Error: ERROR: permission denied for schema public message, I've seen similar ones because org users have their schema instead of public.

Just a blind guess

[rafatower]

I couldn't reproduce the problem in local with an org user. For some reason the new tables are going into public schema.

But I couldn't reproduce it in staging either, and made sure the tables are going into the user's schema in that case. I double-checked it.

[rafatower]

I also tested with a test org in production and it worked as expected, I'm kind of confused with this 😕

[juanignaciosl]

I've just reproduced the problem (while testing #3859). Users get created this way:

carto_db_development=# select username, database_name, database_schema, organization_id from users order by created_at desc limit 3;
       username       |                      database_name                       |   database_schema    |           organization_id
----------------------+----------------------------------------------------------+----------------------+--------------------------------------
 central-org-04-1     | cartodb_dev_user_e71bc01c-e2a7-4d79-b4ab-49bd2d5d3ef0_db | central-org-04-1     | 0dae1b8d-1643-4061-8843-4afd7c86e3fe
 juanignaciosl6       | cartodb_dev_user_cdfa8aa9-2639-4fbc-840c-9d56b4f44f2e_db | public               |
 central-org-04-admin | cartodb_dev_user_e71bc01c-e2a7-4d79-b4ab-49bd2d5d3ef0_db | central-org-04-admin | 0dae1b8d-1643-4061-8843-4afd7c86e3fe

How have you created local users and org? I've created admin and org with Central, and then the user with organization page (juanignaciosl6 is a normal user, other are org owner and user).

[juanignaciosl]

Just to be clear, I've added "Please note explicit geocoding works, it fails only during importing." to the description.

[rafatower]

I don't trust my local org users cause I created them manually at the time with a rake script. But I do trust the users in staging, created through central.

cartodb_staging=# select username, database_name, database_schema, organization_id from users where username like 'rtorre%';
     username     |                        database_name                         | database_schema  |           organization_id            
------------------+--------------------------------------------------------------+------------------+--------------------------------------
 rtorre-ded04     | cartodb_staging_user_3bcd9cc0-4c7c-4f23-a654-150a18246277_db | public           | 
 rtorre-ded041    | cartodb_staging_user_89165c43-ad51-4c1e-99eb-921776d53120_db | public           | 
 rtorre-ded04org1 | cartodb_staging_user_cb4fdeb6-894f-48ed-ae6a-5883f64dbef3_db | rtorre-ded04org1 | 236922dd-4a7c-4752-af42-f0fe0eed0c13
 rtorre-ded04org2 | cartodb_staging_user_cb4fdeb6-894f-48ed-ae6a-5883f64dbef3_db | rtorre-ded04org2 | 236922dd-4a7c-4752-af42-f0fe0eed0c13

For testing in production I stole an org+user from Michelle.