Re: how to extract data from inaccessible 5.0.21 cluster files
thank you all who answered for your time... i think i'll post all errors that were seemed a bug to mysql team...
as about our problem... let me describe how i solved it, as it may be of help for someone else.
at first we've had not all the data lost as we had backup, it was just quite stale; so i had to extract data from just few tables. also, these tables not contained exotic datatypes, or text fields with very long actual text.
after all this, it happened relatively easy to find out how the data is stored and to create small extraction tool. it's not ready-to-use tool, it can only be the base of something that you may (or may not) use in your specific case.
WARNING - all written below was not got from studying docs or source codes, it is just guess after short overall file analysis - so i don't guarantee anything below is correct or will apply to anyone's specific case.
in conditions described above, records seem to be of fixed size, and are stored in fs/D5 directory.
1) find directory in which your table is stored - you can do a search for tablename in the whole fs/D1 directory. remember dirnames under which files containing your tablename are found.
2) under fs/D5/ find that dirname. in it you'll find several subdirs named Fn. actual data is stored there.
3) then you'll need to take a look at your db schema... each record starts with 00hFFhFFhFFh, then 8 bytes reserved and then your data comes in order it is in schema. if you had no explicit PK in table, it will come as last field. i created php class to extract datatypes that were relevant for me, and then there is usage example for sample table.
class Extractor
{
public $data;
public $offset;
public function getInteger($off)
{
$k = unpack("l", substr($this->data, $this->offset+$off, 4));
return $k[1];
}
public function getTinyint($off)
{
$k = unpack("c", substr($this->data, $this->offset+$off, 2));
return $k[1];
}
public function getVarchar($off, $maxlen = 255, $utf = 0)
{
if ($utf)
$s = unpack("s", substr($this->data, $this->offset+$off, 2));
else
$s = unpack("C", substr($this->data, $this->offset+$off, 1));
$s = $s[1];
if ($s > $maxlen || $s < 0)
throw new Exception("bad data (varchar size exceeds limit)");
return substr($this->data, $this->offset+$off+($utf?2:1), $s);
}
public function getText($off, $maxlen = 256)
{
$s = unpack("l", substr($this->data, $this->offset+$off, 4));
$s = $s[1];
if ($s > $maxlen || $s < 0)
throw new Exception("bad data (text size exceeds limit)");
$q = substr($this->data, $this->offset+$off+8, $s);
return $q;
}
}
class ExtractorMyTable extends Extractor
{
public $recs = array();
public function extractRecord()
{
$a = array();
$a["id"] = $this->getInteger(0xc);
$a["author"] = $this->getInteger(0x10);
/* following offsets seem to come in logical manner depending on varchar size, but i was too lazy to figure out exact formula because found all them just in my data file. in theory it's not too hard to create such an extraction script that will take sql create table statement as input.*/
$a["title"] = $this->getVarchar(0x1c, 256, 1);
$a["content"] = $this->getVarchar(0x320, 2048, 1);
$a["location"] = $this->getTinyint(0x1b24);
return $a;
}
public function processData($data)
{
$this->data = $data;
$this->offset = 0x80; //data always starts with this offset
$datalen = strlen($data);
while ($this->offset + 0x1b28 < $datalen)
{
try
{
$a = $this->extractRecord();
//very simple check if got correct data
if ($a["title"] == "")
throw new Exception("title empty");
}
catch (Exception $e)
{
/*here comes the trick - data not comes by one consecutive block in a file so if we lost record signature this doesn't mean we got end of file. need to try to search for signature further*/
$i = strpos($this->data, "\0\xff\xff\xff", $this->offset+4);
if ($i !== false)
{
$this->offset = $i;
continue;
}
if ($i === false)
break;
}
$this->saveRecord($a);
$this->offset += 0x1b28;
}
}
public function saveRecord($a)
{
// check for dupes
foreach ($this->recs as $r)
{
if ($r["id"] == $a["id"])
return;
}
$this->recs[] = $a;
}
public function process()
{
$k = getcwd();
foreach (glob("*", GLOB_ONLYDIR) as $dir)
{
if ($dir[0] == ".")
continue;
$f = file_get_contents($dir."\\S2.data");
$this->processData($f);
}
}
}
$e = new ExtractorMyTable();
$e->process();
foreach ($e->recs as $a)
{
$f = join(",", array_keys($a));
foreach ($a as $k=>$v)
$a[$k] = '"'.mysql_escape_string($v).'"';
$s = join(",", array_values($a));
print "insert into mytable ({$f}) values ({$s});\n";
}