FULLTEXT search in innoDB database

ساخت وبلاگ
I have reasently switched my databse from MyiSam to innoDB because of access to foreign keys (I have about 150 tables where most of them are referencing each other and foreign keys just makes my life so much easier)

One problem though - it broke my FULLTEXT searches and a search like:
PHP Code:

SELECT 
    date 

    
username 
    
bio 
    
randomString 
FROM 
    userlogininfo 
WHERE 
    MATCH 
username bio AGAINST ( ? IN BOOLEAN MODE 
ORDER BY 
    date DESC 

giving a list of users matching the search criteria in ? now throws a "#1191 - Can't find FULLTEXT index matching the column list"

And both username (TEXT) and bio (TEXT) has FULLTEXT indexes
Allthough a:
PHP Code:

SHOW INDEX FROM userlogininfo

shows that not only does both username and bio have FULLTEXT indexes, but so do username_2 and bio_2 (none of which are named columns in the table) - don't know if that matters, but maybe?

But like I said - where the above user search in MyiSam worked fine - in innoDB they throw a "#1191 - Can't find FULLTEXT index matching the column list"

I have tried reading the MySQL documentation and looking on different forums (incl. this one) but all proposed solutions are on how to add the FULLTEXT index for people who didn't already (tried adding FULLTEXT again using the suggested ALTER TABLE in different answers - but none of them did anything and it still throws the same error #1191)

Anybody have any idea what to do to make my FULLTEXT searches work again? (because I would really like to avoid loosing the foreign key functionality going back to MyiSam)

userlogininfo
Code:

------------------------------------------
| id              | username | bio      |
------------------------------------------
| INT(11) NOT NULL | TEXT    | TEXT    |
| AUTO_INCREMENT  | FULLTEXT | FULLTEXT |
------------------------------------------

CodingForums...
ما را در سایت CodingForums دنبال می کنید

برچسب : نویسنده : codingforums بازدید : 187 تاريخ : پنجشنبه 30 آذر 1396 ساعت: 23:15