*bump* Upgrade wiped out table? What to do?

Sorry for *bump*-ing..
I sent this mail to the list a few days ago.
Still no ideas?

Hi,

I recently upgraded a MySQL installation to 4.1.21.
I'm running Gentoo linux and the MySQL upgrade occurred
as a part of a system-wide software upgrade (emerge -uDv world).
Unfortunately I don't know which was my previous version installed.

Anyway, after the upgrade one of my MySQL tables is empty; a
simple SELECT * FROM table returns "Empty set (0.00 sec)".
All other tables and databases seems to have survived the upgrade.

I've tried to repair the table with myisamchk, these are my results:

myisamchk -r -q table
- check record delete-chain
- recovering (with sort) MyISAM-table 'table'
Data records: 0
- Fixing index 1
Delete link points outside datafile at 0
MyISAM-table 'table' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option
or by not using the --quick (-q) flag

myisamchk -o table
- recovering (with keycache) MyISAM-table 'table'
Data records: 0
Delete link points outside datafile at 0
Found block that points outside data file at 128
Found block that points outside data file at 252
Found block that points outside data file at 420
Found block that points outside data file at 580
Found block that points outside data file at 764
Found block that points outside data file at 944
Found block that points outside data file at 1708
Found block that points outside data file at 2220
Found block that points outside data file at 3124
Found block that points outside data file at 4500
Found block that points outside data file at 4676
Found block that points outside data file at 4844
Found block that points outside data file at 5220
Found block that points outside data file at 5400
Found block that points outside data file at 5772
Found block that points outside data file at 6376
Found block that points outside data file at 6712
Found block that points outside data file at 7052
Found block that points outside data file at 8120
Found block that points outside data file at 8296
Found block that points outside data file at 8676
Found block that points outside data file at 8976

After the last run the .MYD-file, which was ~8kb before, has the size
of zero bytes; luckily I made a backup before I attempted to repair the
table.

I also tried to recreate the table in another database and replaced the
original .frm and .MYI files with nes ones, after which I rerun the
procedure with the same result.

The table contained mostly text, and by simply opening the
..MYD-file in a text editor I can see some of the text (maybe all)
which was entered into the table.

Anyone has any idea on what to try next?
The data obviously is there, but still myisamchk failes.

Thank you in advance,
Lukas Garberg


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

Re: *bump* Upgrade wiped out table? What to do?

On 12/12/06, Lukas Garberg wrote:
> Sorry for *bump*-ing..
> I sent this mail to the list a few days ago.
> Still no ideas?
>
> Hi,
>
> I recently upgraded a MySQL installation to 4.1.21.
> I'm running Gentoo linux and the MySQL upgrade occurred
> as a part of a system-wide software upgrade (emerge -uDv world).
> Unfortunately I don't know which was my previous version installed.
>

I use Gentoo as well, and never had any problems with MySQL,
specifically because when the build ends, it warns you that you
already have data in the directory, and its YOUR job to change it.
Anyway, MySQL, Apache, PHP or any other service should be checked
before any upgrade, running emerge -uDv world carelessly may put you
in trouble, as I have discovered the hard way.

I have removed all my service packages from the world file, in order
to upgrade them by hand when needed, and attend to the upgrade so I
have no surprises.

> Anyway, after the upgrade one of my MySQL tables is empty; a
> simple SELECT * FROM table returns "Empty set (0.00 sec)".
> All other tables and databases seems to have survived the upgrade.

I see no reason for a MySQL version to corrupt data from previous
versions, but it may happen if you had, lets say, 3.23 (very OLD). If
all your tables and databases are fine, most likely the upgrade is not
the cause of the problem, maybe a filesystem corruption, or anything
like that.

>
> I've tried to repair the table with myisamchk, these are my results:
>
> myisamchk -r -q table
> - check record delete-chain
> - recovering (with sort) MyISAM-table 'table'
> Data records: 0
> - Fixing index 1
> Delete link points outside datafile at 0
> MyISAM-table 'table' is not fixed because of errors
> Try fixing it by using the --safe-recover (-o), the --force (-f) option
> or by not using the --quick (-q) flag
>
> myisamchk -o table
> - recovering (with keycache) MyISAM-table 'table'
> Data records: 0
> Delete link points outside datafile at 0
> Found block that points outside data file at 128
> Found block that points outside data file at 252
> Found block that points outside data file at 420
> Found block that points outside data file at 580
> Found block that points outside data file at 764
> Found block that points outside data file at 944
> Found block that points outside data file at 1708
> Found block that points outside data file at 2220
> Found block that points outside data file at 3124
> Found block that points outside data file at 4500
> Found block that points outside data file at 4676
> Found block that points outside data file at 4844
> Found block that points outside data file at 5220
> Found block that points outside data file at 5400
> Found block that points outside data file at 5772
> Found block that points outside data file at 6376
> Found block that points outside data file at 6712
> Found block that points outside data file at 7052
> Found block that points outside data file at 8120
> Found block that points outside data file at 8296
> Found block that points outside data file at 8676
> Found block that points outside data file at 8976
>
> After the last run the .MYD-file, which was ~8kb before, has the size
> of zero bytes; luckily I made a backup before I attempted to repair the
> table.

What's this table? 8kb is, well, pretty much like nothing... Anyway,
always backup your data... (and I mean BEFORE any problems)

>
> I also tried to recreate the table in another database and replaced the
> original .frm and .MYI files with nes ones, after which I rerun the
> procedure with the same result.
>
> The table contained mostly text, and by simply opening the
> .MYD-file in a text editor I can see some of the text (maybe all)
> which was entered into the table.
>
> Anyone has any idea on what to try next?
> The data obviously is there, but still myisamchk failes.

It seems your data file is broken, that may be caused by so many
variables that is hard to tell. Anyway, if you don't have a backup,
its hard to get the data easily. You can always install the old
version you had and try copying the file direcly to get the data back,
but I guess the corruption is in the file, so, there's really not much
to do...

Check your filesystem integrity ASAP.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

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