Incorrect key file for table; try to repair it, when merging very large MySQL tables into one
I am using MySQL server 5.0.67 on a Windows Vista 32 bit with 3GB of RAM...
I have a 4.5 million MyISAM row table (Table A) and I have created a C# .NET program which goes through each of these rows, extracts certain information and populates another MyISAM table (Table B). For each row in Table A, I gather about 80 rows for Table B. Table B's structure is as follows: Field1 (integer), Field2 (bit), Field3 (varchar(3)), Field4 (mediumint(8)), Field5 (mediumint(8)), Field6 (integer), and there is a unique key on the combination of Field1,2,3.
I insert into Table B with the IGNORE ON DUPLICATE KEY UPDATE...clause
When I looped through all the rows from A, the program started initially OK (about 50 rows a second), but as about 120,000 rows were processed from Table A, it started to become painstakingly slower. So I decided to split the inserting of rows into 500 smaller tables (rather than just Table B) with the same structure as Table B. This completed in about 1 day.
So then I tried to merge all the 500 tables into Table B (which up until now was empty). I created a script which ran a stored procedure doing an INSERT INTO B SELECT * FROM (one of those 500 tables) IGNORE ON DUPLICATE KEY UPDATE...I did this for a while, and I eventually decided to group about 150 calls to this sp with the respective tables, and let it run overnight. The problem is that at a certain point, after about 100 merges, I got the error "Incorrect key file for table B.MYI; try to repair it".
Could this be because the temporary file might have grown too big? Or is there any other reason why the index became corrupted? Maybe because it was dealing with more than 2GB of data? Would running batches of 5 merges (rather than 150) be a wiser solution? Repairing the table is not an option for me as I have no way of knowing where in the smaller tables it had erred, thus I have to start again and make sure it works. I am now thinking about updating to MySQL 5.5.19 using an .msi file, but I do not know if it is worth the hassle. I just want to populate this Table B and then dump it and move it elsewhere. I still have the original 500 tables so if someone can point me in the right direction of merging them into 1 that would be great!
Thanks in advance, Tim