mysql 4.1.7 innodb index creation problems

[Sebastien Boving]

i have 4x 2gb innodb datafiles with the last one set to auto_increment, on a 300gb partition with nothing else on it. the tables are loaded with data and the last datafile has not incremented yet.

i then create an index on a table:

alter table NODES add INDEX whatever_blows_up (node_hierarchy_level, path_segment_name(50));

and it slowly fills up the whole disk, 300gb!

there doesn't seem to be anything i can do at this point to stop it, killing the thread doesn't work either.

only solution (sigh) is to kill -9 the mysqld's, delete all my datafiles (thank god this is not production) and reload the data.

so i recreate the datafiles and this time specify a max on the last (autoincrement) file, which i had hoped to avoid. This time the index creation again fills up the datafile until the max, and then just keeps running. Only if i kill -9 those mysql'd, restart mysql and run the query again does it eventually complain about a table full error (but the first time,it always keeps spinning).

i also get this when i simplify the index creation to:

create index IDX_PATH_SEGMENT_NAME_BIS on NODES (PATH_SEGMENT_NAME(50));

where i already have an index IDX_PATH_SEGMENT_NAME that is exactly the same!

mysql> show index from NODES;
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| NODES | 0 | PRIMARY | 1 | JOBSYS_NODE_ID | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 0 | PRIMARY | 2 | SYMBOLIC_LINK_NUMBER | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 1 | IDX_HIERARCHY_LEVEL__PARENT_NODE_ID | 1 | NODE_HIERARCHY_LEVEL | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 1 | IDX_HIERARCHY_LEVEL__PARENT_NODE_ID | 2 | PARENT_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | UIDX_FULL_PATH_NAME | 1 | FULL_PATH_NAME | A | 8959401 | 50 | NULL | | BTREE | |
| NODES | 1 | IDX_TOP_NODE_ID | 1 | TOP_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | IDX_PARENT_NODE_ID | 1 | PARENT_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | IDX_PATH_SEGMENT_NAME | 1 | PATH_SEGMENT_NAME | A | 8959401 | 50 | NULL | | BTREE | |
| NODES | 1 | IDX_ACTIVE_IND | 1 | ACTIVE_IND | A | 8959401 | NULL | NULL | | BTREE | |
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set (0.30 sec)

so how is this possible? the only difference i see is that one index has been created before i started inserting (about 9 million) rows in the table, the other one i create after the inserts.

what am i doing wrong?

this is mysql 4.1.7 running on AMD64 btw.

thanks,
-seb.