[RUBY] A solution to an error that makes you angry that you are not following the MySQL default setting ONLY_FULL_GROUP_BY in a production environment and it is not unique.

problem

The portfolio for job change activities has just been deployed on AWS. When I tried to display the graph created by chart.js in the production environment, the following error was displayed.

error

Mysql2::Error:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pfc-master_production.posts.created_at' which ins not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT `posts`.`created_at` FROM `posts` WHERE `posts`.`user_id  = 2 GROUP BY date(created)

Related source code

charts_controller.rb

charts_controller.rb

class ChartsController < ApplicationController

  def index
    #calorie
    @sampleuser = User.find_by(id: 2)
    if user_signed_in?
      #Calculate total calories by date
      sum_calorie = current_user.posts.group("date(created_at)").sum(:calorie)
      #Since the total calories for each date is in the form of a hash, get the value, put it in an array and assign it to a variable
      array_calorie = sum_calorie.values
    else
      sum_calorie = @sampleuser.posts.group("date(created_at)").sum(:calorie)
      array_calorie = sum_calorie.values
    end
      #Pass data to js side using gon
    gon.data = []
    #Extract the total calories by date one by one with the map method
    #How to use map method → Array variable.map {|Variable name|Specific processing}
    gon.data = array_calorie.map{ |calorie| calorie}
    binding.pry
    if user_signed_in?
      #Created by date_Get only at column. Stored in the form of an array
      dates_calorie = current_user.posts.group("date(created_at)").select(:created_at)
    else
      dates_calorie = @sampleuser.posts.group("date(created_at)").select(:created_at)
    end
    
    gon.date = []
    @dates = dates_calorie.map{ |dates| dates.created_at}⬅️ I got an error in this part
    #Take out and change the date notation one by one in each statement
    @dates.each do |a|
      gon.date << a.strftime("%Y year%m month%d day")
    end
    binding.pry

    #body weight
    if user_signed_in?
      gon.weight = current_user.posts.group("date(created_at)").select(:weight).map{ |weight| weight[:weight]}
    else
      gon.weight = @sampleuser.posts.group("date(created_at)").select(:weight).map{ |weight| weight[:weight]}
    end
  end
end

Solution procedure

Edit the MySQL configuration file my.cnf.

vi /etc/my.cnf 

Open my.cnf with Added the following code.

/etc/my.cnf

[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

Since my.cnf is a read-only file, it could not be overwritten with shift + ZZ or : wq!, Which normally overwrites vim.

To overwrite the read-only file, enter the following

:w !sudo tee %

Then close the vim file below.

:q!

After editing my.cnf and changing the MySQL settings, restart MySQL for the changes to take effect.

service mysqld restart

Why was it solved

Cause of this error

Since MySQl5.7, ONLY_FULL_GROUP_BY is set in sql_mode if it is left as default, so it seems that this error will occur if it is not unique.

As a proper method, it is necessary to specify the column to be acquired in the GroupBy clause or modify it so that it is unique in MAX, MIN, SUM, etc. so that SQL can be identified properly and uniquely.

Source: I got an error with only_full_group_by after updating to MySQL 5.7

This time the error occurred in the following part of charts_controller.rb.

charts_controller.rb

@dates = dates_calorie.map{ |dates| dates.created_at}

The variable dates_calorie contains the posts of current_user grouped by date, the created_at column is fetched and put in an array as shown below.

charts_controller.rb

dates_calorie = current_user.posts.group("date(created_at)").select(:created_at)

If there are multiple posts on the same day, they are grouped together by .group ("date (created_at) "), so there can be no multiple posts with the same date and they should be unique. I don't understand why you are told to "correct it to be unique".

In fact, it works fine in my local environment.

I found a reference article that turns off the default automatic setting ** ONLY_FULL_GROUP_BY ** and the error disappears, and when I run it, the error at the beginning disappears.

Reference article

-I got an error with only_full_group_by after updating to MySQL 5.7 -Edit read-only file -Start MySQL