Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

After adding a fulltext index to one column in a table with 30,000 rows that table is now inaccessible. Any attempts to modify, drop, truncate, or access it hang. MySQL also reports that the user has too many connections. Why would adding a fulltext index cause this problem? How do I delete this table?

share|improve this question
    
Do you have enough diskspace? If running Linux of some sort, does dmesg tell you something about I/O errors? –  Janne Pikkarainen Sep 16 '10 at 10:16
    
I have plenty of diskspace. –  Daniel Sep 16 '10 at 23:54
add comment

2 Answers

up vote 1 down vote accepted

Altering a large table is going to take some time. You should have tested your changes before running them against production.

I suspect it was a table that is written to. When you started the alteration, it became locked. When connections attempt to write to it, they wait for it to unlock. Eventually, all available connections filled waiting for the table to unlock.

If you don't want to risk data corruption, wait for it to stop. Otherwise, you can kill the alter but rollback can potentially take a while as well. If you stop the database, you risk data corruption. It may recover but taking actions like that are further irresponsible.

share|improve this answer
    
Remarkably, after a week or so the table no longer hangs. Somehow the fulltext indexing eventually finished. –  Daniel Sep 21 '10 at 6:10
add comment

I would start by stopping the server, and running mysqlcheck on that database (if you can't stop the server, then make a backup of the entire database, copy it to a different machine and run the checks there).

The initial set of options would be to just check for errors. Once you have that, update your post with the details from that check and we'll take it from there.

share|improve this answer
    
Taking these actions risk data corruption. Data corruption risk should be weighed versus the cost of application inaccessibility and how recent the backups are. If InnoDB, rollback on restart can also be time consuming. –  Warner Sep 16 '10 at 17:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.