Instant Response System - MySQL Error Message

[cthorpe]

We are currently on 3.20.01 using Zend and MySQL 5.0.45 (This error appeared even before I performed the upgrade and if I recall, the problem started back when we were at 3.11.01)

Whenever I try to submit a trouble ticket, we would see this error message where the IRS kicks in:

Invalid SQL: SELECT kbarticlelinks.kbarticleid, MATCH(kbarticledata.contents) AGAINST ('copier working') AS relevance FROM `swkbarticledata` AS kbarticledata LEFT JOIN `swkbarticlelinks` AS kbarticlelinks ON (kbarticledata.kbarticleid = kbarticlelinks.kbarticleid) LEFT JOIN `swtgroupassigns` AS tgroupassigns ON (kbarticlelinks.kbcategoryid = tgroupassigns.toassignid AND tgroupassigns.type = '5') LEFT JOIN `swkbcategories` AS kbcategories ON (kbarticlelinks.kbcategoryid = kbcategories.kbcategoryid) WHERE (tgroupassigns.tgroupid = '1' OR kbarticlelinks.kbcategoryid = '0') AND MATCH(kbarticledata.contents) AGAINST('copier working') LIMIT 5; (The used table type doesn't support FULLTEXT indexes)

Thanks in advance,
Christopher

[Ryan Lederman]

cthorpe, Please e-mail me and provide me with access to your database and I'll try to see if I can see what's going on. Don't forget the URL to your help desk so I can test the fix.

[Ryan Lederman]

Just replied to your e-mail. For everyone else's information, this error is caused by having InnoDB tables instead of MyISAM. InnoDB tables do not yet support fulltext searching. Please see MySQL Bugs: #13979: InnoDB engine doesn't support FULLTEXT for more information.

[craigbrass]

A better way would be to have a separate MyISAM for fulltext like we have done in our software of cause as InnoDB is much better at recovering from database crashes in MySQL5.

[cthorpe]

Ryan suggested that I convert the database to use MYISAM instead of the default INNODB because INNODB does not support FULLTEXT yet so therefore the IRS did not work properly.

After converting it to use MYISAM,

Ryan then ran the following command:

“ALTER TABLE `swkbarticledata` ADD FULLTEXT (`contents`)”

The IRS finally worked without any issues.

Ryan, thank you for solving this issue.

Here is my next question:

I prefer to use INNODB, can I leave 'swkbarticledata' alone using MYISAM and convert the other tables back to INNODB?

[Jamie Edwards]

Hi cthorpe,

I prefer to use INNODB, can I leave 'swkbarticledata' alone using MYISAM and convert the other tables back to INNODB?

Yes, this would be fine