REPAIR/CHECK/BACKUP/RESTORE TABLE handling of compressed tables leads to data loss.

HI,
I've found a problem related to the handling of compressed tables.
Several MySQL commands (or external tools) don't handle these table
correctly. Sometimes, this mishandling leads to data loss. 

In practical terms, this also means that a compressed table with a
damaged or missing index file (due to other causes) can't be recovered.

Any fixes / workarounds available?

Thanks

Best regards

Giuseppe Maxia

P.S. 
Detailed bug report attached below.
I've tested this behaviour with two distributions:
4.0.16 (Debian out-of-the-box)
4.0.18 (binaries compiled by MySQL AB)

From: [email protected]
To: [email protected]
Subject: REPAIR/CHECK/BACKUP/RESTORE TABLE handling of compressed tables
leads to data loss.

>Description:
    REPAIR TABLE tablename USE_FRM, when operating on a compressed
    table, issues an error saying that the table is compressed, but
    after that operation the table is unusable.
    
    There are a few related issues:
    1) BACKUP / RESTORE apparently don't check if a table is compressed,
and the
       restored table is unusable;
    2) myisamchk can't handle a compressed table with a missing or
damaged index file,
       therefore A COMPRESSED TABLE WITHOUT INDEX FILE CAN'T BE
RECOVERED (Is that true?)
    
>How-To-Repeat:
mysql> select length(b) from big; # compressed table
+-----------+
| length(b) |
+-----------+
|   1000000 |
|   1000000 |
|   1000000 |
|   1000000 |
|   1000000 |
|   1000000 |
+-----------+
6 rows in set (0.12 sec)

mysql> repair table big use_frm;
+----------+--------+----------+--------------------------+
| Table    | Op     | Msg_type | Msg_text                 |
+----------+--------+----------+--------------------------+
| test.big | repair | error    | Table 'big' is read only |
+----------+--------+----------+--------------------------+
1 row in set (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select length(b) from big;
Empty set (0.00 sec)

mysql> select count(*) from big;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

>Fix:
    No fixes found.

>Submitter-Id:	<submitter ID>
>Originator:	gmax
>Organization: Stardata.it
>MySQL support: email support (MySQL Support partner)
>Synopsis:	mishandling of compressed tables
>Severity:	critical 
>Priority:	 high 
>Category:	mysql
>Class:		sw-bug 
>Release:	mysql-4.0.16 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.0.16, for pc-linux-gnu
on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		4.0.16-log (also tested with 4.0.18)
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			11 min 44 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 7  Flush tables: 1 
Open tables: 1  Queries per second avg: 0.013
>C compiler:    gcc (GCC) 3.3.2 (Debian)
>C++ compiler:  g++ (GCC) 3.3.2 (Debian)
>Environment:
	<machine, os, target, libraries (multiple lines)>
System: Linux LTGmax 2.4.22 #1 Wed Nov 19 13:13:23 EST 2003 i686
GNU/Linux
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.2/specs
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared
--with-system-zlib --enable-nls --without-included-gettext
--enable-__cxa_atexit --enable-clocale=gnu --enable-debug
--enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc
i486-linux
Thread model: posix
gcc version 3.3.2 (Debian)
Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS='' 
LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx    1 root     root           14 2004-05-16 16:32
/lib/libc.so.5 -> libc.so.5.4.46
-rw-r--r--    1 root     root       565940 2003-09-22 17:45
/lib/libc.so.5.4.46
lrwxrwxrwx    1 root     root           13 2004-05-16 16:32
/lib/libc.so.6 -> libc-2.3.2.so
-rw-r--r--    1 root     root      1243076 2003-11-05 20:17
/lib/libc-2.3.2.so
-rw-r--r--    1 root     root      2537670 2003-11-05 20:18
/usr/lib/libc.a
-rw-r--r--    1 root     root          204 2003-11-05 20:07
/usr/lib/libc.so
lrwxrwxrwx    1 root     root           28 2004-05-16 16:34
/usr/lib/libc-client.so.2002ddebian -> libc-client.so.2002ddebian.1
-rw-r--r--    1 root     root       767184 2003-10-06 08:43
/usr/lib/libc-client.so.2002ddebian.1
Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--datadir=/usr/share'
'--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql'
'--includedir=/usr/include' '--infodir=/usr/share/info'
'--mandir=/usr/share/man' '--enable-shared' '--enable-static'
'--enable-thread-safe-client' '--enable-assembler'
'--enable-local-infile' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock'
'--with-mysqld-user=mysql' '--with-libwrap'
'--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio'
'--with-openssl' '--without-docs' '--without-bench' '--without-readline'
'--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb'


-- 
Giuseppe Maxia
CTO
http://www.StarData.it
       __              __     __
  ___ / /____ ________/ /__ _/ /____ _
 (_-</ __/ _ `/ __/ _  / _ `/ __/ _ `/
/___/\__/\_,_/_/  \_,_/\_,_/\__/\_,_/
       Database is our business



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]