Full Text Search not working

[MatLudlam]

Hi, I have just upgraded my dev system to 5.3.0 as I wanted to try out the full text search.

I have enabled this on the Contacts table but whenever I try and search for anything I get "Bad Server Response" error.

When I dig into .../data/logs/espo=2-18-07-10.log the error is:

[2018-07-10 07:06:33] Espo.ERROR: API [GET]:/:controller, Params:Array (     [controller] => Contact ) , InputData:  - S
QLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list [] []
[2018-07-10 07:06:33] Espo.ERROR: Display Error: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching
 the column list, Code: HY000 URL: /espo_dev/api/v1/Contact?select=salutationName%2CfirstName%2ClastName%2Cname%2Caccoun
tId%2CaccountName%2CemailAddressIsOptedOut%2CemailAddress%2CemailAddressData%2CphoneNumber%2CphoneNumberData&maxSize=20&
offset=0&sortBy=createdAt&asc=false&where%5B0%5D%5Btype%5D=textFilter&where%5B0%5D%5Bvalue%5D=oggs [] []

The output from

mysql --version

is

mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

Any help appreciated.

Thanks

[MatLudlam]

Note that I don't have Cron running for this environment, don't know if that is an issue.

[yuri]

https://github.com/espocrm/documenta...ll-text-search
" Mysql 5.5 doesn't support full-text search for InnoDB tables. 5.6.4 and later versions do support."

Could be this.

I suppose that you run rebuild.

[MatLudlam]

Ok, the version of MySQL is 5.7.22 i believe (see mysql --version above).

When you say "rebuild", I assume you mean the "rebuild" option in the "Administration" menu. If that is the case, then yes I have done this.

All of my tables are InnoDB.

The error text says "... Can't find FULLTEXT index matching the column list ..." When I look at the indices on the table there are no FULLTEXT ones, only BTREE. It seems that they are not being created for some reason.

[tarasm]

Could print a result of the following MySQL query:

[URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]SHOW[/URL] [URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]CREATE[/URL] [URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]TABLE[/URL] `email`

[MatLudlam]

I have done some more digging ...

I enabled cron on the dev box and now have the following text in my .../data/logs/... file

[2018-07-10 08:20:39] Espo.ERROR: API [GET]:/:controller, Params:Array (     [controller] => Contact ) , InputData:  - SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list [] []
[2018-07-10 08:20:39] Espo.ERROR: Display Error: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list, Code: HY000 URL: /espo_dev/api/v1/Contact?select=salutationName%2CfirstName%2ClastName%2Cname%2CaccountId%2CaccountName%2CemailAddressIsOptedOut%2CemailAddress%2CemailAddressData%2CphoneNumber%2CphoneNumberData&maxSize=20&offset=0&sortBy=createdAt&asc=false&where%5B0%5D%5Btype%5D=textFilter&where%5B0%5D%5Bvalue%5D=oggs [] []
[2018-07-10 08:20:41] Espo.ERROR: API [GET]:/:controller, Params:Array (     [controller] => Contact ) , InputData:  - SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list [] []
[2018-07-10 08:20:41] Espo.ERROR: Display Error: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list, Code: HY000 URL: /espo_dev/api/v1/Contact?select=salutationName%2CfirstName%2ClastName%2Cname%2CaccountId%2CaccountName%2CemailAddressIsOptedOut%2CemailAddress%2CemailAddressData%2CphoneNumber%2CphoneNumberData&maxSize=20&offset=0&sortBy=createdAt&asc=false&where%5B0%5D%5Btype%5D=textFilter&where%5B0%5D%5Bvalue%5D=oggs [] []
[2018-07-10 09:57:58] Espo.WARNING: Utf8mb4: FAILED executing the query - [ALTER TABLE `email`   CHANGE COLUMN `body_plain` `body_plain` mediumtext  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;], details: SQLSTATE[HY000]: General error: 1283 Column 'body_plain' cannot be part of FULLTEXT index. [] []
[2018-07-10 09:57:58] Espo.WARNING: Utf8mb4: FAILED executing the query - [ALTER TABLE `email`   CHANGE COLUMN `body` `body` mediumtext  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;], details: SQLSTATE[HY000]: General error: 1283 Column 'body' cannot be part of FULLTEXT index. [] []

My reading of this is that it has problems creating the FULLTEXT index because of collation sequences on text fields. Not sure if that is true or how to resolve it.

I also remember that there was a scheduled job called "...mb4" that had a status of failed. This has since been cleaned up. Not sure if this is related.

[MatLudlam]

If I look on the "email" table there is one FULLTEXT index on the fields: name, body_plain and body (this seems to have been created during the upgrade). They are all with a collation sequence of utf8_unicode_ci which is different to many of the other text fields in this table which have a collation of utf8mb4_unicode_mb4.

