[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: Client still reports table full
From:       "Jacob, Raymond A Jr" <raymond.jacob () navy ! mil>
Date:       2006-06-29 22:07:49
Message-ID: 653C8E7D21FB654997909E77C691053F0758EC8F () NAEAWNYDEX21VA ! nadsusea ! nads ! navy ! mil
[Download RAW message or body]

 
I will try REPAIR TABLE data. data.MYD is 4.1G so I am over the 4GB
limit and growing.
I also tried:
CHECK TABLE data QUICK;
CHECK TABLE data MEDIUM;
CHECK TABLE data EXTENDED;

They all showed OK.

Thank you again,
raymond
-----Original Message-----
From: Dan Buettner [mailto:drbuettner@gmail.com] 
Sent: Thursday, June 29, 2006 17:55
To: Jacob, Raymond A Jr
Cc: mysql@lists.mysql.com
Subject: Re: Client still reports table full

I'm not sure that avg_row_length has a bearing on your problem right now
... the output of show table status you posted earlier shows that you
have:
current data length: 4335220336
maximum data length: 1099511627775
data_free: 0  (oddly)

data_free should be something like 1099511627775 - 4335220336 =
1095176407439

I wonder if the table was marked as being in an error status when it
filled up earlier and now needs a REPAIR operation (see my earlier
post).

Alternatively, Brent's question about a filesystem limit seems
pertinent.  What OS and filesystem are you using?  Though I would think
your error would change from a MySQL 'table is full' error to some kind
of OS-related error...

Dan



On 6/29/06, Jacob, Raymond A Jr <raymond.jacob@navy.mil> wrote:
> If I understand the results from SHOW TABLE STATUS LIKE 'data'; My 
> avg_row_length = 497 Why would descreasing it to 50 have a positive 
> Effect. I would assume I should increase it?
>
> Thank you/Raymond
>
> -----Original Message-----
> From: Brent Baisley [mailto:brenttech@gmail.com]
> Sent: Thursday, June 29, 2006 15:53
> To: Jacob, Raymond A Jr; mysql@lists.mysql.com
> Subject: Re: Client still reports table full
>
> Oops, left out an important part. You should change the Avg_row_length

> also.
>
> ALTER TABLE AVG_ROW_LENGTH = 50
>
> You need to specify an average row length if you have dynamic length 
> fields in the table (blob, text, etc.).
>
> Also, perhaps a silly question which you may have answered earlier, 
> but does you file system allow files larger than 4GB? Sometimes you 
> have to specifically enable that feature in a file system.
>
> If that doesn't work, or you're limited to 4GB files, you may need to 
> switch to using a merge table.
>
> ----- Original Message -----
> From: "Jacob, Raymond A Jr" <raymond.jacob@navy.mil>
> To: <mysql@lists.mysql.com>
> Sent: Thursday, June 29, 2006 1:37 PM
> Subject: Client still reports table full
>
>
> Yesterday:
> I ran the following command:
> ALTER TABLE data max_rows=11000000
>
>  Today:
> The client still reported table is full.
> I rebooted the client and stopped and started the mysql server.
> I still get the table is full error on the data table.
>
> I ran the command:
> \ echo "SHOW TABLE STATUS LIKE 'data';" | mysql -u user -p snortdb \ 
> below is the output.
>
> Name Engine Version Row_format Rows Avg_row_length Data_length 
> Max_data_length Index_length Data_free Auto_increment Create_time 
> Update_time Check_time Collation Checksum Create_options Comment data 
> MyISAM 9 Dynamic 8721565 497 4335220336
> 1099511627775 127599616 0 NULL 2006-06-28 20:54:55
> 2006-06-29 18:02:32 NULL latin1_swedish_ci NULL max_rows=11000000
>
> \\ df shows /var the partition with the database has enoungh room:
> Filesystem    1K-blocks     Used   Avail Capacity  Mounted on
> ....
> /dev/amrd0s1f  27792614 18449326 7119880    72%    /var
> ....

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com


[prev in list] [next in list] [prev in thread] [next in thread]