Drop MyISAM in SearchIndex table

[dolphiq-allard]

This question has been asked several times before, but let me bring this under attention one more time.

Would it be possible to drop the MyISAM engine in the searchindex table. We host several sites on DigitalOcean and they don't support it anymore, resulting in an error during installation. And since I always forget to remove this part before running the installation, I need to clean out the DB again before getting everything up and running.

Would be much appreciated.

Current situation:

// Add the FULLTEXT index on searchindex.keywords
$this->createTable(Table::SEARCHINDEX, [
    'elementId' => $this->integer()->notNull(),
    'attribute' => $this->string(25)->notNull(),
    'fieldId' => $this->integer()->notNull(),
    'siteId' => $this->integer()->notNull(),
    'keywords' => $this->text()->notNull(),
], ' ENGINE=MyISAM');

Preferable situation:

// Add the FULLTEXT index on searchindex.keywords
$this->createTable(Table::SEARCHINDEX, [
    'elementId' => $this->integer()->notNull(),
    'attribute' => $this->string(25)->notNull(),
    'fieldId' => $this->integer()->notNull(),
    'siteId' => $this->integer()->notNull(),
    'keywords' => $this->text()->notNull(),
]);

Thanks much

[brandonkelly]

Looks like fulltext indexes have been available for InnoDB tables since MySQL 5.6.4, and since Craft 4 requires MySQL 5.7.8+, I don’t think there’s any good reason not to do this. So we’ll look into it for 4.1.

[jonaswouters]

This still doesn't work out-of-the-box for DigitalOcean, unfortunately.

By altering the Install.php file, I made it work. I think the result is the same with less LoC.

        if ($this->db->getIsMysql()) {
            $this->createIndex(null, Table::ELEMENTS_SITES, ['uri', 'siteId']);
            $this->createIndex(null, Table::USERS, ['email']);
            $this->createIndex(null, Table::USERS, ['username']);

            // Add the FULLTEXT index on searchindex.keywords
            $this->createTable(Table::SEARCHINDEX, [
                'elementId' => $this->integer()->notNull(),
                'attribute' => $this->string(25)->notNull(),
                'fieldId' => $this->integer()->notNull(),
                'siteId' => $this->integer()->notNull(),
                'keywords' => $this->text()->notNull(),
'PRIMARY KEY([[elementId]], [[attribute]], [[fieldId]], [[siteId]])',
            ]);

            $sql = 'ALTER TABLE ' .
                $this->db->quoteTableName(Table::SEARCHINDEX) . ' ' .
                'ADD FULLTEXT (' . $this->db->quoteColumnName('keywords') . ')';

            $this->db->createCommand($sql)->execute();
        } else {

[brandonkelly]

@jonaswouters CREATE FULLTEXT INDEX and ALTER TABLE … ADD FULLTEXT appear to just be aliases of each other. Did you get a specific error with the former?

[jonaswouters]

@jonaswouters CREATE FULLTEXT INDEX and ALTER TABLE … ADD FULLTEXT appear to just be aliases of each other. Did you get a specific error with the former?

Sorry for the late reply. This is the error:
I just did another install, same error, same solution.

*** failed to install Craft: An error occurred while executing the "craft\migrations\Install migration: SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
The SQL being executed was: CREATE TABLE `searchindex` (
	`elementId` int(11) NOT NULL,
	`attribute` varchar(25) NOT NULL,
	`fieldId` int(11) NOT NULL,
	`siteId` int(11) NOT NULL,
	`keywords` text NOT NULL
) ENGINE = InnoDb DEFAULT CHARACTER SET = utf8

[brandonkelly]

@jonaswouters Ahh… I missed that you had moved the primary key definition to the CREATE TABLE statement. The error message is complaining about the CREATE TABLE command, so that must be the important change there, not the switch from CREATE FULLTEXT INDEX to ALTER TABLE … ADD FULLTEXT.

Made the same change for the next Craft 3 and 4 releases (e2baa64) so this should be fully resolved going forward!

[brandonkelly]

Craft 3.7.55 and 4.2.5 are out with that change.