Max File Size Mysql

Anon

I have about 21 million entries in a table but when i try to add more it give me this error
insert into others values("adf@kldjf.com");
ERROR 1030: Got error 27 from table handler

root@sparky:/usr/local/mysql/bin# ./perror 27
Error code 27: File too large

How do i go about fixing this problem?

Any help would be helpful!

Anon

Do you have a filesystem/OS that supports files larger than 2GB? Have you looked at the directory that holds your table to see if there's a file that's close to 2 GB?

Anon

yah i have a file that is 800 MB i am running slackware 8.1 Linux(2.2.19) should support up to 4G, here is dir listing of /var

drwx------ 2 mysql mysql 4096 Aug 2 22:55 email
drwx------ 2 mysql mysql 4096 Aug 1 22:14 master
drwx------ 2 mysql mysql 4096 Jul 23 21:57 mysql
-rw-rw---- 1 mysql mysql 86 Jul 24 05:14 sparky-bin.001
-rw-rw---- 1 mysql mysql 73 Jul 24 05:30 sparky-bin.002
-rw-rw---- 1 mysql mysql 58819442 Jul 26 22:01 sparky-bin.003
-rw-rw---- 1 mysql mysql 86 Jul 26 22:13 sparky-bin.004
-rw-rw---- 1 mysql mysql 627043648 Aug 1 23:53 sparky-bin.005
-rw-rw---- 1 mysql mysql 1073743945 Aug 2 07:56 sparky-bin.006
-rw-rw---- 1 mysql mysql 459447974 Aug 2 21:41 sparky-bin.007
-rw-rw---- 1 mysql mysql 203 Aug 2 22:55 sparky-bin.008
-rw-rw---- 1 mysql mysql 682763788 Aug 3 02:50 sparky-bin.009
-rw-rw---- 1 mysql mysql 153 Aug 2 23:10 sparky-bin.index
-rw-r--r-- 1 mysql root 1959 Aug 2 23:10 sparky.err
-rw-rw---- 1 mysql mysql 3 Aug 2 23:10 sparky.pid
drwx------ 2 mysql mysql 4096 Jul 23 21:57 test
drwx------ 2 mysql mysql 4096 Jul 25 01:02 users

as you can see the biggest file is 680 MB.

Anon

Solving my own problems again =) here is the solution!!!

my linux only supports 2gb files, so

configure mysql using the --with-raid option.

on you large talbes, use this

create table foo (
name text not null) RAID_TYPE=STRIPED RAID_CHUNKS=8;

replace the 8 with how much space you need
i am store about 80million entries, i found that i got about 21million with 2gb so each chunk represents 2gb, 8 x 2 = 168 GB total table size, woohoo.

opido

joe wrote:

Solving my own problems again =) here is the solution!!!

my linux only supports 2gb files, so

configure mysql using the --with-raid option.

on you large talbes, use this

create table foo (
name text not null) RAID_TYPE=STRIPED RAID_CHUNKS=8;

replace the 8 with how much space you need
i am store about 80million entries, i found that i got about 21million with 2gb so each chunk represents 2gb, 8 x 2 = 168 GB total table size, woohoo.

I assume you are using an array of disks to do this?

I recently ran into the same problem (2GB file limit), and was curious what your config for the data partition.

-Stephen VanDyke

Anon

Nope, it just breaks the data file into smaller chunks. But all the new distro's of linux are 2.2.x so you get ext3 wich will do larger > 2GB files.

opido

phil wrote:

Nope, it just breaks the data file into smaller chunks. But all the new distro's of linux are 2.2.x so you get ext3 wich will do larger > 2GB files.

Our sysadmins use debian on a 2.4.x kernel and for some reason the limit is still 2GB.

Thanks for the info on this. I did my homework and found another great bit of info on what to do with my tables after they get huge (but no longer needed for updating):
http://www.mysql.com/doc/m/y/myisampack.html