#1214 - The used table type doesn't support FULLTEXT indexes

[vmo]

Hi,
I am having an issue while migrating a site from the development server to the live server.

On the dev server we have the mysql 5.6 and on the client live server we have the mysql 5.5.

During the migration to the live server during the process of importing the exported SQL file we start to receive the following error:

SQL query:

ALTER TABLE `fieldtype_options` ADD FULLTEXT KEY `title_value` (`title`,`value`)
MySQL said: Documentation

#1214 - The used table type doesn't support FULLTEXT indexes

After some research the solution was to run the following command on each table

ALTER TABLE <table name> ENGINE = MYISAM

So at the end of the SQL file we enter this command for each table and the SQL file was imported correctly without any error.

It seams that everything was alright but while testing the search functionality on the frontend we are getting the following error:

SQLSTATE[42S02]: Base table or view not found: 1109 Unknown table 'INNODB_FT_DEFAULT_STOPWORD' in information_schema

(Full error on the attached image)

Executing the query:

SELECT * FROM INFORMATION_SCHEMA.TABLES

the table "INNODB_FT_DEFAULT_STOPWORD" is not there.

While questioning the server support I am not getting any answer how to fix this and i can't find any solution to fix this while using the mysql 5.5 or if it is a mysql 5.5 installation issue and the table should be there.

As far as I can understand the table "INNODB_FT_DEFAULT_STOPWORD" is a internal mysql table and should exist on the "INFORMATION_SCHEMA", but I can be wrong about this. I could not find much information about this table on a mysql 5.5 server.

Any ideia or solution (if any) I would be most appreciated.

Thank you

Edited October 15, 2020 by vmo
cmd + enter while typing

[kongondo]

@vmo,

Was there question you wanted to ask?

[vmo]

41 minutes ago, kongondo said:

@vmo,

Was there question you wanted to ask?

I might missed the question, thank you.

Is there a way to search text in fields without using the table "INNODB_FT_DEFAULT_STOPWORD"?

or in the mean while implementing a work around maybe with a hook implementation to be able to search text  in fields and not have this issue?

Thank you

[dragan]

It's weird that mySQL Workbench doesn't show any INNODB_FT_DEFAULT_STOPWORD table, just these three:

That's with version 5.7.24 though. It seems strange that I don't see that table, but PW search works just fine.

You could try to use the Migration Wizard from Workbench (with a local copy), perhaps you'll get more control with your import. But ultimately, since this is a system table, maybe you don't have enough rights to manipulate that anyway on your hosting setup.

[vmo]

Thank you for your replies
you are right @dragan I do not have enough rights to manipulate the DB so nothing better do take a peek into the core and see what I could do and this is what I did to temporary fix the issue:

/**
* Get all fulltext stopwords for database engine
*
* @param string $engine Specify DB engine of "myisam" or "innodb" or omit for current DB engine
* @param bool $flip Return flipped array where stopwords are array keys rather than values? for isset() use (default=false)
* @return array
*
*/
public function getStopwords($engine = '', $flip = false) {
    $engine = $engine === '' ? $this->engine : strtolower($engine);

    // ORIGINAL
    // if($engine === 'myisam') return DatabaseStopwords::getAll();

    // TEMP FIX 20201015: FOR MySQL 5.5 without INNODB_FT_DEFAULT_STOPWORD
    /*
    SQL query:
    ALTER TABLE `fieldtype_options` ADD FULLTEXT KEY `title_value` (`title`,`value`)
    MySQL said: Documentation
    #1214 - The used table type doesn't support FULLTEXT indexes
    
    AFTER RUNNING THE FOLLOWING COMMAND FOR EACH TABLE
    ALTER TABLE <table name> ENGINE = MYISAM
    */

    // Will always return the DatabaseStopwords::getAll();
    return DatabaseStopwords::getAll();


    if($this->stopwordCache === null) { //  && $engine === 'innodb') {
        $cache = $this->wire()->cache;
        $stopwords = null;
        if($cache) {
            $stopwords = $cache->get('InnoDB.stopwords');
            if($stopwords) $stopwords = explode(',', $stopwords);
        }
        if(!$stopwords) {
            $query = $this->prepare('SELECT value FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD');
            $query->execute();
            $stopwords = $query->fetchAll(\PDO::FETCH_COLUMN, 0);
            $query->closeCursor();
            if($cache) $cache->save('InnoDB.stopwords', implode(',', $stopwords), WireCache::expireDaily);
        }
        $this->stopwordCache = array_flip($stopwords);
    }
    return $flip ? $this->stopwordCache : array_keys($this->stopwordCache);
}

 

Now I have other issue but it will be "simpler" to fix (I hope)
- The stop words array returned by the "DatabaseStopwords::getAll();" are only in english and since the site is multi language I need to find a way to get the current user language and change the "DatabaseStopwords::getAll();" to return the stop words in the language that the user has selected.

Right now is what I could do to put the search by text to work.

I will look further into this and see what I can do in a better way since the client's server will not be updated for while and normally it means never!

Thank you

 

 

 

[adrian]

4 hours ago, vmo said:

the client's server will not be updated for while and normally it means never

That's a shame given that 5.5 was EOL'd 2 years ago ?