| Bug #8283 | OPTIMIZE TABLE causes data loss | ||
|---|---|---|---|
| Submitted: | 3 Feb 2005 13:21 | Modified: | 20 Oct 2006 17:24 |
| Reporter: | Torsten Kasch | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.9 | OS: | Solaris (Solaris 9 (sparc)) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | corruption, myisam | ||
[5 Feb 2005 16:55]
Jorge del Conde
mysql> DELETE FROM Sequence WHERE _id < 8; Query OK, 7 rows affected (0.00 sec) mysql> OPTIMIZE TABLE Sequence; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.Sequence | optimize | status | OK | +---------------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> CHECK TABLE Sequence EXTENDED; +---------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+----------+ | test.Sequence | check | status | OK | +---------------+-------+----------+----------+ 1 row in set (0.00 sec) mysql> SELECT _id FROM Sequence; +-----+ | _id | +-----+ | 8 | | 9 | +-----+ 2 rows in set (0.00 sec)
[7 Feb 2005 12:20]
Torsten Kasch
We've done some further testing here and currently it seems that the bug is only triggered, if "myisam_repair_threads" is configured > 2. I must've overlooked the statement in the docs that this feature is alpha-quality (otherwise I would not have switched it on... ;-) Anyway, setting the value to "1" makes the reported test case pass flawlessly. Can it be that there is some kind of problem in the "multithreaded repair code" or did I do something wrong?
[7 Feb 2005 12:21]
Torsten Kasch
Sorry, my previous comment should have read ... if "myisam_repair_threads" is configured >= 2 ...
[7 Feb 2005 21:26]
Jorge del Conde
Thanks for your bug report. I was able to verify this bug under Linux after I set myisam_repair_threads to 2. DROP TABLE IF EXISTS `Sequence`; CREATE TABLE `Sequence` ( `_id` int(11) NOT NULL default '0', `url` text, `email` text, `description` text, `loverlap` int(11) default NULL, `roverlap` int(11) default NULL, `lneighbor_id` int(11) default NULL, `rneighbor_id` int(11) default NULL, `length_` int(11) default NULL, `sequence` mediumtext, `name` text, `_obj_class` text NOT NULL, PRIMARY KEY (`_id`), UNIQUE KEY `sequence_name_index` (`name`(50)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `Sequence` -- /*!40000 ALTER TABLE `Sequence` DISABLE KEYS */; LOCK TABLES `Sequence` WRITE; INSERT INTO `Sequence` VALUES (1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''),(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''),(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''),(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''),(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''),(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''),(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''),(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''),(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); UNLOCK TABLES; /*!40000 ALTER TABLE `Sequence` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; mysql> show variables like 'myisam_repair%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | myisam_repair_threads | 2 | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> select _id FROM Sequence; +-----+ | _id | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +-----+ 9 rows in set (0.00 sec) mysql> DELETE FROM Sequence WHERE _id < 8; Query OK, 7 rows affected (0.00 sec) mysql> OPTIMIZE TABLE Sequence; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.Sequence | optimize | status | OK | +---------------+----------+----------+----------+ 1 row in set (0.00 sec) mysql> CHECK TABLE Sequence EXTENDED; +---------------+-------+----------+---------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+---------------------------------------------------------------+ | test.Sequence | check | error | Found key at page 2048 that points to record outside datafile | | test.Sequence | check | error | Corrupt | +---------------+-------+----------+---------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT _id FROM Sequence; ERROR 1016 (HY000): Can't open file: 'Sequence.MYI' (errno: 145)
[28 Jun 2006 15:59]
Calvin Sun
mark bug#20051 as dup of this one.
[5 Aug 2006 10:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/10067 ChangeSet@1.2521, 2006-08-05 12:41:11+02:00, istruewing@chilla.local +9 -0 Bug#8283 - OPTIMIZE TABLE causes data loss [Not to be pushed. There is an issue with myisamchk -p left. I will fix it later. But please review the new design.] OPTIMIZE TABLE with myisam_repair_threads > 1 performs a non-quick parallel repair. This means that it does not only rebuild all indexes, but also the data file. Non-quick parallel repair works so that there is one thread per index. The first of the threads rebuilds also the new data file. The problem was that all threads shared the read io cache on the old data file. If there were holes (deleted records) in the table, the first thread skipped them, writing only contiguous, non-deleted records to the new data file. Then it built the new index so that its entries pointed to the correct record positions. But the other threads didn't know the new record positions, but put the positions from the old data file into the index. The new design is so that there is a shared io cache which is filled by the first thread and read by the other threads. Now they know the new record positions.
[14 Sep 2006 16:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11951 ChangeSet@1.2514, 2006-09-14 18:11:59+02:00, istruewing@chilla.local +10 -0 Bug#8283 - OPTIMIZE TABLE causes data loss OPTIMIZE TABLE with myisam_repair_threads > 1 performs a non-quick parallel repair. This means that it does not only rebuild all indexes, but also the data file. Non-quick parallel repair works so that there is one thread per index. The first of the threads rebuilds also the new data file. The problem was that all threads shared the read io cache on the old data file. If there were holes (deleted records) in the table, the first thread skipped them, writing only contiguous, non-deleted records to the new data file. Then it built the new index so that its entries pointed to the correct record positions. But the other threads didn't know the new record positions, but put the positions from the old data file into the index. The new design is so that there is a shared io cache which is filled by the first thread (the data file writer) with the new contiguous records and read by the other threads. Now they know the new record positions. Another problem was that for the parallel repair of compressed tables a common bit_buff and rec_buff was used. I changed it so that thread specific buffers are used for parallel repair. A similar problem existed for checksum calculation. I made this multi-thread safe too.
[9 Oct 2006 17:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13346 ChangeSet@1.2514, 2006-10-09 19:26:55+02:00, istruewing@chilla.local +10 -0 Bug#8283 - OPTIMIZE TABLE causes data loss OPTIMIZE TABLE with myisam_repair_threads > 1 performs a non-quick parallel repair. This means that it does not only rebuild all indexes, but also the data file. Non-quick parallel repair works so that there is one thread per index. The first of the threads rebuilds also the new data file. The problem was that all threads shared the read io cache on the old data file. If there were holes (deleted records) in the table, the first thread skipped them, writing only contiguous, non-deleted records to the new data file. Then it built the new index so that its entries pointed to the correct record positions. But the other threads didn't know the new record positions, but put the positions from the old data file into the index. The new design is so that there is a shared io cache which is filled by the first thread (the data file writer) with the new contiguous records and read by the other threads. Now they know the new record positions. Another problem was that for the parallel repair of compressed tables a common bit_buff and rec_buff was used. I changed it so that thread specific buffers are used for parallel repair. A similar problem existed for checksum calculation. I made this multi-thread safe too.
[9 Oct 2006 20:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13356 ChangeSet@1.2296, 2006-10-09 22:16:22+02:00, istruewing@chilla.local +1 -0 Bug#8283 - OPTIMIZE TABLE causes data loss After merge fix. MyISAM version 10.
[10 Oct 2006 18:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13421 ChangeSet@1.2346, 2006-10-10 20:01:39+02:00, istruewing@chilla.local +1 -0 Bug#8283 - OPTIMIZE TABLE causes data loss After merge fix. Renamed 'info' -> 'sort_param'.
[12 Oct 2006 7:13]
Ingo Strüwing
Pushed to 5.1-engines, 5.0-engines, and 4.1-engines.
[20 Oct 2006 9:08]
Ingo Strüwing
Pushed to 5.1.13, 5.0.27, and 4.1.22.
[20 Oct 2006 17:24]
Paul DuBois
Noted in 4.1.22, 5.0.27, 5.1.13 changelogs. OPTIMIZE TABLE with myisam_repair_threads > 1 could result in table corruption.
[25 Oct 2006 16:40]
Paul DuBois
The 5.0.x fix is in 5.0.30.
Description: After upgrading our database server from MySQL 3.23.52 to 4.1.9, we are experiencing severe problems with the OPTIMIZE TABLE command which results in corrupted tables or the loss of all data in the affected table. Although it is not always reproducible, but possibly related: We have had several "OPTIMIZE threads" hanging (could not be KILLed) thus blocking affected tables until we restarted the mysqld. Systems tested: - SunFire V880 (8 CPU, 16 GB RAM, dedicated StorEdge 3510 Disk system) (production system) - SunFire V880 (8 CPU, 32 GB RAM, internal FC-AL disks) (test system) After experiencing the problem on the above production system (MySQL compiled from sources, 64bit with Sun ONE Studio 8 ), we set up the test system with the official Solaris 9 binaries (both 32 and 64bit). How to repeat: Script started on Thu Feb 03 13:16:27 2005 tk@marvin mysql -p -h marvin seq_test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 to server version: 4.1.9-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP TABLE IF EXISTS `Sequence`; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE `Sequence` ( -> `_id` int(11) NOT NULL default '0', -> `url` text, -> `email` text, -> `description` text, -> `loverlap` int(11) default NULL, -> `roverlap` int(11) default NULL, -> `lneighbor_id` int(11) default NULL, -> `rneighbor_id` int(11) default NULL, -> `length_` int(11) default NULL, -> `sequence` mediumtext, -> `name` text, -> `_obj_class` text NOT NULL, -> PRIMARY KEY (`_id`), -> UNIQUE KEY `sequence_name_index` (`name`(50)) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (1, REPEAT('gcat', 1098311), 'sample1'); Query OK, 1 row affected (0.10 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (2, REPEAT('gcat', 1098311), 'sample2'); Query OK, 1 row affected (0.10 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (3, REPEAT('gcat', 1098311), 'sample3'); Query OK, 1 row affected (0.10 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (4, REPEAT('gcat', 1098311), 'sample4'); Query OK, 1 row affected (0.09 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (5, REPEAT('gcat', 1098311), 'sample5'); Query OK, 1 row affected (0.10 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (6, REPEAT('gcat', 1098311), 'sample6'); Query OK, 1 row affected (0.08 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (7, REPEAT('gcat', 1098311), 'sample7'); Query OK, 1 row affected (0.10 sec) mysql> INSERT INTO Sequence -> ( _id, Sequence, name ) VALUES (9, REPEAT('gcat', 1098311), 'sample9'); Query OK, 1 row affected (0.10 sec) mysql> SELECT _id FROM Sequence; +-----+ | _id | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 9 | +-----+ 8 rows in set (0.00 sec) mysql> DELETE FROM Sequence WHERE _id < 8; Query OK, 7 rows affected (0.19 sec) mysql> OPTIMIZE TABLE Sequence; +-------------------+----------+----------+------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------+----------+----------+------------------------------------+ | seq_test.Sequence | optimize | warning | Number of rows changed from 1 to 0 | | seq_test.Sequence | optimize | status | OK | +-------------------+----------+----------+------------------------------------+ 2 rows in set (0.03 sec) mysql> CHECK TABLE Sequence EXTENDED; +-------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------+-------+----------+----------+ | seq_test.Sequence | check | status | OK | +-------------------+-------+----------+----------+ 1 row in set (0.00 sec) mysql> SELECT _id FROM Sequence; Empty set (0.00 sec) mysql> SHOW VARIABLES; +---------------------------------+---------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------------------------------------------------+ | back_log | 50 | | basedir | /export/scratch/mysql-standard-4.1.9-sun-solaris2.9-sparc/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /export/scratch/mysql-standard-4.1.9-sun-solaris2.9-sparc/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /usr/local/mysql/data/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | NO | | have_bdb | NO | | have_compress | YES | | have_crypt | YES | | have_csv | NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_ndbcluster | NO | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 20971520 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 2147483648 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir | /export/scratch/mysql/ | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/scratch/mysql/ | | innodb_log_archive | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/scratch/mysql/ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 402653184 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /export/scratch/mysql-standard-4.1.9-sun-solaris2.9-sparc/share/mysql/english/ | | large_files_support | ON | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | ON | | log_error | | | log_slave_updates | OFF | | log_slow_queries | ON | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 33553408 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 8192 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 67107840 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 10737418240 | | myisam_max_sort_file_size | 10737418240 | | myisam_recover_options | DEFAULT | | myisam_repair_threads | 2 | | myisam_sort_buffer_size | 134217728 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | ON | | open_files_limit | 40970 | | pid_file | /usr/local/mysql/data/marvin.pid | | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 2093056 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 4711 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slow_launch_time | 2 | | socket | /tmp/mysql.sock | | sort_buffer_size | 8388600 | | sql_mode | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | MET | | table_cache | 2048 | | table_type | MyISAM | | thread_cache_size | 8 | | thread_concurrency | 16 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.9-standard-log | | version_comment | Official MySQL-standard binary | | version_compile_machine | sparc | | version_compile_os | sun-solaris2.9 | | wait_timeout | 28800 | +---------------------------------+---------------------------------------------------------------------------------+ 177 rows in set (0.00 sec) mysql> ^DBye tk@marvin script done on Thu Feb 03 13:22:18 2005