From: Bill Marrs Date: August 21 2000 6:48pm Subject: mysqld getting killed during OPTIMIZE, causes corruption List-Archive: http://lists.mysql.com/mysql/48464 Message-Id: <200008211848.OAA31896@ugtz.com> >Description: While running OPTIMIZE TABLE, server process gets killed mysteriously, this can sometimes cause database corruption. The mysqld error log has this in it: ------------------ 000821 14:22:11 Aborted connection 600 to db: 'TZ' user: 'root' host: `localhost' (Got an error writing communication packets) Number of processes running now: 1 mysqld process hanging, pid 28604 - killed 000821 14:24:43 mysqld restarted /usr/local/libexec/mysqld: ready for connections ------------------ My Perl script got this error back: DBD::mysql::db do failed: Lost connection to MySQL server during query at tz/daily_tz line 975. ...then a number of these as it attempted to OPTIMIZE the tables after that: DBD::mysql::db do failed: MySQL server has gone away at tz/daily_tz line 975. Use of uninitialized value at tz/daily_tz line 976. . . . But, it did recover and eventually started working again, optimizing the last few tables. In this case there was no database corruption. The two previous times this happened, there was. I did do a "FLUSH TABLES" just before doing the OPTIMIZE TABLE; The problem always occurs after it optimizes one table and before it optomizes the next one. The first table is: CREATE TABLE AdLog ( UID int(11) NOT NULL auto_increment, Ad int(11) DEFAULT '0' NOT NULL, User int(11) DEFAULT '0' NOT NULL, Created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, Type enum('impression','click-thru') DEFAULT 'impression' NOT NULL, Host varchar(255) DEFAULT '' NOT NULL, Mode int(11) DEFAULT '1' NOT NULL, PRIMARY KEY (UID), KEY Created (Created), KEY Type (Type) )/*! type=MyISAM */; The next table is: CREATE TABLE Adverts ( UID int(11) NOT NULL auto_increment, Banner varchar(255), Code text, Priority enum('high','default','below') DEFAULT 'high' NOT NULL, Info varchar(255), URL varchar(255), TotalImps int(11), TotalClicks int(11), ImpsSoFar int(11) DEFAULT '0' NOT NULL, ClicksSoFar int(11), Status enum('on','off') DEFAULT 'on' NOT NULL, StartDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, EndDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, Mode int(11) DEFAULT '1' NOT NULL, AltCode text, PRIMARY KEY (UID) )/*! type=MyISAM */; The AdLog table is always getting frequest DELAYED INSERTS done to it, even while the OPTIMIZE TABLE is executing. The Adverts table gets frequent UPDATES done to it. The OPTIMIZE of the AdLog table took 25 seconds, it has 189,525 rows. The OPTIMIZE of the Adverts failes (with the "Lost connection" error). I have delayed-key-writes turned on for all tables. This is how I run mysqld: $bindir/safe_mysqld \ --user=$mysql_daemon_user --datadir=$datadir --pid-file=$pid_file --log=$log_file --log-slow-queries --delay-key-write-for-all-tables --skip-networking & Let me know if there's something I can do to help track this down. I suspect this will keep happening most morning when the script runs, if I let it. The corruption seems to be fixable with myisamchk -r. I suspect it's just that the keys are messed up (the errors I saw seemed to indicate that, but I'm not sure). When I did get errors in the mysqld log file after the corruption occurred, this is what they looked like: 000821 7:18:16 read_next: Got error 134 when reading table Trades 000821 7:18:16 read_next: Got error 134 when reading table Games >How-To-Repeat: It has now happened to me 3 times, but have not been able to come up with a repeatable test case. Several attempts and guess as to what's causing it have not yielded any problems. This happened Saturday morning, then again Monday morning (today). Then just now, monday afternoon when I ran the script by had. >Fix: I don't need to run OPTIMIZE on my tables, it just seemed like a good idea. >Submitter-Id: >Originator: Bill Marrs >Organization: Game Trading Zone (http://gametz.com) >MySQL support: licence (I think, though I'm not sure) >Synopsis: mysqld getting killed during OPTIMIZE, causes corruption >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.22-beta (Source distribution) >Server: /usr/local/bin/mysqladmin Ver 8.8 Distrib 3.23.22-beta, for pc-linux-gnu on i686 TCX Datakonsult AB, by Monty Server version 3.23.22-beta-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 3 min 41 sec Threads: 19 Questions: 7903 Slow queries: 1 Opens: 91 Flush tables: 1 Open tables: 49 Queries per second avg: 35.760 >Environment: System: Linux ugtz.com 2.2.16 #4 SMP Thu Jun 22 17:12:22 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/pgcc/lib/gcc-lib/i686-pc-linux-gnu/pgcc-2.95.2/specs gcc version pgcc-2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 29 1999 /lib/libc.so.6 -> libc-2.1.1.so -rwxr-xr-x 1 root root 4016683 Apr 16 1999 /lib/libc-2.1.1.so -rw-r--r-- 1 root root 19533408 Apr 16 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Apr 16 1999 /usr/lib/libc.so Configure command: ./configure --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-gnu-ld --with-extra-charsets=none Perl: This is perl, version 5.005_03 built for i686-linux