Sequelize – Check the manual that corresponds to your MySQL server version for the right syntax to use – Phpmyadmin

I’m trying to deploy a web-app and the database is a MySQL database that I’m connected to via Sequelize.

Generally I use PostgreSQL, but the web hosting uses MySQL with access via phpMyAdmin.

I have a api & db query that works locally and in Heroku (I assume because both uses PostgreSQL), but not with this web hosting probably because they use MySQL.

When I make the API call, I get this error below.

"sqlMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%%' OR `Cocktail`.`description` ILIKE '%')' at line 1",
"sql": "SELECT count(*) AS `count` FROM `Cocktails` AS `Cocktail` WHERE (`Cocktail`.`name` ILIKE '%%' OR `Cocktail`.`description` ILIKE '%');"

Below is the query I’m making. It seems like this only works if I remove the iLike, the modulus operator %, and Op.or; which basically strips the search functionality.

I initially have mysql2 installed, but I also added mysql, hoping it would somehow resolve this; but it didn’t.

How would I resolve this issue? Sorry if a trivial issue.. I’m literally at a loss on how to proceed..

const paginator = async (req, res, limit) => {
  const { searchIngredients, searchTerm } = req.query;
  const ingredients =
    searchIngredients &&
    searchIngredients.split(',').map((ingredient) => `%${ingredient}%`);
  const { page } = req.query;
  if (!searchIngredients) {
    await Cocktail.findAndCountAll({
      where: {
        [Op.or]: [
          {
            name: {
              [Op.iLike]: `%${searchTerm || ''}%`,
            },
          },
          {
            description: {
              [Op.iLike]: `%${searchTerm || ''}%`,
            },
          },
        ],
      },
    }).then(async (data) => {
      const offset = limit * (page === undefined ? 1 - 1 : page - 1);
      await Cocktail.findAll({
        limit,
        offset,
        order: [[`createdAt`, 'DESC']],
        where: {
          [Op.or]: [
            {
              name: {
                [Op.iLike]: `%${searchTerm || ''}%`,
              },
            },
            {
              description: {
                [Op.iLike]: `%${searchTerm || ''}%`,
              },
            },
          ],
        },
        include: [
          {
            model: Ingredient,
            as: 'ingredients',
            attributes: {
              exclude: ['createdAt', 'updatedAt', 'Cocktail_Ingredient'],
            },
          },
          {
            model: Step,
            as: 'steps',
            attributes: {
              exclude: ['createdAt', 'updatedAt', 'Cocktail_Step'],
            },
          },
        ],
      })
        .then((cocktails) => {
          return res.status(200).send({ count: data.count, limit, cocktails });
        })
        .catch(() => res.status(500).send({ message: 'error here too' }));
    });

EDIT:

Converted my iLike queries to be MySQL friendly using the below:

Sequelize.where(Sequelize.fn('lower', Sequelize.col('name')), {
  [Op.like]: `%${searchTerm || ''}%`,
}),