Mysql – How to repair all indexes in a MySQL database

full-text-searchindexMySQL

My database got corrupted, and now the php app is throwing this error:

Invalid query: Can't find FULLTEXT index matching the column list

The error is not very useful, since it doesn't say what column/table is causing it.
Is there a way to regenerate FULLTEXT indexes on all columns in all tables within a specific database?

Thanks

Best Answer

Perhaps you should look for all tables that have fulltext indexes

SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics
WHERE index_type='FULLTEXT';

To individually remake all tables that have FULLTEXT indexes

REPAIR_SQL_SCRIPT=RemakeTables.sql
SQL="SELECT"
SQL="${SQL} CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=',eng,';')"
SQL="${SQL} FROM (SELECT A.table_schema db,A.table_name tb,B.engine eng"
SQL="${SQL} FROM (SELECT DISTINCT table_schema,table_name"
SQL="${SQL} FROM information_schema.statistics WHERE index_type='FULLTEXT') A"
SQL="${SQL} INNER JOIN information_schema.tables B"
SQL="${SQL} USING (table_schema,table_name)) AA"
mysql -uroot -ANe"${SQL}" > ${REPAIR_SQL_SCRIPT} 
cat ${REPAIR_SQL_SCRIPT}

If you are OK with the contents of RemakeTables.sql, then execute it:

mysql -uroot < ${REPAIR_SQL_SCRIPT}

CAVEAT : All indexes are remade as well

Related Question