Problem creating indices, autoincrement id's on HEAP engine.

[Michael Paik]

All,

I have a relatively large table I am trying to load into a HEAP table. At the moment I have reserved approximately 3GB of main memory for it, and am using ~2GB. 222 attributes, with average row width of 4259. Here are the results of ;show table status;':

mysql> show table status;
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+
| 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 |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+
| cache | HEAP | 9 | Dynamic | 500000 | 4259 | 2138450360 | 3095270840 | 0 | 0 | NULL | NULL | NULL | NULL | latin1_swedish_ci | NULL | max_rows=2100000 avg_row_length=8000 | |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+

As you can see, I have half a million rows (500,000). I can try to create an index on any column, or create an autoincrement id with alter table, and the operations always fail with:

ERROR 1030 (HY000): Got error 12 from storage engine

I am told that ERROR 1030 usually implies a lack of storage space, but seeing as how I can fit another 207K rows (this demonstrated by experiment), it seems unusual that there would be no more space to create a column with an integer value, where the size would be (4B * 500000) * 1.05 (assuming 5% table overhead) ~= 2.1MB.

Any ideas? I notice there isn't much traffic on this forum, but hopefully someone has done this before. In the meantime I will try to do the operation with 50,000 rows to see if it's some size limitation.