[Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP

Error report after project deployment

SequelizeDatabaseError: ER_ WRONG_ FIELD_ WITH_ GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mysql.category.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_ full_ group_ by

To put it simply, the output result is called target list, which is the field followed by select. There is another place, group by column, which is the field followed by group by. Because only_FULL_GROUP_By is turned on set, so if a field does not appear in the target list and group by fields at the same time, or the value of the aggregate function, the SQL query is considered illegal by MySQL and an error will be reported.

Error source code:

// Find All
const categoryAll = async (ctx) => {
  const resData = await Category.findAll({
    attributes: ['name', 'id', [sequelize.fn('COUNT', sequelize.col('name')), 'count']],
    // group: 'name', // Key
    where: {
      articleId: { [Op.not]: null }
    },
    order: [[sequelize.fn('COUNT', sequelize.col('name')), 'desc']]
  })
  ctx.body = successResult(resData)
}

Solution:

Global SQL modification is mentioned in several reference articles_ Mode, but I failed to modify it with this method. Even if it succeeds, it is temporary and will reappear after MySQL is restarted
so I chose another method to modify the MySQL configuration file and add SQL manually_Mode is mandatory. Only_FULL_GROUP_By is not required property
1. Confirm which configuration file MySQL loads. You can use the following methods to confirm

mysql –verbose –help | grep my.cnf

Directory where my configuration is located:

2. Modify the configuration and add the following content at the end of the configuration file

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: there is no keyword [mysqld] in my configuration file. If there is no keyword, an error will be reported
then restart MySQL.

Read More: