Changes between Version 6 and Version 7 of DbPurge


Ignore:
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

Legend:

Unmodified
Added
Removed
Modified
  • DbPurge

    v6 v7  
    128128</workunit_archive>
    129129}}}
     130
     131
     132== Recovering an archive into the DB ==
     133
     134To 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.
     135
     136'''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).
     137
     138Prerequisites:
     139
     140 *  A ''local'' working installation of mysql 6.0 alpha
     141 *  A ''local'' image of the current BOINC database (e.g. restored from a recent dump)
     142
     143Procedure:
     144
     145 1. put the following script, '''boinc_xml_import''', where it can be executed
     146 
     147{{{
     148#!/bin/sh
     149
     150# iterate over all arguments in the command line, filter them through
     151# a fixup, feed them to mysql for import
     152
     153tmpf=/tmp/boinc_import.$$
     154
     155for f in $*; do
     156    echo Processing $f ...
     157
     158    # We need to remove the xml text
     159    zcat -f $f | perl -w -e '
     160        use strict;
     161        my @a;
     162        { local $/=undef;
     163          @a=<>;        }
     164        my $l="@a";
     165        $l=~s|<xml_doc_in>.+?</stderr_out>|    |sg;
     166        print "$l";
     167    ' > $tmpf
     168
     169    mysql YOURPROJECT <<EOF
     170          CREATE TABLE IF NOT EXISTS result_archive LIKE result;
     171          SET AUTOCOMMIT=0;
     172          SET UNIQUE_CHECKS=0;
     173          SET FOREIGN_KEY_CHECKS=0;
     174          LOAD XML CONCURRENT INFILE '$tmpf' IGNORE INTO TABLE result_archive
     175             ROWS IDENTIFIED BY '<result_archive>';
     176          SET FOREIGN_KEY_CHECKS=1;
     177          SET UNIQUE_CHECKS=1;
     178          COMMIT;
     179EOF
     180
     181    echo ... done
     182done
     183}}}
     184
     185
     186 2. Copy the .xml.gz archive files on a local machine, and run the following command (it will take a long time)
     187{{{
     188boinc_xml_import *.xml.gz
     189}}}
     190
     191 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)
     192{{{           
     193INSERT INTO res SELECT * FROM result;
     194}}}
     195
     196
     197
     198
     199