It seems that if I insert too many rows into a MySQL table (>300,000) it stops functioning after a while. I've yet to figure out exactly what causes this.

I have about 300,000 rows to be inserted into a MySQL table. These are inserted (from a plain text file with INSERT commands) into an SQL table. Seemingly it works fine at first, but after it's been in use for some hours (mostly SELECTs), the table becomes unavailable.

If I do a SHOW KEYS FROM search

Can't open file: 'search.MYI'. (errno: 144)

I did a myisamchk -r on the 'search.MYI' file, when the database was offline, and it was actually repaired. I started the mysqld again, blocking all external access (just to be cautious). Then I removed all the 300,000 that apparently caused this problem (because the table has worked fine before), and now it seems to be OK. However, I tried inserting those 300,000 rows again, and faced the same problem. :/

Anyone can test by creating this table:

http://www.rasterburn.org/~sgt/searchlayout.sql

And then inserting the rows found in this file (~20MB):

http://www.rasterburn.org/~sgt/Deutsch.sql.gz

Thanks for any help we can get on this!

sun-solaris2.9 on sparc 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 4.0.12-log Protocol version 10 Connection mysql.plex.no via TCP/IP TCP port 3306 Uptime: 1 day 22 hours 4 min 8 sec

Threads: 46 Questions: 2535936 Slow queries: 318480 Opens: 772 Flush tables: 1 Open tables: 256 Queries per second avg: 15.291

System: SunOS mysql 5.9 Generic_112233-02 sun4u sparc SUNW,UltraSPARC-IIi-cEngine Architecture: sun4

Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.9/3.1/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1828460 Apr 6 2002 /lib/libc.a lrwxrwxrwx 1 root root 11 Aug 26 2002 /lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 root bin 855484 Apr 6 2002 /lib/libc.so.1 -rw-r--r-- 1 root bin 1828460 Apr 6 2002 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Aug 26 2002 /usr/lib/libc.so -> ./libc.so.1 -rwxr-xr-x 1 root bin 855484 Apr 6 2002 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=complex' '--enable-thread-safe-client' '--with-innodb' '--with-berkeley-db' '--with-embedded-server' '--with-openssl' '--with-vio' '--enable-local-infile' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=gcc'