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?
Asked
Active
Viewed 1,178 times
0
-
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
2 Answers
1
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.
wolfgangsz
- 8,847
- 3
- 30
- 34
-
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
1
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.
Warner
- 23,756
- 2
- 59
- 69
-
Remarkably, after a week or so the table no longer hangs. Somehow the fulltext indexing eventually finished. – Daniel Sep 21 '10 at 06:10