MySQL bug report: Large number of rows fails in MySQL 4.0.12 (.MYI problem - errno: 144)

>Description:
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.

>How-To-Repeat:
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

>Fix:
Thanks for any help we can get on this!

>Submitter-Id: PlexBug
>Originator: Torbjorn Kristoffersen
>Organization:
Plex Communications AS
>MySQL support: none
>Synopsis: Large number of rows fails and corrupts the table (the index
file)
>Severity: critical
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.12 (Source distribution)
>Server: /home/mysql/bin/mysqladmin Ver 8.40 Distrib 4.0.12, for
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
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
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'





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: MySQL bug report: Large number of rows fails in MySQL 4.0.12 (.MYI problem - errno: 144)

Torbj=F8rn Kristoffersen writes:
> >Description:
> 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 w=
hat
> causes this.
>=20
> >How-To-Repeat:
> 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 SQ=
L
> table. Seemingly it works fine at first, but after it's been in use
> for some hours (mostly SELECTs), the table becomes unavailable.
> =09
> =09If I do a SHOW KEYS FROM search
>=20
> =09Can't open file: 'search.MYI'. (errno: 144)
>=20
> I did a myisamchk -r on the 'search.MYI' file, when the database
> was offline, and it was actually repaired. I started the mysqld agai=
n,
> blocking all external access (just to be cautious). Then I removed al=
l=20
> the 300,000 that apparently caused this problem (because the table ha=
s=20
> worked fine before), and now it seems to be OK. However, I tried=20
> inserting those 300,000 rows again, and faced the same problem. :/
>=20
> Anyone can test by creating this table:
>=20
> http://www.rasterburn.org/~sgt/searchlayout.sql
>=20
> And then inserting the rows found in this file (~20MB):
>=20
> http://www.rasterburn.org/~sgt/Deutsch.sql.gz
>=20

Hi!

I tested your bug report on 4.0.12 and all worked just
fine. Therefore, this is not a repeatable test case.

Here is the output:

bash$ mysql sinisa < Deutsch.sql
bash$ mysql sinisa -e "show keys from search"
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collati=
on | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+
| search | 0 | PRIMARY | 1 | id | A =
| 258575 | NULL | NULL | | BTREE | |
| search | 1 | q | 1 | q | A =
| NULL | NULL | NULL | YES | BTREE | |
| search | 1 | kid | 1 | kid | A =
| NULL | NULL | NULL | YES | BTREE | |
| search | 1 | tittel | 1 | tittel | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 2 | innhold | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 3 | url | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 4 | q | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+


--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org

Re: MySQL bug report: Large number of rows fails in MySQL 4.0.12 (.MYI problem - errno: 144)

Torbj=F8rn Kristoffersen writes:
> >Description:
> 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 w=
hat
> causes this.
>=20
> >How-To-Repeat:
> 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 SQ=
L
> table. Seemingly it works fine at first, but after it's been in use
> for some hours (mostly SELECTs), the table becomes unavailable.
> =09
> =09If I do a SHOW KEYS FROM search
>=20
> =09Can't open file: 'search.MYI'. (errno: 144)
>=20
> I did a myisamchk -r on the 'search.MYI' file, when the database
> was offline, and it was actually repaired. I started the mysqld agai=
n,
> blocking all external access (just to be cautious). Then I removed al=
l=20
> the 300,000 that apparently caused this problem (because the table ha=
s=20
> worked fine before), and now it seems to be OK. However, I tried=20
> inserting those 300,000 rows again, and faced the same problem. :/
>=20
> Anyone can test by creating this table:
>=20
> http://www.rasterburn.org/~sgt/searchlayout.sql
>=20
> And then inserting the rows found in this file (~20MB):
>=20
> http://www.rasterburn.org/~sgt/Deutsch.sql.gz
>=20

Hi!

I tested your bug report on 4.0.12 and all worked just
fine. Therefore, this is not a repeatable test case.

Here is the output:

bash$ mysql sinisa < Deutsch.sql
bash$ mysql sinisa -e "show keys from search"
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collati=
on | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+
| search | 0 | PRIMARY | 1 | id | A =
| 258575 | NULL | NULL | | BTREE | |
| search | 1 | q | 1 | q | A =
| NULL | NULL | NULL | YES | BTREE | |
| search | 1 | kid | 1 | kid | A =
| NULL | NULL | NULL | YES | BTREE | |
| search | 1 | tittel | 1 | tittel | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 2 | innhold | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 3 | url | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
| search | 1 | tittel | 4 | q | A =
| NULL | NULL | NULL | YES | FULLTEXT | |
+--------+------------+----------+--------------+----------- --+--------=
---+-------------+----------+--------+------+------------+-- -------+


--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic >
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org