Task #905
Updated by Aaron Marcuse-Kubitza almost 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 not, selectively install system upgrades to narrow down the 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], files, and can be configured to keep around even more of them, so that a filesystem-level backup would work perfectly fine in most cases). fine). 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