Issues converting to Fulltext search (duplicated index)

[Voyager-S]

Hello everyone.

I have a quite large forum (1,200,000+ posts, 15,000 members) and I'm currently unable to use the search funtion.

I've opened a support ticket but the responses I've got didn't solve my problem so I think this would be the best place to open this thread

I was having a lot of issues with my forum so I tried to convert to fulltext, hoping I would have a better performance.

When I ran the query, my server went crazy so I had to stop it. Final result: post table crashed and a duplicated index on the table (title and title_2). After 15 hours of repairing the post table and trying to recreate the index, I still have a duplicated index (though it shows as fulltext), but when checking the search options, it still shows that I'm using the vB search function instead of Fulltext search. And of course, the normal search doesn't work properly cause I had to empty the index.

Having so many problems with my DB lately, I'm scared to delete the wrong index, so I could really use some help. By the way, I ran the query to alter the thread table too.

I'm a little bit lost. Anyone willing to help? I'll prepare cookies for everyone :erm:

Thanks in advance, guys!

Edit: When I enter the post table through phpMyAdmin I get a warning: 'The following indexes appear to be equal and one of them should be removed: title, title_2'

[Voyager-S]

An attachment of the index section for the 'table' post in phpMyAdmin

[veenuisthebest]

did you tried Maintainance->Repairing/Optimizing tables and Fix Unique Indexes

[Voyager-S]

Yes, I did a full repair through SSH (it took many MANY hours) and also fixed the Unique Indexes. Stil having that problem after doing that.

EDIT: I want to add that i have no hacks or plug-in's installed. In fact I'm waiting to fix this to reinstall my plug-ins. This didn't happen after using any hack, it happen after the database crashed.

BTW I could really use a list of the tables and fields included in a default vB installation, I wanna make sure I don't have anything left in the DB from any past hack

[Voyager-S]

I could really use some help with this, still not working. I erased the title_2 index but for some reason, vBulletin doesn't recognize the fulltext search index. I think I've done everything I could already, still no hacks running but can't seem to get this to work.

The weird thing about this is that even though we ran both queries and everything seems to be ok after deleting the duplicated index, the admin CP still says I'm using de default vBsearch! So I can't choose to go back to the default vB search type, it's already using it. And knowing that we emptied the index used by the default search when converting to fulltext, it's only working with the posts created AFTER we ran the convert to fulltext queries (remember it took around 15 hours!). Permission problem? maybe we have to force that option? It's weird.

[snakes1100]

What cmd(s) did you run via ssh to fix the db?

What version of mysql are you running on the server?

[Voyager-S]

Thanks for your reply, snakes.

I ran through console (same user, database and password as my vB installation) the two queries needed according to the vB options:

ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)
ALTER TABLE thread ADD FULLTEXT INDEX (title)

My MySQL version is 5.0.45-community

--------------- Added [DATE]1212456274[/DATE] at [TIME]1212456274[/TIME] ---------------

It seems we solved this issue by forcing vBulletin to use fulltext search through the settings table and then restarting apache and mysql.

Thanks again!