Changes between Version 6 and Version 7 of DbPurge

Timestamp:

Nov 2, 2009, 2:41:09 AM (15 years ago)

Author:

tonig

Comment:

Added instruction on how to recover the archive in a db

DbPurge

@@ -128 +128 @@
 </workunit_archive>
 }}}
+
+
+== Recovering an archive into the DB ==
+
+To extract old statistics, one has to recover the xml archive files (records  get periodically removed from the ''results'' table  by '''db_purge''').  This section explains a quick hack to recover the xml archive into a database. For now, only the '''result_archive''' is recreated, without xml data.
+
+'''Note.''' This procedure relies on the {{{LOAD XML INTO}}} MySQL command, which was introduced (about) in MySQL version 6.0 alpha. The 6.0 version was then removed from the MySQL roadmap, and must therefore be resurrected from sameplace on the net. Try e.g. looking for ''mysql-6.0.11-alpha-linux-x86_64-glibc23.tar.gz''. It might be found e.g. at ftp://ftp.fu-berlin.de/unix/databases/mysql/Downloads/MySQL-6.0/ . MySQL 6.0 should '''not''' be used in the production DB, but only on a sandbox machine (called ''local'' henceforth).
+
+Prerequisites:
+
+ *  A ''local'' working installation of mysql 6.0 alpha
+ *  A ''local'' image of the current BOINC database (e.g. restored from a recent dump)
+
+Procedure:
+
+ 1. put the following script, '''boinc_xml_import''', where it can be executed
+ 
+{{{
+#!/bin/sh
+
+# iterate over all arguments in the command line, filter them through
+# a fixup, feed them to mysql for import
+
+tmpf=/tmp/boinc_import.$$
+
+for f in $*; do
+    echo Processing $f ...
+
+    # We need to remove the xml text
+    zcat -f $f | perl -w -e '
+        use strict;
+        my @a;
+        { local $/=undef;
+          @a=<>;        }
+        my $l="@a";
+        $l=~s|<xml_doc_in>.+?</stderr_out>|    |sg;
+        print "$l";
+    ' > $tmpf
+
+    mysql YOURPROJECT <<EOF
+          CREATE TABLE IF NOT EXISTS result_archive LIKE result;
+          SET AUTOCOMMIT=0;
+          SET UNIQUE_CHECKS=0;
+          SET FOREIGN_KEY_CHECKS=0;
+          LOAD XML CONCURRENT INFILE '$tmpf' IGNORE INTO TABLE result_archive 
+             ROWS IDENTIFIED BY '<result_archive>';
+          SET FOREIGN_KEY_CHECKS=1;
+          SET UNIQUE_CHECKS=1;
+          COMMIT;
+EOF
+
+    echo ... done
+done
+}}}
+
+
+ 2. Copy the .xml.gz archive files on a local machine, and run the following command (it will take a long time)
+{{{
+boinc_xml_import *.xml.gz
+}}}
+
+ 3. At the end of the run, a  '''result_archive''' table will be reconstructed on the server. To add the most recent results, contained in the '''result''' table,  do (it will also take a while)
+{{{            
+INSERT INTO res SELECT * FROM result;
+}}}
+
+
+
+
+