Project

General

Profile

Task #905

Updated by Aaron Marcuse-Kubitza over 10 years ago

_see #887, #902_ 

 h3. -alternate OS approach- 

 p(. _tried, and problem also occurs on Mac, so using other approaches_ 

 # -fix @`make install`@, which sets up the entire BIEN installation and dependencies- 
 # -test the import on the local testing machine (a Mac), which already has most of the dependencies- 
 # -if that doesn't work, try the other approaches below- 

 h3. -working-backup approach- 

 p(. _restoring to a working backup allows us to successfully run the import, so that we can then test system changes to see what broke things_ 

 # -restore vegbiendev to last working configuration- 
 # -get restored VM to work on VirtualBox- 
 ## -install bootloader- 
 ## -install device drivers- _the Linux VM configuration does not support the VirtualBox ethernet device natively, so it must be configured manually_ 
 # -set up database configuration[1] (the files and data that are not part of a Postgres-level backup)- 
 # -install database contents[1]- 
 there are 2 possible strategies for this: 
 ** -a) fix the bugs in the version of @make install@ available in that backup, and run it to build the database from the flat files. the advantage of this approach is that @make install@ is very fast (~15 min) when restoring only a single datasource in addition to the metadata datasources, while @pg_restore@ would take a day or more or require a custom script to do a selective restore.- _not using this method_ 
 ** -b) find the nearest database backup, restore it, and undo/redo modifications between the time of the VM backup and the time of the database backup. this involves either restoring the full DB (~1 day on vegbiendev, likely slower on an external USB drive) or writing a script to do a selective restore of the data needed for the test import; and then figuring out which modifications were made to the DB before/since the VM backup. (see "README.TXT":http://vegpath.org/fs/config/VirtualBox_VMs/vegbiendev/README.TXT > to set up the VM)- 
 # -test import and verify that it works- 
 # -perform system upgrades and see if import still works (see "README.TXT":http://vegpath.org/fs/config/VirtualBox_VMs/vegbiendev/README.TXT > to install system updates)- _the import does still work[2], so the join columns bug (#902) is fortunately not in a system upgrade._ 
 ** -if if not, selectively install system upgrades to narrow down the problem- _N/A_ problem 
 # -update svn and see if import still works (see "README.TXT":http://vegpath.org/fs/config/VirtualBox_VMs/vegbiendev/README.TXT > to test a different revision)- _it doesn't, so this means the bug is somewhere in our code_ 
 ** -if not, test different revisions to narrow down the problem- _the problem was caused by r12153, so debugging can be limited to the code affected by this commit_ 

 fn1(. the backups from that time did not include the database because of "incorrect information in the Postgres documentation":http://www.postgresql.org/docs/9.3/static/backup-file.html stating that a filesystem-level backup of the live database would not be useful (in fact Postgres usually keeps around enough WAL files[3], and can be configured to keep around even more of them, so that a filesystem-level backup would work perfectly fine in most cases). this incorrect information (essentially a disclaimer) is likely there because the Postgres developers do not want users being upset at them if their filesystem-level backup happens to be unrestorable. 
   

 fn2(. note that there is a slowdown, where the import freezes for 25 min on a query, before resuming: 
 <pre><code class="SQL"> 
 pg_stat_activity snapshot taken: "15:34:51" 

 "query_start" 
 "2014-07-14 14:56:59.482365-07" 
 "backend_start" 
 "2014-07-14 14:43:41.845428-07" 

 query running for 25 min ("0:23:56.119442" in inputs/ARIZ/omoccurrences/logs/test_import.log.sql, but strangely 40 min ("14:56:59".."15:34:51") according to pg_stat_activity) 

 [2] DB query: non-cacheable: 
 Took 0:23:56.119442 sec 
 /*ARIZ.omoccurrences*/ 
 CREATE TEMP TABLE "coordinates_pkeys" AS 
 SELECT 
 "in#41"."*occurrenceID" 
 , "coordinates"."coordinates_id" AS "out.coordinates_id" 
 FROM "in#41" 
 JOIN "coordinates" ON 
 "coordinates"."source_id" = 5 
 AND "coordinates"."latitude_deg" = "in#41"."_nullIf(decimalLatitude).result" 
 AND "coordinates"."longitude_deg" = "in#41"."_nullIf(decimalLongitude).result" 
 AND COALESCE("coordinates"."verbatimlatitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text)) 
 AND COALESCE("coordinates"."verbatimlongitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text)) 
 AND COALESCE("coordinates"."verbatimcoordinates", CAST('\N' AS text)) = COALESCE("in#41"."ARIZ.omoccurrences.verbatimCoordinates", CAST('\N' AS text)) 
 AND COALESCE("coordinates"."footprintgeometry_dwc", CAST('\N' AS text)) = COALESCE("in#41"."ARIZ.omoccurrences.footprintWKT", CAST('\N' AS text)) 
 /* EXPLAIN: 
 Nested Loop    (cost=5333.13..5366.19 rows=1 width=8) 
   ->    Index Scan using coordinates_unique on coordinates    (cost=0.41..18.01 rows=1 width=84) 
         Index Cond: ((source_id = 5) AND (COALESCE(verbatimlatitude, '\N'::text) = '\N'::text) AND (COALESCE(verbatimlongitude, '\N'::text) = '\N'::text)) 
   ->    Bitmap Heap Scan on "in#41"    (cost=5332.72..5348.17 rows=1 width=73) 
         Recheck Cond: ((COALESCE("ARIZ.omoccurrences.verbatimCoordinates", '\N'::text) = COALESCE(coordinates.verbatimcoordinates, '\N'::text)) AND (COALESCE("ARIZ.omoccurrences.footprintWKT", '\N'::text) = COALESCE(coordinates.footprintgeometry_dwc, '\N'::text))) 
         Filter: ((coordinates.latitude_deg = "_nullIf(decimalLatitude).result") AND (coordinates.longitude_deg = "_nullIf(decimalLongitude).result")) 
         ->    Bitmap Index Scan on "in#41_coalesce_coalesce1_coalesce2_coalesce3_idx"    (cost=0.00..5332.72 rows=4 width=0) 
               Index Cond: ((COALESCE("ARIZ.omoccurrences.verbatimCoordinates", '\N'::text) = COALESCE(coordinates.verbatimcoordinates, '\N'::text)) AND (COALESCE("ARIZ.omoccurrences.footprintWKT", '\N'::text) = COALESCE(coordinates.footprintgeometry_dwc, '\N'::text))) 
 */ 
 </code></pre> 

 fn3. except when the database is under very heavy load, such as at the beginning of full-database import 

 h3. tracing approach 

 # attempt to trace where the incorrect columns list is being introduced 
 ** generally time-consuming to trace data through complex code 
 ** narrowing down the affected section of code (using the working-backup approach) would reduce the amount of code that needs to be traced 

 h3. rewriting approach 

 # rewrite the section of code with the bug 
 ** in this case, that code section is the most complex part of our codebase, so this would be potentially very time-consuming 
 ** narrowing down the affected section of code (using the working-backup approach) would reduce the amount of code that needs to be rewritten 

 --- 

 h3. -clean VM approach- 

 p(. _not using this approach because we have a backup from the time of the last successful import, so we don't need to obtain/install dependencies in the versions they had in the last successful import_ 

 # -prepare clean VMs- 
 # -fix @`make install`@, which sets up the entire BIEN installation and dependencies- 
 ** normally, we do not reinstall the DB from scratch, so the bugs in @`make install`@ only become apparent when it is run on a partial installation 
 # install the database from scratch on a clean VM (VirtualBox) 
 ** this would involve adding any missing dependencies to our install scripts 
 # test the import in the clean VM with a sample datasource to see if that reproduces the problem 
 ** if it does, we know it's a bug in Postgres/Ubuntu and can troubleshoot using VM images with different Postgres/Ubuntu versions 
 ** if it doesn't, it's a problem specific to just the vegbiendev VM and we would reset the vegbiendev VM to a clean Ubuntu install and reinstall our dependencies 

 h3. -Postgres rollback approach- 

 p(. _not using this approach because it only works if the problem is with Postgres, but it could also be a problem in the other import code (eg. Python)_ 

 # roll back Postgres to the version it was at in the last successful import 
 ** this may require building Postgres from source, because past _revisions_ of the same numeric version might only be available in version control, not in binary form via apt-get (which numbers packages by numeric version) 
 ** if this isn't possible, it may be necessary to downgrade to Postgres 9.2 (which will unfortunately be missing some features that we now use) 
 # see if this fixes the problem

Back