Default sql mode in mysql

Today i was writing a very basic select query with aggregate function. The query was:

SELECT max(id) FROM `users` GROUP by username order by last_name

It seems to be correct but surprised me when i got an unexpected error.

#1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#1140 – In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column. this is incompatible with sql_mode=only_full_group_by

When try to fix the error, i learnt about SQL Modes and here i am sharing the same. First i am covering the error and the solution for that. After that we will take a brief idea about sql modes.

This type of error is thrown when ONLY_FULL_GROUP_BY sql mode is enabled (which it is by default).

According to mysql official documentation “If the ONLY_FULL_GROUP_BY SQL mode is enabled, MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are
neither named in the GROUP BY clause nor are functionally dependent on them.”

Confused… let me explain this by some examples:

  • If you write a query with a select list and didn’t use selected nonaggregated columns in GROUP BY list
  • If you write a query with HAVING condition and columns used in HAVING clause are not in GROUP BY list
  • If you write a query with ORDER BY, and columns used to sort in ORDER BY are not in GROUP BY list

Let’s assume a table `users` with columns id, username, first_name, last_name. Here are some example queries which are correct but not fullfils ONLY_FULL_GROUP_BY criteria.

Example 1: Following query will throw error as ORDER BY last_name is not in GROUP BY list

SELECT max(id) FROM `users` GROUP by username order by last_name

If you modify the query and write is as shown below, it will be executed without any error.

SELECT max(id) FROM `users` order by last_name
OR
SELECT max(id) FROM `users` GROUP by last_name order by last_name

Example 2: Following queries are incorrect as column first_name in select list is not in GROUP BY list

SELECT max(id),first_name FROM `users` GROUP BY last_name

SELECT max(id),first_name FROM `users`

Example 3: Incorrect as column first_name in select list and in having clause is not in GROUP BY list

SELECT max(id),first_name FROM `users` HAVING first_name like '%test%'

Correct:

SELECT max(id),first_name FROM `users` GROUP by first_name HAVING first_name like '%test%'

SELECT max(id) FROM `users` GROUP by first_name HAVING first_name like '%test%'

Fix Error:

To ignore this type of errors, either you can write a query which fulfills ONLY_FULL_GROUP_BY conditions or alternatively you can disable ONLY_FULL_GROUP_BY in sql_mode by removing ONLY_FULL_GROUP_BY from sql_mode. Before disable sql_mode, you should check existing enabled sql modes to ensure that only specific one is disabled. So this is a two step approach, first check existing sql modes and than disable whichever you want.

Step 1: check existing sql_mode

The default SQL mode in MySQL 8.0 includes these modes: ​ ONLY_FULL_GROUP_BY​ , STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , and ​ NO_ENGINE_SUBSTITUTION

These sql modes are saved in a sql_mode system variable. To determine the current global or session sql_mode you can execute a select query:

select @@global.sql_mode;
select @@session.sql_mode;

Step 2:​​ ​ Disable sql_mode

To disable sql mode, you have to remove that mode from sql_mode system variable. To change the SQL mode at runtime, you can set the global or session ​ sql_mode​ system variable using a ​ SET​ statement. Let’s assume that select @@global.sql_mode; returns ONLY_FULL_GROUP_BY​ , STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION and you want to disable ONLY_FULL_GROUP_BY then you can disable it by removing this from sql_mode system variable as shown below:

set global sql_mode='STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION';

You can disable any sql_mode whichever you want. Or if you want to disable all sql_modes you can set it to blank as shown in query below:

set global sql_mode='';

set session sql_mode='';

Note: This change in sql_mode is temporary. If you restart the server or mysql service, it’s default values will be set again and every time when your service is restarted, you have to reset sql_mode variable.

If you want to disable sql mode permanently, you can set sql_mode variable in mysql configuration /etc/mysql/mysql.conf.d file. Add following mode below [mysqld]

sql_mode='STRICT_TRANS_TABLES​ , ​ NO_ZERO_IN_DATE​ , ​ NO_ZERO_DATE​ , ERROR_FOR_DIVISION_BY_ZERO​ , NO_ENGINE_SUBSTITUTION'

or set it to blank to disable all modes.

Sql mode

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top