Problem with 2GB limit.

Hi,

I'm working with Solaris 8, and MySQL 4.0.17-standard.

I was trying to upload data into a single table database and when it =
reached
2GB it stopped uploading sending the error message:

ERROR 1030 at line 2450: Got error 27 from table handler

After more than 4 hours digging into the FAQs and some of the digests of =
the
mailing list, and a real headacke, I come to ask for help.

I can say that this is not a problem of OS limits, since the file =
containing
the SQL commands is more than 3GB.

When I get into the folder of my database (named superfamily), I see =
that
the file *.MYD is exactly 2GB (results in bytes):

-rw-rw---- 1 mysql mysql 2147483647 Apr 13 22:26 align.MYD
-rw-rw---- 1 mysql mysql 1024 Apr 13 22:26 align.MYI
-rw-rw---- 1 mysql mysql 8616 Apr 13 22:19 align.frm

Then after all what I read, I think this is useful to know that my =
"ibdata1"
file is not too big (results in bytes):

-rw-rw---- 1 mysql mysql 10485760 Apr 13 21:09 ibdata1


Also, that my innodb was created as default:

innodb_data_file_path ibdata1:10M:autoextend




And that none of the logfiles is greater than 6MB (results in bytes):

-rw-rw---- 1 mysql mysql 25088 Jan 29 21:33 =
ib_arch_log_0000000000
-rw-rw---- 1 mysql mysql 5242880 Apr 13 21:25 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jan 29 21:33 ib_logfile1



And if you ask me to send you anything else that would help you to help =
me,
I will send it of course. I am new to MySQL, so please if you ask me to =
run
any command please give me a hint (or better the command itself) so I =
can
run (the exact way) whatever you think would help.

I will appreciate any help/advice, I'm kind of disappointed, I know =
there is
someone that had the same problem or that know how to solve it.

Regards, and thank you guys in advance.

C=E9sar
Nitrogen Fixation Research Center.
RegulonDB staff.




--
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: Problem with 2GB limit.

In the last episode (Apr 13), Cesar Bonavides Martinez said:
> I'm working with Solaris 8, and MySQL 4.0.17-standard.
>
> I was trying to upload data into a single table database and when it
> reached 2GB it stopped uploading sending the error message:
>
> ERROR 1030 at line 2450: Got error 27 from table handler
>
> After more than 4 hours digging into the FAQs and some of the digests
> of the mailing list, and a real headacke, I come to ask for help.
>
> I can say that this is not a problem of OS limits, since the file
> containing the SQL commands is more than 3GB.
>
> When I get into the folder of my database (named superfamily), I see that
> the file *.MYD is exactly 2GB (results in bytes):
>
> -rw-rw---- 1 mysql mysql 2147483647 Apr 13 22:26 align.MYD

Two things to check. First run

SHOW VARIABLES LIKE "large_files_support";

and verify that it's set to ON. If it's OFF, then for some reason your
mysqld wasn't compiled with large file support. If it's ON, run

SHOW TABLE STATUS LIKE "align";

and check the Max_data_length column. If it's 2147483647, then you
simply have to let MySQL know that your table needs to be larger than
2gb. I usually see 4294967295 as a limit here, though, not 2147483647.
To raise the max filesize, run

ALTER TABLE align AVG_ROW_LENGTH=nnnn MAX_ROWS=mmmm

and use reasonable guesses for each. You can use the current average
rowlength value from the previous SHOW TABLE STATUS command. It's not
a hard limit, so it's ok if you guess too low for MAX_ROWS, as long as
MAX_ROWS*AVG_ROW_LENGTH is larger than 2gb.

--
Dan Nelson
dnelson@allantgroup.com

--
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: Problem with 2GB limit.

In the last episode (Apr 14), Cesar Bonavides Martinez said:
> Thank you for your fast answer.
>
> I tried everything you told me, but unfortunately another error
> message came through:
>
> Here you have all what you asked me to do:
>
> mysql> SHOW VARIABLES LIKE "large_files_support";
> +---------------------+-------+
> | Variable_name | Value |
> +---------------------+-------+
> | large_files_support | ON |
> +---------------------+-------+
>
> After running the SHOW TABLE STATUS LIKE "align"; I got:
>
> Name = align
> Type = MyISAM
> Row_format = Dynamic
> Rows = 3398922
> Avg_row_length = 631
> Data_length = 2147625328
> Max_data_length = 4294967295
> Index_length = 1024
> Data_free = 0
> Auto_increment = NULL

