Your message dated Thu, 05 Jul 2012 15:53:12 +0000
with message-id <[email protected]>
and subject line Bug#680362: Removed package(s) from unstable
has caused the Debian Bug report #665013,
regarding mysql-server-5.1: OPTIMIZE TABLE on a big MyIsam-Table with to small
myisam_sort_buffer_size leads to table corruption
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
665013: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=665013
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
Package: mysql-server-5.1
Version: 5.1.61-0+squeeze1
Severity: important
Running OPTIMIZE TABLE command on a big MyIsam-Table (~600000 rows, Size ~
90GByte)
with to small myisam_sort_buffer_size (8M) leads to table corruption
After experienced an crashed table after using mysql's OPTIMZE TABLE I've try to
trigger this bug again with a newly created Table,. Here's a script of the
session:
###################################################################
mysql> use db1
DROP TABLE IF EXISTS `table1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `table1` (
`field1` varchar(100) NOT NULL DEFAULT '',
`field2` longtext CHARACTER SET utf8,
`field3` date DEFAULT NULL,
`field4` date DEFAULT NULL,
`field5` varchar(100) DEFAULT NULL,
`field6` varchar(100) DEFAULT NULL,
`field7` char(1) NOT NULL DEFAULT 'X',
`field8` varchar(10) DEFAULT NULL,
`field9` varchar(50) DEFAULT NULL,
PRIMARY KEY (`field1`),
KEY `idx1` (`field1`),
KEY `idx2` (`field3`),
KEY `idx3` (`field4`),
KEY `idx4` (`filed8`),
KEY `idx5` (`field5`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 715207 |
+----------+
mysql> show table status like 'table1' \G
*************************** 1. row ***************************
Name: table1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 715207
Avg_row_length: 146752
Data_length: 104958158804
Max_data_length: 281474976710655
Index_length: 49564672
Data_free: 0
Auto_increment: NULL
Create_time: 2012-03-20 09:53:21
Update_time: 2012-03-20 11:28:03
Check_time: 2012-03-20 12:55:21
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> delete from table1 limit 100000;
Query OK, 100000 rows affected (4 min 6.26 sec)
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 615207 |
+----------+
1 row in set (0.00 sec)
mysql> show table status like 'table1' \G
*************************** 1. row ***************************
Name: table1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 615207
Avg_row_length: 149394
Data_length: 104958158804
Max_data_length: 281474976710655
Index_length: 49564672
Data_free: 13049594756
Auto_increment: NULL
Create_time: 2012-03-20 09:53:21
Update_time: 2012-03-20 15:30:35
Check_time: 2012-03-20 12:55:21
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> optimize local table table1;
+------------+----------+----------+--------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+--------------------------------------+
| db1.table1 | optimize | error | myisam_sort_buffer_size is too small |
| db1.table1 | optimize | status | OK |
+------------+----------+----------+--------------------------------------+
# Now, very sad, the row count is 0 :-(
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> show table status like 'table1' \G
*************************** 1. row ***************************
Name: table1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 104958158804
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2012-03-20 09:53:21
Update_time: 2012-03-20 15:30:35
Check_time: 2012-03-20 15:34:39
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# But Optimize thought, the table was up to date (whatever that means)
mysql> optimize local table table1;
+------------+----------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+-----------------------------+
| db1.table1 | optimize | status | Table is already up to date |
+------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)
# CHECK found the table is corrupt
mysql> check local table table1;
+------------+-------+----------+--------------------------------------------------+
| Table | Op | Msg_type | Msg_text
|
+------------+-------+----------+--------------------------------------------------+
| db1.table1 | check | error | Record-count is not ok; is 615207 Should
be: 0 |
| db1.table1 | check | warning | Found 13049594756 deleted space. Should be
0 |
| db1.table1 | check | warning | Found 100000 deleted blocks Should be:
0 |
| db1.table1 | check | warning | Found 715207 key parts. Should be: 0
|
| db1.table1 | check | error | Corrupt
|
+------------+-------+----------+--------------------------------------------------+
5 rows in set (15 min 46.42 sec)
# Luckily it could be repaired:
mysql> repair table table1;
+------------+--------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------------+--------+----------+----------------------+
| db1.table1 | repair | error | myisam_sort_buffer_size is too small |
| db1.table1 | repair | warning | Number of rows changed from 0 to 615207 |
| db1.table1 | repair | status | OK |
+------------+--------+----------+-----------------------------------------+
3 rows in set (36 min 23.57 sec)
# Table is now optimized and seems to be intact:
mysql> show table status like 'table1' \G
*************************** 1. row ***************************
Name: table1
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 615207
Avg_row_length: 149394
Data_length: 91908564048
Max_data_length: 281474976710655
Index_length: 65316864
Data_free: 0
Auto_increment: NULL
Create_time: 2012-03-20 09:53:21
Update_time: 2012-03-20 16:29:20
Check_time: 2012-03-20 16:29:29
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select count(*) from table1;
+----------+
| count(*) |
+----------+
| 615207 |
+----------+
1 row in set (0.00 sec)
mysql>quit
Bye
# MyIsamSortBufferSize was 8 MByte (Default value)
mysql> show variables like 'myisam_sort_buffer_size';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| myisam_sort_buffer_size | 8388608 |
+-------------------------+---------+
###################################################################
Sincerly
Harald Hellmuth
-- System Information:
Debian Release: 6.0.4
APT prefers stable
APT policy: (500, 'stable')
Architecture: amd64 (x86_64)
Kernel: Linux 2.6.32.46-2squeeze2-hs-grsec (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Versions of packages mysql-server-5.1 depends on:
ii adduser 3.112+nmu2 add and remove users and groups
ii debconf [de 1.5.36.1 Debian configuration management sy
ii libc6 2.11.3-2 Embedded GNU C Library: Shared lib
ii libdbi-perl 1.605-1 Perl5 database interface by Tim Bu
ii libgcc1 1:4.4.5-8 GCC support library
ii libmysqlcli 5.1.61-0+squeeze1 MySQL database client library
ii libstdc++6 4.4.5-8 The GNU Standard C++ Library v3
ii lsb-base 3.2-23.2squeeze1 Linux Standard Base 3.2 init scrip
ii mysql-clien 5.1.61-0+squeeze1 MySQL database client binaries
ii mysql-commo 5.1.61-0+squeeze1 MySQL database common files, e.g.
ii mysql-serve 5.1.61-0+squeeze1 MySQL database server binaries
ii passwd 1:4.1.4.2+svn3283-2+squeeze1 change and administer password and
ii perl 5.10.1-17squeeze3 Larry Wall's Practical Extraction
ii psmisc 22.6-1 Utilities that use the proc filesy
ii zlib1g 1:1.2.3.4.dfsg-3 compression library - runtime
Versions of packages mysql-server-5.1 recommends:
ii bsd-mailx [mailx] 8.1.2-0.20071201cvs-3 A simple mail user agent
ii libhtml-template-p 2.9-1 HTML::Template : A module for usin
ii mailx 1:20071201-3 Transitional package for mailx ren
Versions of packages mysql-server-5.1 suggests:
pn tinyca <none> (no description available)
-- Configuration Files:
/etc/mysql/debian-start changed [not included]
-- debconf information excluded
--- End Message ---
--- Begin Message ---
Version: 5.1.62-1+rm
Dear submitter,
as the package mysql-5.1 has just been removed from the Debian archive
unstable we hereby close the associated bug reports. We are sorry
that we couldn't deal with your issue properly.
For details on the removal, please see http://bugs.debian.org/680362
The version of this package that was in Debian prior to this removal
can still be found using http://snapshot.debian.org/.
This message was generated automatically; if you believe that there is
a problem with it please contact the archive administrators by mailing
[email protected].
Debian distribution maintenance software
pp.
Alexander Reichle-Schmehl (the ftpmaster behind the curtain)
--- End Message ---