When looking at the contact table (which is the one where I turned on "full test" indexing within the application and did the re-build) there is no FULLTEXT index at all. There was also no error in the log file.

[tarasm]

I would like to see your email schema, please print a result of the MySQL query:

[URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]SHOW[/URL] [URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]CREATE[/URL] [URL="https://admin.espocloud.eu/phpm/url.php?url=http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html"]TABLE[/URL] `email`

[MatLudlam]

Sorry for the delay. Email table structure:

CREATE TABLE `email` (
  `id` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `from_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_string` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reply_to_string` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_replied` tinyint(1) NOT NULL DEFAULT '0',
  `message_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `message_id_internal` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `body_plain` mediumtext COLLATE utf8_unicode_ci,
  `body` mediumtext COLLATE utf8_unicode_ci,
  `is_html` tinyint(1) NOT NULL DEFAULT '1',
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'Archived',
  `has_attachment` tinyint(1) NOT NULL DEFAULT '0',
  `date_sent` datetime DEFAULT NULL,
  `delivery_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT '0',
  `from_email_address_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_type` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sent_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modified_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `assigned_user_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `replied_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_name_map` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_E7927C74D445573A` (`from_email_address_id`),
  KEY `parent` (`parent_id`,`parent_type`),
  KEY `IDX_E7927C74B03A8386` (`created_by_id`),
  KEY `IDX_E7927C74A45BB98C` (`sent_by_id`),
  KEY `IDX_E7927C7499049ECE` (`modified_by_id`),
  KEY `IDX_E7927C74ADF66B1A` (`assigned_user_id`),
  KEY `IDX_E7927C74B4E994E0` (`replied_id`),
  KEY `IDX_E7927C749B6B5FBA` (`account_id`),
  KEY `IDX_EMAIL_DATE_SENT` (`date_sent`,`deleted`),
  KEY `IDX_EMAIL_DATE_SENT_STATUS` (`date_sent`,`status`,`deleted`),
  KEY `IDX_E7927C74537A1329` (`message_id`),
  FULLTEXT KEY `IDX_SYSTEM_FULL_TEXT_SEARCH` (`name`,`body_plain`,`body`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Not sure if this is two un-related issues.

The error with the "email" table was [I believe] triggered by the upgrade. I also have no emails in this database.

The issue that triggered this post was because I cannot do a search on Contact (with 4+ characters) once I have enabled "full-text search". Interestingly the search with only 3 characters works fine, I am guessing different code paths.

For reference here is the structure of `Contact` after I have enabled the feature and done the rebuild:

CREATE TABLE `contact` (
  `id` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `salutation_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `do_not_call` tinyint(1) NOT NULL DEFAULT '0',
  `address_street` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address_postal_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `campaign_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modified_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `assigned_user_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_4C62E6389B6B5FBA` (`account_id`),
  KEY `IDX_4C62E638F639F774` (`campaign_id`),
  KEY `IDX_4C62E638B03A8386` (`created_by_id`),
  KEY `IDX_4C62E63899049ECE` (`modified_by_id`),
  KEY `IDX_4C62E638ADF66B1A` (`assigned_user_id`),
  KEY `IDX_CONTACT_FIRST_NAME` (`first_name`,`deleted`),
  KEY `IDX_CONTACT_NAME` (`first_name`,`last_name`),
  KEY `IDX_CONTACT_ASSIGNED_USER` (`assigned_user_id`,`deleted`),
  KEY `IDX_CREATED_AT` (`created_at`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[tarasm]

Please try to upgrade to version 5.3.2.

[MatLudlam]

Thanks for the feedback. I have done the following:

1. Upgraded to 5.3.2
2. Rebuilt the database
3. I was still getting "Bad Server Response" errors and log file entries referencing FULLTEXT
4. I disabled the "Full-Text Search" check box on Contact
5. Did a rebuild
6. Enabled the check box on Contact
7. Did a rebuild

Still not working.

When I look at the table definition for contact, there are 10 BTREE indexes (as above) and no FULLTEXT ones.

Thanks for the help.

[tarasm]

It's a bug. We are working on fixing it. It will work if you add some additional fields to search in Contact (Entity Manager).

[MatLudlam]

Ah, ok. I just tried what you said (i.e. included an extra field to search on) then did a rebuild and it worked. No more errors and when I look in the database there is a FULLTEXT index.

I will wait for the fix as this is only my dev system.

Thanks for all the help.

[MatLudlam]

This all seems to be working fine in 5.3.3. Thanks for the help.

[olof]

Does anyone have full text search working on MariaDB? We are on 10.3.8-MariaDB?