Bill Marrs writes:
> >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: <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
Hi!
Can you try our binary version in order and check if a problem reccurs
??
Do note that your glinc is not a stable one !!!!
Also please check that you do not have shutdown command in some script
or cron job.
If you can make a repeatable test case where the above always happens,
but with out binary distribution, please repost your mail with a name
of the file uploaded to our ftp://support.mysql.com/pub/mysql/secret
directory.
Your uploaded file should contains dumps of all tables involved, your
scripts and exact procedure how to repeat the error.
Regards,
Sinisa
+----------------------------------------------------------------------+
| ____ __ _____ _____ ___ == MySQL AB |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sinisa@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+----------------------------------------------------------------------+
