Missing database file names

After a recent hard drive kerfluffle and the results of fsck, I'm left
with a slew of jumbled database files. The file command can tell me
the file types, like so:

#15901614: MySQL table definition file Version 10
#15901615: MySQL MISAM compressed data file Version 1
#15901617: MySQL table definition file Version 10
#15901618: MySQL MISAM compressed data file Version 1
#15901620: MySQL table definition file Version 10
#15901621: MySQL MISAM compressed data file Version 1

These files are, I'm pretty sure, from my mythtv database. I'm
rebuilding my box and it would be nice if I can keep the mythtv
database the same as before, but how can I tell which file is which?

Any ideas?

Thanks much.

--
Jesse F. Hughes
Me: "Quincy, there's only *one* Truth, isn't there?"
Quincy (age 4): "Yeah, and it's *mine*."
-- A lesson in postmodernism goes awry.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

In the last episode (Jun 02), Jesse F. Hughes said:
> After a recent hard drive kerfluffle and the results of fsck, I'm left
> with a slew of jumbled database files. The file command can tell me the
> file types, like so:
>
> #15901614: MySQL table definition file Version 10
> #15901615: MySQL MISAM compressed data file Version 1
> #15901617: MySQL table definition file Version 10
> #15901618: MySQL MISAM compressed data file Version 1
> #15901620: MySQL table definition file Version 10
> #15901621: MySQL MISAM compressed data file Version 1
>
> These files are, I'm pretty sure, from my mythtv database. I'm
> rebuilding my box and it would be nice if I can keep the mythtv
> database the same as before, but how can I tell which file is which?

"table definition" files are the .frm files; "MISAM compressed data" files
are .MYI files. Unfortunately, you're missing the .MYD files in that list,
which don't have a header (sometimes 'file' thinks they're dbase format).

You may be in luck, though. The filenames are the inode numbers of the
original files, and it looks like they're in sequential order, so the tables
were probably created all at once, one after the other. File #15901616 (if
it exists) is probably the .MYD file that corresponds to #15901614 (.frm)
and #15901615 (.MYI).

The numerical order of the files should match the order the mythtv setup
script creates its tables. If they aren't in order for some reason, then
you can rename the groups of files into "a.frm", "a.MYI", "a.MYD", "b.frm",
"b.MYI", "b.MYD", etc, then run "show create table a" etc to determine the
table layout and match them up to known mythtv tables.

--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

Dan Nelson writes:

> In the last episode (Jun 02), Jesse F. Hughes said:
>> After a recent hard drive kerfluffle and the results of fsck, I'm left
>> with a slew of jumbled database files. The file command can tell me the
>> file types, like so:
>>
>> #15901614: MySQL table definition file Version 10
>> #15901615: MySQL MISAM compressed data file Version 1
>> #15901617: MySQL table definition file Version 10
>> #15901618: MySQL MISAM compressed data file Version 1
>> #15901620: MySQL table definition file Version 10
>> #15901621: MySQL MISAM compressed data file Version 1
>>
>> These files are, I'm pretty sure, from my mythtv database. I'm
>> rebuilding my box and it would be nice if I can keep the mythtv
>> database the same as before, but how can I tell which file is which?
>
> "table definition" files are the .frm files; "MISAM compressed data" files
> are .MYI files. Unfortunately, you're missing the .MYD files in that list,
> which don't have a header (sometimes 'file' thinks they're dbase format).
>
> You may be in luck, though. The filenames are the inode numbers of the
> original files, and it looks like they're in sequential order, so the tables
> were probably created all at once, one after the other. File #15901616 (if
> it exists) is probably the .MYD file that corresponds to #15901614 (.frm)
> and #15901615 (.MYI).
>
> The numerical order of the files should match the order the mythtv setup
> script creates its tables. If they aren't in order for some reason, then
> you can rename the groups of files into "a.frm", "a.MYI", "a.MYD", "b.frm",
> "b.MYI", "b.MYD", etc, then run "show create table a" etc to determine the
> table layout and match them up to known mythtv tables.

Brilliant! I'll look into it as soon as I can.

And I'll be sure to complain if it doesn't work.

Thanks.

--
"[I want to] stand at the pinnacle of human achievement with no one
else in all of history even close, no human being having faced what I
have--and survived. Because when all is said and done, make no
mistake, the simple truth is, I am better." --James S. Harris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

Dan Nelson writes:

> In the last episode (Jun 02), Jesse F. Hughes said:
>> After a recent hard drive kerfluffle and the results of fsck, I'm left
>> with a slew of jumbled database files. The file command can tell me the
>> file types, like so:
>>
>> #15901614: MySQL table definition file Version 10
>> #15901615: MySQL MISAM compressed data file Version 1
>> #15901617: MySQL table definition file Version 10
>> #15901618: MySQL MISAM compressed data file Version 1
>> #15901620: MySQL table definition file Version 10
>> #15901621: MySQL MISAM compressed data file Version 1
>>
>> These files are, I'm pretty sure, from my mythtv database. I'm
>> rebuilding my box and it would be nice if I can keep the mythtv
>> database the same as before, but how can I tell which file is which?
>
> "table definition" files are the .frm files; "MISAM compressed data" files
> are .MYI files. Unfortunately, you're missing the .MYD files in that list,
> which don't have a header (sometimes 'file' thinks they're dbase format).
>
> You may be in luck, though. The filenames are the inode numbers of the
> original files, and it looks like they're in sequential order, so the tables
> were probably created all at once, one after the other. File #15901616 (if
> it exists) is probably the .MYD file that corresponds to #15901614 (.frm)
> and #15901615 (.MYI).
>
> The numerical order of the files should match the order the mythtv setup
> script creates its tables. If they aren't in order for some reason, then
> you can rename the groups of files into "a.frm", "a.MYI", "a.MYD", "b.frm",
> "b.MYI", "b.MYD", etc, then run "show create table a" etc to determine the
> table layout and match them up to known mythtv tables.

