| Bug #17329 | Tables get corrupted (errors 127 and 134) | ||
|---|---|---|---|
| Submitted: | 12 Feb 2006 9:10 | Modified: | 14 Sep 2014 19:29 |
| Reporter: | Martin Spasov | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.18, 5.5.38 | OS: | Linux (Linux x86_64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | corruption, myisam | ||
[12 Feb 2006 9:10]
Martin Spasov
[12 Feb 2006 9:21]
Valeriy Kravchuk
Thank you for a problem report. Please, describe your upgrade procedure. Did you perform mysqldump in 4.x.y and restore in 5.0.18, as reccommended, or just installed new binaries over old data?
[12 Feb 2006 9:27]
Martin Spasov
Full dump, than restore. After initial corruption we stopped all clients and did full repair from console (repair table...). I got more corrupted tables, so I repaired again, dumped (from 5.0.18) and reimported the database. Than we found that the concurrent insert is causing troubles.
[12 Feb 2006 9:31]
Martin Spasov
This is rescent mysqld error log, 2-3 corruptions/24h.
Attachment: mysql-err.log (text/x-log), 4.55 KiB.
[12 Feb 2006 10:06]
Valeriy Kravchuk
Please, send the SHOW CREATE TABLE results for tables that are usually corrupted. Your my.cnf file content will be also useful.
[12 Feb 2006 10:22]
Martin Spasov
SHOW TABLE STATUS
| tr_country | MyISAM | 10 | Dynamic | 1425449 | 29 | 42741064 | 281474976710655 | 32163840 | 40 | NULL | 2006-02-08 17:13:01 | 2006-02-12 11:53:59 | 2006-02-12 00:46:46 | cp1251_general_ci | NULL | | |
SHOW CREATE TABLE tr_country
CREATE TABLE `tr_country` (
`trid` int(11) NOT NULL default '0',
`countryid` int(11) NOT NULL default '0',
`completed_by` varchar(255) NOT NULL default '',
`times_completed` int(10) unsigned NOT NULL default '0',
`ls` int(10) unsigned NOT NULL default '0',
`sd` int(10) unsigned NOT NULL default '0',
`visible` enum('yes','no') NOT NULL default 'yes',
`downloaded` bigint(20) unsigned NOT NULL default '0',
`uploaded` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`trid`,`countryid`),
KEY `visible` (`visible`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
cat /etc/mysql/my.cnf |egrep -v ^#
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /old-sql/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
default-character-set=cp1251
old-passwords = 1
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache = 1024
table_cache = 8096
query_cache_limit = 1048576
query_cache_size = 26214400
query_cache_type = 1
set-variable = wait-timeout=15
set-variable = concurrent_insert=0
set-variable = delay-key-write=OFF
ft_min_word_len=3
set-variable=max_connections=4096
set-variable=wait_timeout=15
set-variable=max_tmp_tables=512
myisam_sort_buffer_size = 64M
skip-innodb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
ft_min_word_len = 3
key_buffer = 16M
daemon started with:
# ./bin/safe_mysqld --user=mysql --open-files=65535 --log-error=/var/log/mysql/mysql.err --skip-name-resolve --myisam-recover
[15 Feb 2006 12:50]
Martin Spasov
Ok, after some tweaking i have found that the problem is in the delayed_key_write=all variable. I have disabled this and everything seems to be fine (no more corrupted tables). Here is what I did: use tr; Database changed mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired >Meanwhile the error log is empty - no errors reported. I have few (100-200) threads that are using this table. I have repaired the table few seconds later: mysql> repair table tr_country; +---------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+--------+----------+----------+ | tr.tr_country | repair | status | OK | +---------------------------+--------+----------+----------+ 1 row in set (7.73 sec) >It seems everything is ok. Now let's alter again (10 seconds after the repair said ok I hit this): mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired >Seems that repair does not work ?! Is this the issue here? Let's try again with extended options appended: mysql> repair table tr_country extended; +---------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+--------+----------+----------+ | tr.tr_country | repair | status | OK | +---------------------------+--------+----------+----------+ 1 row in set (14.37 sec) mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired >I'm getting nervous.. Trying to solve the problem blindly... mysql> flush tables; Query OK, 0 rows affected (1.92 sec) mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired mysql> repair table tr_country; +---------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+--------+----------+----------+ | tr.tr_country | repair | status | OK | +---------------------------+--------+----------+----------+ 1 row in set (5.01 sec) mysql> ALTER TABLE `tr_country` DROP completed_by; ERROR 1194 (HY000): Table 'tr_country' is marked as crashed and should be repaired Doesn't work. So let's try to disable the delay_key_write options: mysql> set global delay_key_write=off; Query OK, 0 rows affected (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.29 sec) mysql> repair table tr_country; +---------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+--------+----------+----------+ | tr.tr_country | repair | status | OK | +---------------------------+--------+----------+----------+ 1 row in set (5.03 sec) mysql> ALTER TABLE `tr_country` DROP completed_by; Query OK, 1435759 rows affected (19.24 sec) Records: 1435759 Duplicates: 0 Warnings: 0 It's done. I left delayed_key_write off and the server is working ok. I have reported a bug (overflow) which may be connected to this one - 17328
[1 Mar 2006 15:08]
Valeriy Kravchuk
Sorry, but you had delay_key_write=off by default according to your my.cnf content, or I misinterpreted it? Moreover, you had not set DELAY_KEY_WRITE for that particular table... Anyway, can you try to repeat with a copy of that table and onl one session, trying to perform ALTER. Please, do and inform about the results.
[1 Mar 2006 15:21]
Martin Spasov
We have enabled this on the fly (by seting the global variable to "all", then flushing tables and threads), that's why this was not in the config file. I'll try to reproduce the problem described in the last post on a non-production machine.
[2 Mar 2006 11:07]
Valeriy Kravchuk
Please, do and inform about the results.
[2 Apr 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[6 Apr 2006 13:41]
Vygintas Krasauskas
I have found this bug page while I was searching for a problem solution: several big (1-5 GB) MyISAM tables get often corrupted on my MySQL 5 (5.0.19) server. All of them have DELAY_KEY_WRITE=1. It mostly happens when some big/heavy query is running, eg. data transformation/loading using INSERT INTO ..... SELECT FROM. Sometimes (rarely) the whole server crashes but mostly a table gets broken after such operation is done. The error message mostly says "Incorrect key file for table ...." so no data is lost, but it takes long until index is repaired. Few hours ago one index file got corrupted after I have started an UPDATE query and then have killed it after it took 2-3 minutes. Now I will try to disable DELAY_KEY_WRITE for all tables.
[18 Mar 2010 14:21]
Jerry Champlin
Yesterday we experienced this issue in a production environment on 5.0.33 and a test environment on 5.1.36. Both databases reside on servers running CentOS 5.2. 5.1.36 error: 100316 21:15:09 [ERROR] /opt/mysql/libexec/mysqld: Table './log_loader_caches/malog' is marked as crashed and should be repaired 5.0.33 error: 100314 18:02:39 [ERROR] /opt/mysql-5.0.33/libexec/mysqld: Table './data_loader_caches/madata_18' is marked as crashed and last (automatic?) repair failed Both tables are MyISAM tables. The 5.0.33 table is a merge table. After altering the tables to set DELAY_KEY_WRITE=0 the crashes have stopped. I will post an update if the problem happens with DELAY_KEY_WRITE turned off.
[5 Aug 2014 16:33]
Michael Brunnbauer
Had index crashes with 5.5.38 on heavily used tables with DELAY_KEY_WRITE=1 and full text indices. It seems that setting DELAY_KEY_WRITE=0 stopped the errors.
[14 Aug 2014 19:29]
Sveta Smirnova
Michael, thank you for the feedback. We were not able to repeat this issue internally. Please upload to our FTP server files of problematic table compressed. Please also check if you parallel queries to the same table when doing alter.
[15 Sep 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