Now that's interesting. Max_data_length is set to 4gb, but you're
getting an error at 2gb. Could it be a process resource limit? What
does the "ulimit" command return? You can reset that limit with the
"ulimit unlimited" command.

> Then I executed the last thing you told me. I used those values just
> to make sure I was really over the 2GB, but got the next:
>
> mysql> ALTER TABLE align AVG_ROW_LENGTH=700 MAX_ROWS=4000000;
> ERROR 1030: Got error 127 from table handler

The could be due to resource limits also. Mysql basically got an I/O
error trying to write past the 2gb point, so the table got marked as
crashed. You will have to run "REPAIR TABLE align" before mysql will
let you do anything with it.

$ perror 127
127 = Record-file is crashed

--
Dan Nelson
dnelson@allantgroup.com

--
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: Problem with 2GB limit.

--=-GhmrYH/rXfmwhT0tNRiV
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Wed, 2004-04-14 at 15:20, Dan Nelson wrote:
> Now that's interesting. Max_data_length is set to 4gb, but you're
> getting an error at 2gb. Could it be a process resource limit? What
> does the "ulimit" command return? You can reset that limit with the
> "ulimit unlimited" command.

IIRC, some filesystems have a 2gb limit for file size. This could be the
wall your hitting?

I've used mysql's 'raid' support in a couple instances in the past as a
simple way get beyond this data limit (wont help with indexes > your
filesize limit however). However I'm having trouble getting that working
today!


--=20
Sean Quinlan

--=-GhmrYH/rXfmwhT0tNRiV
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQBAfZJGnv2yYfTgGZsRArisAJ0TAxWGzgw7/pRX9TmH21UBUO25MQCg mkKU
9sFwU98mC1wXJt4zuRpu+O0=
=Sapr
-----END PGP SIGNATURE-----

--=-GhmrYH/rXfmwhT0tNRiV--

RE: Problem with 2GB limit.

Thank you for your fast answer.

I tried everything you told me, but unfortunately another error message =
came
through:

Here you have all what you asked me to do:

mysql> SHOW VARIABLES LIKE "large_files_support";
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| large_files_support | ON |
+---------------------+-------+

After running the SHOW TABLE STATUS LIKE "align"; I got:

Name =3D align
Type =3D MyISAM
Row_format =3D Dynamic
Rows =3D 3398922
Avg_row_length =3D 631
Data_length =3D 2147625328
Max_data_length =3D 4294967295
Index_length =3D 1024
Data_free =3D 0
Auto_increment =3D NULL


Then I executed the last thing you told me. I used those values just to =
make
sure I was really over the 2GB, but got the next:

mysql> ALTER TABLE align AVG_ROW_LENGTH=3D700 MAX_ROWS=3D4000000;
ERROR 1030: Got error 127 from table handler

Am I doing something wrong?, should I try to install another MySQL =
version?

I'm looking forward to get an answer because I just don't know what to
think/do.

Best regards,

C=E9sar


-----Original Message-----
From: Dan Nelson [mailto:dnelson@allantgroup.com]=20
Sent: Mi=E9rcoles, 14 de Abril de 2004 12:51 a.m.
To: Cesar Bonavides Martinez
Cc: mysql@lists.mysql.com
Subject: Re: Problem with 2GB limit.

In the last episode (Apr 13), Cesar Bonavides Martinez said:
> I'm working with Solaris 8, and MySQL 4.0.17-standard.
>=20
> I was trying to upload data into a single table database and when it
> reached 2GB it stopped uploading sending the error message:
>=20
> ERROR 1030 at line 2450: Got error 27 from table handler
>=20
> After more than 4 hours digging into the FAQs and some of the digests
> of the mailing list, and a real headacke, I come to ask for help.
>=20
> I can say that this is not a problem of OS limits, since the file
> containing the SQL commands is more than 3GB.
>=20
> When I get into the folder of my database (named superfamily), I see =
that
> the file *.MYD is exactly 2GB (results in bytes):
>=20
> -rw-rw---- 1 mysql mysql 2147483647 Apr 13 22:26 align.MYD

Two things to check. First run=20

SHOW VARIABLES LIKE "large_files_support";

and verify that it's set to ON. If it's OFF, then for some reason your
mysqld wasn't compiled with large file support. If it's ON, run

SHOW TABLE STATUS LIKE "align";