Ouch. I've found the time to look into the problem a little more
closely.

I do have a lot of DBase3 files listed, with varying number of
records. Unfortunately, they *aren't* in sequential order with the
..MYI and .frm files. I probably used a MySQL dump file to create
them, so I suppose that the previous files were unlinked and new ones
created.

Except that the dbase files I see have *lower* inode numbers than the
..MYIs and .frms!

Is there some way to match the .MYI to its corresponding .MYD? I have
only 33 to 47 .MYD files (judging from the output of file and their
contiguity), but 74 .MYI files.

Thanks.
--
Jesse F. Hughes

"This post marks the end of an era in the world of mathematics."
-- James S. Harris and the demise of Galois theory

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

Is it possible to build a .MYI file "from scratch"?

I have found a file that I believe is recorded.MYD. I have the .frm
file as well, but I don't think that recorded.MYI survived the
file system event.

--
"No feeling sympathy for mathematicians who start marching with signs
like 'Will work for food' in the future... I will not show mercy
going forward. I was trained as a soldier in the United States Army
after all... We play to win." --James Harris, feel his wrath!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

2010/6/4 Jesse F. Hughes :
> Is it possible to build a .MYI file "from scratch"?
>
> I have found a file that I believe is recorded.MYD.  I have the .frm
> file as well, but I don't think that recorded.MYI survived the
> file system event.

Yes, the mysql utility "myisamchk" and the REPAIR command should be
able to regenerate an MYI file from a .MYD and .frm files.
After all, MDI file only contains the disk version of the indexes.
Beware of possible issues if using different server version, though.

--=20
Jaime Crespo
MySQL & Java Instructor
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Missing database file names

(I emailed this to Martin and Jaime rather than to the discussion
group, so I'm re-sending it.)

Martin Gainty writes:

> Jesse ..please keep us apprised on your progress..we would like to
> know how fubar the db can be before it becomes 'unrecoverable'

Well, I'm not having much luck so far.

I have a file that I'm certain is recorded.frm. I also have a file
that I'm almost certain is recorded.MYD. I stuck an arbitrary .MYI
file in as recorded.MYI, in the hopes that it would be overwritten
with good data.

Unfortunately, here's the result:

root@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
- recovering (with keycache) MyISAM-table 'recorded.MYI'
Data records: 0
Key 1 - Found wrong stored record at 0
Found link that points at 19054226382932 (outside data file) at 36708
Found link that points at 13556668244051 (outside data file) at 44344
Found link that points at 13673551504478720 (outside data file) at 59948
Found link that points at 5996055336546156 (outside data file) at 59956
Found link that points at 17916077434656 (outside data file) at 64864
Found block with too small length at 69120; Skipped

and so on.

The result is an empty database, of course.

Now, someone mentioned that I need to be sure I have the same versions
of MySQL. I thought that I had the same versions, since I thought
that I had the same versions of Ubuntu on my working and non-working
machines. Unfortunately, it looks like the non-working machine had
5.0.67-0ubuntu6, while the working machine has
5.1.30really5.0.75-0ubuntu10.3 (referring here to Ubuntu package
versions, but I think the mysql versions are 5.0.67 and 5.0.75,
resp.).

Perhaps that's the problem? Or perhaps I'm wrong about the .MYD file
and it isn't really recorded.MYD?

--
"So how do you go on? [...] How will you keep moving for the next few
weeks or months until you are known for what you are, the story
becomes huge all over the world, and you have reporters at your
schools asking you, why?" -- Another JSH mystery

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

"Jesse F. Hughes" writes:

> root@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI
> Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
> Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
> Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
> Warning: option 'sort_buffer_size': unsigned value
18446744073709551615 adjusted to 4294967295

Could these problems be that the dead machine was 64 bit and the
working machine 32 bit?

I'm rebuilding the dead machine now. When it's got mysql loaded on
it, I'll try to run myisamchk on a 64-bit machine.

--
Jesse F. Hughes
"Well, if I can get [my proof of FLT accepted], then I hopefully get a
book deal down the road, and maybe I get to go on 'Oprah'."
James Harris, on the rewards of mathematical endeavours.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Missing database file names

"Jesse F. Hughes" writes:

> "Jesse F. Hughes" writes:
>
>> root@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI
>> Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
>> Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
>> Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
>> Warning: option 'sort_buffer_size': unsigned value
> 18446744073709551615 adjusted to 4294967295
>
> Could these problems be that the dead machine was 64 bit and the
> working machine 32 bit?
>
> I'm rebuilding the dead machine now. When it's got mysql loaded on
> it, I'll try to run myisamchk on a 64-bit machine.

Well, I'm sorry to say that I did not successfully rebuild my recorded
(or any other) database index. Again, the issue could be that I'm
using a different mysql version, but I think I'll have to throw in the
towel and start the mythtv database from scratch.

Consequently, I'll have to throw away a terabyte worth of recordings,
too.

Oh well. Losing the recordings isn't that big a deal. Losing the
database of recording rules and list of shows previously recorded
matters a bit more to me.

I appreciate all the help anyway. Thanks, guys.

--
"Quincy, would you rather do epistemology or conceptual analysis?"
"You know what? I'd rather fight on an aircraft carrier.... And Mama
and Baba (Papa) would fight on an aircraft carrier, too."
-- Quincy P. Hughes, age 3 1/2

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org