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

MySQL & MariaDB Other MySQL® Questions
1

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: 4294967288Max_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.
[URL=“http://MySQL :: MySQL 8.0 Reference Manual :: B.3.2.10 The table is full”]http://dev.mysql.com/doc/refman/4.1/en/full-table.html[/URL]

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: 110563036Max_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

2

I see something here regarding others that this happened to (see comments): [URL]http://jeremy.zawodny.com/blog/archives/000796.html[/URL]

Never experienced it myself nor tried it. You might have to backup the table first, issue the command to increase the size and then do a reload. Might be the only “safe” method(?)

3

I have the backup.

One thing I forgot to mention that I executed the ALTER query on MASTER MySQL server (I am in replication environment). But it did not deleted the rows from slave servers. All the slave servers have the correct number or rows.

4

Thanks for the link, I have already read this. This also does not mentioned what should I use the value for AVG_ROW_LENGTH?