and check the Max_data_length column. If it's 2147483647, then you
simply have to let MySQL know that your table needs to be larger than
2gb. I usually see 4294967295 as a limit here, though, not 2147483647.
To raise the max filesize, run

ALTER TABLE align AVG_ROW_LENGTH=3Dnnnn MAX_ROWS=3Dmmmm

and use reasonable guesses for each. You can use the current average
rowlength value from the previous SHOW TABLE STATUS command. It's not
a hard limit, so it's ok if you guess too low for MAX_ROWS, as long as
MAX_ROWS*AVG_ROW_LENGTH is larger than 2gb.

--=20
Dan Nelson
dnelson@allantgroup.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: Problem with 2GB limit.

In the last episode (Apr 14), Sean Quinlan said:
> On Wed, 2004-04-14 at 15:20, Dan Nelson wrote:
> > Now that's interesting. Max_data_length is set to 4gb, but you're
> > getting an error at 2gb. Could it be a process resource limit? What
> > does the "ulimit" command return? You can reset that limit with the
> > "ulimit unlimited" command.
>
> IIRC, some filesystems have a 2gb limit for file size. This could be the
> wall your hitting?

He mentioned his input file was 3gb, so I assumed this was not the
problem :) Under Solaris, if the filessytem has the "largefiles" flag
on it when you run "mount", files over 2gb are supported. This is the
default, and if you don't want it, you have to explicitly disable it
with the "nolargefiles" flag in vfstab.

--
Dan Nelson
dnelson@allantgroup.com

--
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: Problem with 2GB limit.

--=-Dq14NQH76lBo+nw4bgF9
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Wed, 2004-04-14 at 16:03, Dan Nelson wrote:
> He mentioned his input file was 3gb, so I assumed this was not the
> problem :)=20

Ah, sorry, just joined the list & must have missed it.

--=20
Sean Quinlan

--=-Dq14NQH76lBo+nw4bgF9
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQBAfZ3Unv2yYfTgGZsRAucjAKCGBA6nrFB0Gb+fyN51QhY7du12SgCg lDrX
d4f4ZqxFWAY0XjXvRu9E4SE=
=ZVnL
-----END PGP SIGNATURE-----

--=-Dq14NQH76lBo+nw4bgF9--

RE: Problem with 2GB limit.

Here is what you suggested:

% ulimit
unlimited

I don't know what is going on, since I do have files greater than 2 GB, =
and
no problem with them...=20

At the beginning I thought it was a problem in which MySQL couldn't =
handle
large input files, so I spited my original file into two parts. One a =
little
bit less than 2 GB and the other with the rest of the data. Here you can =
see
that I do have files greater than 2GB:

% ls -al | grep supfam
-rw-r--r-- 1 supfam supfam 2161802937 Apr 13 22:17 supfam01.sql
-rw-r--r-- 1 supfam supfam 887476820 Apr 13 21:58 supfam02.sql
-rw-r--r-- 1 supfam supfam 3049279757 Apr 13 19:46 =
supfamORIGINAL.sql

So I don't think this is a limit imposed by the filesystem... unless you =
can
guess something different.

I could try using the 'raid' support, and see what happens, but still =
the
problem would persist since I will have to have some indexes and as you =
say
that could bring me to the same problem again.

Thank you for your help... I am looking forward to get another
answer/direction from your side.

Anyway, could you please tell me where can I find a "quick guide to raid
support in MySQL"? =3D)

Best regards,

Cesar


-----Original Message-----
From: Sean Quinlan [mailto:sean@quinlan.org]=20
Sent: Mi=E9rcoles, 14 de Abril de 2004 01:35 p.m.
To: Dan Nelson
Cc: Cesar Bonavides Martinez; mysql@lists.mysql.com
Subject: Re: Problem with 2GB limit.

On Wed, 2004-04-14 at 15:20, Dan Nelson wrote:
> Now that's interesting. Max_data_length is set to 4gb, but you're
> getting an error at 2gb. Could it be a process resource limit? What
> does the "ulimit" command return? You can reset that limit with the
> "ulimit unlimited" command.

IIRC, some filesystems have a 2gb limit for file size. This could be the
wall your hitting?

I've used mysql's 'raid' support in a couple instances in the past as a
simple way get beyond this data limit (wont help with indexes > your
filesize limit however). However I'm having trouble getting that working
today!


--=20
Sean Quinlan



--
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