Announcement

Collapse
No announcement yet.

Full Text Search not working

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Full Text Search not working

    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:

    Code:
    [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
    Code:
    mysql --version
    is
    Code:
    mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
    Any help appreciated.

    Thanks

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

    Comment


    • #3
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          Could print a result of the following MySQL query:
          Code:
          [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`
          Job Offers and Requests

          Comment


          • #6
            I have done some more digging ...

            I enabled cron on the dev box and now have the following text in my .../data/logs/... file
            Code:
            [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.

            Comment


            • #7
              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.

              Comment


              • #8
                I would like to see your email schema, please print a result of the MySQL query:
                Code:
                [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`
                Job Offers and Requests

                Comment


                • #9
                  Sorry for the delay. Email table structure:
                  Code:
                  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:
                  Code:
                  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

                  Comment


                  • #10
                    Please try to upgrade to version 5.3.2.
                    Job Offers and Requests

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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).
                        Job Offers and Requests

                        Comment


                        • #13
                          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.

                          Comment


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

                            Comment


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

                              Comment

                              Working...
                              X