REPAIR/CHECK/BACKUP/RESTORE TABLE handling of compressed tables
am 26.05.2004 14:29:03 von Giuseppe MaxiaHI,
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: g.maxia@stardata.it
To: mysql@lists.mysql.com
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:
>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:
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/bugs?unsub=gcdmb-bugs@m.gmane.org