MySQL hangs and table inaccessible after adding fulltext index

full-text-searchMySQL

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?

Best Answer

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.

Related Solutions

Mysql – Weird MySQL query plan: why is this query using temporary & filesort? How to optimize it

Indexes tend not to be used when the values are used in a OR condition. (amp_c.zone = '24' OR amp_c.zone = '25') may be better written as (amp_c.zone BETWEEN '24' AND '25')`.

Indexes tend not to be used when they occur on one side of an OR but not the other. Use of amp_c.some_countr = '0' OR (...) will likely invalidate use of the index.m

Optimizers work better with simple equalities. You may need outer joins rather than inner joins.

Mysql – After setting root password why does MYSQL still allow me to login without a password

Looks like the password is set on the 'root'@'localhost' user entry, but not on the 'root'@'%' entry; password-free authentication would be allowed based on that.

For security purposes, reconsider allowing root access from anywhere. If you do need it, then just get rid of the localhost specifications:

drop user 'root'@'localhost';
drop user 'root'@'127.0.0.1';
drop user 'root'@'::1';

And set the password for the 'root'@'%' user:

set password for 'root'@'%' = password('passwordhere');

Best Answer

Related Solutions

Mysql – Weird MySQL query plan: why is this query using temporary & filesort? How to optimize it

Mysql – After setting root password why does MYSQL still allow me to login without a password

Related Topic