Description:
I have this little script (1) that builds a table each night.
For quite a while the output shows a normal load data infile (2).
Then, it switches to a state of 'Repair with keycache' (3).
This just started happening with mysql 3.23.32. Until I upgraded
a few days ago it was not a problem. I was using 3.23.8.
The idea is to load the data minus the keys and then add the
keys, since this is much faster. The data file for load data infile
is 1.3 gigs. At about the time the message changes, the mysql file
sizes are -
sq1_invhist_rec.MYD 616840056, sq1_invhist_rec.MYI 15904768.
I also made sure the files did not start out corrupt since I
did a mysqldump -d on them, rm'ed and recreated them. I
never reach the 'pause' line in the script, at least not until I
forcibly (-9) brought down the server. It didn't seem to want to
shutdown any other way.
This all seems to be sporadic. It will work fine for a couple of days
taking a couple of hours to build the tables. Then for the next
couple of days it will bomb out and still be building when I come in
ten hours after it started. So I shutdown mysql to abort the build.
It sounds somewhat similar to a post I saw from David J. Potter where he
writes
This happens while inserting a large number rows at high speed being read
from a text file. It crashes usually around the 1,000,000th inserted row
into a table.
Though other details he mentions are different.
Any ideas?
1. The script.
#!/bin/sh
set -x
cd /data2/sq1tmp
PATH=$PATH:/usr/local/bin
echo delete from sq1_invhist_rec | mysql book
mysqladmin flush-tables
myisamchk --keys-used=0 -rq /data/book/sq1_invhist_rec
mysqladmin flush-tables
echo "load data infile '/data2/sq1tmp/$file' into table $file" | mysql book
echo pausing now
read
2. Line from mysqladmin processlist before the repair message
| 43 | root | localhost | book | Query | 1074 | | load
|data infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec |
0 |
3. Line from mysqladmin processlist once the repair message appears
| 43 | root | localhost | book | Query | 1226 | Repair with keycache | load data
|infile '/data2/sq1tmp/sq1_invhist_rec' into table sq1_invhist_rec |
>How-To-Repeat:
<code/input/activities to reproduce the problem (multiple lines)>
>Fix:
<how to correct or work around the problem, if known (multiple lines)>
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
<organization of PR author (multiple lines)>
MySQL support: [email support] (I believe so. For Powell's Books)
Synopsis: Problem with nightly table build
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
>Release: mysql-3.23.32 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.14 Distrib 3.23.32, for pc-linux-gnu on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.32
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 57 min 49 sec
Threads: 3 Questions: 2765 Slow queries: 0 Opens: 63 Flush tables: 1 Open tables:
57 Queries per second avg: 0.797
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux snowball.burnside.powells.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999
i586 unknown
Architecture: i586
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 17 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 --prefix=/usr/local/mysql --localstatedir=/data
--with-raid
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
