[RAILS] An error occurred because only_full_group_by of sql_mode was not observed.

problem

When I tried to display the graph, the following error was displayed.

Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pfc-master_production.posts.created_at' which is 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` = 8 GROUP BY date(created_at)

charts_controller.rb

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

Read the error log

Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and 
Mysql2 ::Error: Expression # 1 in the SELECT list is not in the GROUP BY clause

contains nonaggregated column 'pfc-master_production.posts.created_at' 
which is not functionally dependent on columns in GROUP BY clause;
Non-aggregate columns that are not functionally dependent on the columns in the GROUP BY clause
'pfc-master_production.posts.created_at'contained

this is incompatible with sql_mode=only_full_group_by: 
This is sql_mode = only_full_group_Not compatible with by

SELECT SUM(`posts`.`calorie`) AS sum_calorie, date(created_at) AS date_created_at FROM `posts` WHERE `posts`.`user_id` = 8 GROUP BY date(created_at) ORDER BY created_at DESC

** What is the map method? ** **

・ In a word, the map method is "a method that executes processing for each element in order". -Access each element and perform the instructed processing. [Introduction to Rails] Completely capture the map method! Learn the basics of array operations

Array variables.map {|Variable name|Specific processing}

In this case
@dates = dates_calorie.map(Array variables) { |dates(Variable name)| dates.created_at(processing) }

** What's in the array dates_calorie? ** **

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

If there are multiple posts by the logged-in user in a day, the ones that are grouped by day and get the created_at column are included. That is, one created_at column is acquired per day, and the created_at column information for each day is included in the array.

The reason for getting this information is to implement a graph that shows your daily weight. This is because when there are multiple posts per day, I want to display only one per day on the graph.

✖️ [2020-11-09 00:00:00, 2020-11-09 00:11:00, 2020-11-10 00:12:00] ○ [2020-11-09 00:00:00, 2020-11-10 00:12:00, 2020-11-11 00:12:00]

** What kind of processing are you doing using the map method? ** **

@dates = dates_calorie.map(Array variables) { |dates(Variable name)| dates.created_at(processing) }

As explained above, the array dates_calorie contains daily created_ats in the array, which you can use the map method to put each element in the array into a variable called dates. Take it out and get created_at.

I'm assigning them to instance variables so that I can pass them to chart.js, which displays the graph with gon, and use them in the graph.

Why did you get an error this time?

I didn't know only_full_group_by, so I looked up only_full_group_by in the reference.

** What is sql_mode = only_full_group_by? ** **

Reject queries referenced by select lists, HAVING conditions, or (since MySQL 5.6.5) ORDER lists for non-aggregated columns that are not named in the GROUP BY clause.

If> ONLY_FULL_GROUP_BY is in effect, the following query is invalid. The first is that the non-aggregated address column in the picklist is not named in the GROUP BY clause, and the second is invalid because max_age in the HAVING clause is not named in the GROUP BY clause. Become.

What is MySQL Reference ONLY_FULL_GROUP_BY?

sql_mode is like the official rule of MySQL, and only_full_group_by seems to be a rule newly set in MySQL 5.6.5 or later.

-It is probable that an error occurred because the rule of only_full_group_by was not followed. -It is probable that an error occurred because the name was not specified in the GROUP BY clause.

** What is a GROUP BY clause? ** **

-"GROUP BY" is an instruction used to perform grouping. -In many cases, it is mainly used in the form of "using an aggregate function for each type". ・ For example, "Check the number of people in each team". [SQL] Aggregate freely with GROUP BY! Use it in combination with aggregate functions and HAVING

select [Element name to display] from [table name] GROUP BY [Element name to group];
SELECT team, COUNT(team) FROM user GROUP BY team;

Countermeasures

From the above, there are two possible countermeasures.

(1) Change to a code that uses the GROUP BY clause in order to comply with SQL mode only_full_group_by. (2) Change my.cnf (MySQL configuration file) and remove the restriction of only_full_group_by.

This time, I chose method (2). The reason is that I don't have time.

Now that I'm starting to change jobs to a web engineer, I need to be able to show the app to the hiring manager of the company as soon as possible. Actually, ① is desirable, but this time I chose ② because time is prioritized.

This solution

my.Find the location of conf
[naota@ip-10-0-0-32 ~]$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

my.Open conf
[naota@ip-10-0-0-32 ~]$ vi /etc/my.cnf

Used when overwriting a read-only file
:w !sudo tee %

The following description was added to my.conf.

my.conf

[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

I looked into sql_modes such as NO_ZERO_IN_DATE and NO_ZERO_DATE and found that there was no point in turning off ʻonly_full_group_by on their own. Probably, it is not set to ʻonly_full_group_by in the above setting, but it is not set because it is not written including ʻonly_full_group_by`.

→ ʻonly_full_group_by` This hypothesis was correct because the error at the beginning occurred when I added it.

I restarted MySQL for the above settings to take effect.

I got an error with only_full_group_by after updating to MySQL 5.7

Stop MySQL
[naota@ip-10-0-0-32 ~]$ sudo systemctl stop mysqld.service

Start MySQL
[naota@ip-10-0-0-32 ~]$ sudo systemctl start mysqld.service

The graph is displayed!

Source code

charts_controller.rb is a controller for displaying graphs.

charts_controller.rb

def index
    #calorie
    @sampleuser = User.find_by(id: 3)
    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 }

    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 }
    #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


    #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