Forum Large VB forum upgrade problems!
 

I've run into some problems upgrading our forums to 4.2. It appears our 'vb_user' table (8.5 million rows) has corrupted and now whenever I run the upgrade script, the query will say 'copying to tmp table' and after 20 minutes, it will say 'REPAIR BY KEYCACHE'.

This first started happening when the upgrade ran: ALTER TABLE vb_user ADD INDEX fbuserid (fbuserid) (4.0.3 stage) which ran for 1.5 hours. By the time this completed, the upgrade script reached the 1 hour timeout I set. I searched to find out why this was happening and found that it could be a disk space issue, and to my surprise I only had 2GB of space left (I had done a couple of backups and I hadn't realised it used all the space up!). I cleaned out the disk, ran myisamchk vb_user.MYI and all seemed ok - although I noticed in phpmyadmin the cardinality of all but the PRIMARY index on vb_user was 0. I wasn't able to find any indication on the internet that this would cause problems and I ran a few simple queries using the indexes and they ran ok.

I loaded up the upgrade script again and it continued ok until, ALTER TABLE vb_user ADD fbjoindate INT UNSIGNED NOT NULL DEFAULT 0 which again ended up doing a 'REPAIR BY KEYCACHE'. This time I stopped the upgrade, killed the process and did a myisamchk -r -f vb_user.MYI which completed, but in phpmyadmin the cardinality of all indexes were now all 0. Believing that myisamchk must have repaired the indexes as no errors were thrown up, and also making sure it was not an issue with limited /tmp space (I created and set my tmpdir in my.cnf to /home/mysqltmp), I manually ran the query to add fbjoindate, but once again it ended up running a keycache repair.

I have followed all the suggestions I could find, increasing buffers etc. I'd previously upgraded the forum on a test server successfully, but now I'm lost.

Can anyone advise what to do? Is there anything more I can try on the vb_user table (assuming there is something wrong with it) so that it doesn't keep running keycache repair. My test upgrade ran REPAIR BY SORTING only. Or is the keycache repair a symptom of some misconfiguration I may have missed, albeit ALTER queries were running fine on the user table before it added the fbuserid index.

Also, one of my main concerns is attachments. The upgrade stated it was 'converting' attachments at one stage. If need be and I can't fix this user table, will I be able to start the upgrade from scratch with these attachments or do I need to restore attachments from a backup.

I'd really appreciate any help.