>>>>> "Arun" == Arun Bhalla <abhalla@stripped> writes:
Arun> Tonu Samuel writes:
>> Arun Bhalla wrote:
>> >=20
>> > Hi, I'm using 3.23.5 on a Solaris 2.6 Sun 450. Apparently I had been
>> > running 3.23.4 without bigfiles support because my 4GB table wasn't
>> > completely accessible, and when I was running ALTER TABLE, the table
>> > rebuild would croak at 2GB with "Table is full" errors.
>>
>> Have you read the manual?:
>>
>> http://mysql.mirror.ok.ee/Manual_chapter/manual_Problems.html#Full_table
Arun> Should that apply to ALTER TABLE, too? See the section 18.19 on ALTER
Arun> TABLE. It doesn't explicitly talk about temporary files. Regardless,
Arun> my tmp_table_size limit is set to 12M, and the table was already at 2G,
Arun> so that should be irrelevant. The server was plainly using the disk
Arun> for rebuilding the table, not virtual memory.
Arun> I tried ALTER TABLE again with 3.23.5, and it had a similar problem --
Arun> the table wouldn't grow beyond 2GB on the rebuild process, but the statement
Arun> wouldn't terminate either. Again I had to use myisamchk to rebuild the
Arun> MyISAM file.
Hi!
Do you think you can do this one more time, but this time
do an
myisamchk -dvv table_name
before and after you run myisamchk
How did you create the table? Did you use the:
MAX_ROWS=# option to tell MySQL that you want to have support for
really big tables?
Here is a test that shows that ALTER TABLE should be able to handle
this properly:
mysql> create table t1 (a char(255), b char(255)) MAX_ROWS=10000000;
Query OK, 0 rows affected (0.08 sec)
mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |
Index_length | Data_free | Auto_increment | Create_time | Update_time |
Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| t1 | MYISAM | Fixed | 0 | 0 | 0 | 8573157375 |
1024 | 0 | NULL | 1999-11-14 11:23:15 | 1999-11-14 11:23:15 | NULL
| max_rows=10000000 | |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
This created a table that can be 8G with 8573157375/(255+255+1) =
16777216 rows
mysql> ALTER TABLE t1 add c int;
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like "t1";
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |
Index_length | Data_free | Auto_increment | Create_time | Update_time |
Check_time | Create_options | Comment |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
| t1 | MYISAM | Fixed | 0 | 0 | 0 | 8640266239 |
1024 | 0 | NULL | 1999-11-14 11:26:31 | 1999-11-14 11:26:31 | NULL
| max_rows=10000000 | |
+------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+---------+
Regards,
Monty
