FULLTEXT | MYSQL | failed with status code 500. Error: ER_PARSE_ERROR: You have an error in your SQL syntax;

[MwSpaceLLC]

Describe the bug

Hi there,

Today we try to fulltextsearch with mysql, after install system, and configure as follow:

Our application model:

Our request crud (get):

Server response:

System as installed with clear installation and clear mode, repo & crud generation.

Logs

No response

Additional information

Install loopback, generate model, add fulltextindex, add mysql datasource, query with match fulltext query

Reproduction

Sorry, dataset has private, we can reproduce error with dataset demo.

[akshatdubeysf]

@MwSpaceLLC It seems like there could be a couple of reasons that result in the error that you are facing -

• The LB4 cli seems to add an old version loopback-connector-mysql while creating a datasource, if that is the same in your case, you need to manually update the loopback-connector-mysql version to atleast 6.1.0 to use the match operator.

• The index is not being migrated through the column level configurations as mentioned in this issue, can you try this instead in the model decorator -

@model({
  settings: {
    strict: true,
    allowExtendedOperators: true,
    mysql: {
      table: '<your table name>',
    },
    indexes: {
      some_name_index: {
        kind: 'FULLTEXT',
        columns: '<your column name>',
      },
    },
  },
})
export class YourModel extends Entity {