[debian-mysql] Bug#665013: mysql-server-5.1: OPTIMIZE TABLE on a big MyIsam-Table with to small myisam_sort_buffer_size leads to table corruption
Harald Hellmuth
harald.hellmuth at googlemail.com
Thu Mar 22 09:57:18 UTC 2012
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
More information about the pkg-mysql-maint
mailing list