MySQL Performance: MySQL => Error: table is full [ALTER TABLE deleted the rows]

Error: table is full [ALTER TABLE deleted the rows] [message #1690]

Thu, 23 August 2007 16:00

mysql_beginner
Messages: 5
Registered: May 2007

Junior Member

Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211).

mysql> SHOW TABLE STATUS LIKE 'messages' \G
*************************** 1. row ***************************
           Name: messages
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 5359211
 Avg_row_length: 801
    Data_length: 4294967288
Max_data_length: 4294967295
   Index_length: 45783040
      Data_free: 0
 Auto_increment: 5406252
    Create_time: 2007-04-20 18:26:38
    Update_time: 2007-08-22 09:55:22
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Here is the table structure.

CREATE TABLE `messages` (
  `id` int(11) NOT NULL auto_increment,
  `subject` varchar(255) NOT NULL default '',
  `message` text NOT NULL,
  `attachment_path` varchar(255) default NULL,
  `new` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

On searching I found the this link.
http://dev.mysql.com/doc/refman/4.1/en/full-table.html

According to manual i executed the following query

ALTER TABLE messages MAX_ROWS=20000000000;

I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this.

After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left.

mysql> SHOW TABLE STATUS LIKE 'messages' \G
*************************** 1. row ***************************
           Name: messages
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 170109
 Avg_row_length: 649
    Data_length: 110563036
Max_data_length: 281474976710655
   Index_length: 1961984
      Data_free: 0
 Auto_increment: 5409214
    Create_time: 2007-08-23 10:07:08
    Update_time: 2007-08-23 13:41:57
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=4294967295
        Comment:
1 row in set (0.00 sec)

What could be the reason of this rows deletion?

I am on 32 bit system.
MySQL version: 4.1.18-standard-log
Operating System : CentOS 3.x
Memory: 4 GB DDR

[Updated on: Thu, 23 August 2007 16:04]

Report message to a moderator