(Reprinted from: http://blog.csdn.net/wangyunfeis/article/details/77911704)
Mysql Only_full_group_by and other detailed solutions for Sql_mode cause error
- Error message:
- Reason:
- How to view it:
- Workaround 1:
- Verify that:
- Solution 2
- Solution 3
- Explanation for Solution 3
- Description of 32 Methods of approach 2
- Attach other mode explanations
- For other mode instructions
Error message:1.Error querying database. Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:In aggregated query without GROUP by, expression #1 of SELECT list contains nonaggregated column ' 15kong_smartmeter.meter_reading_realtime_day.room_id '; this was incompatible with sql_mode=only_full_group_byReason:1. The value of Sql_mode in MySQL 5.7 is: 2.only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_ Division_by_zero,no_auto_create_user,no_engine_substitution How to view it:1. My mysql5. 7 deployment on Linux, then I use navicat 12 connection, say how to use navicat 12 to view it 2. Open the database using Navicat, and then the command-line interface (shortcut F6) 3. Then enter:SELECT @@ sql_mode; 4. See the value:5.only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_ Division_by_zero,no_auto_create_user,no_engine_substitution Workaround 1:1. The MySQL runtime I am using specifies the following configuration:2./apps/mysql5. 718/bin/mysqld--defaults-file=/apps/my3306.cnf--basedir=/apps/mysql5. 7. 18- -datadir=/apps/mysql/data/3306--plugin-dir=/apps/mysql5. 7. 18/lib/mysql/plugin--user=mysql-- log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err--open-files-limit=8192--pid-file=/apps/ mysql/data/3306/BJ-DYC-VM-5-106.pid--socket=/tmp/mysql_3306.sock--port=3306 so I changed the/APPS/MY3306.CNF, you have no idea hahaFind:Sql_mode = "Only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_ Zero,no_auto_create_user,no_engine_substitution " modified to:Sql_mode = "Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_ User,no_engine_substitution " restart MySQL because the MySQL configuration file was modified/apps/mysql5. 718/bin/mysqld--defaults-file=/apps/my3306.cnf--basedir=/apps/mysql5. 7. 18-- datadir=/apps/mysql/data/3306--plugin-dir=/apps/mysql5. 7. 18/lib/mysql/plugin--user=mysql-- log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err--open-files-limit=8192--pid-file=/apps/ mysql/data/3306/BJ-DYC-VM-5-106.pid--socket=/tmp/mysql_3306.sock--port=3306 Verify that:1. Go to the command-line interface2. Then enter: SELECT @ @sql_mode;3. See the value:Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_ SUBSTITUTION 6. At this point, you do not have to obey: The following conventions:7. For GROUP by aggregation operations, if the column in select does not appear in group BY, then this SQL is not legal because the column is not in the group by clause. So for the database with this mode, when using group by, it is necessary to use the aggregate function of Max (), SUM (), Ant_value () to complete The aggregation operation of GROUP by. Solution 21. The second method does not need to modify the configuration file, using Navicat to modify2. Go to the Command line interface (F6)3. Input:SELECT @ @GLOBAL. sql_mode; 4. Result: Only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_ Auto_create_user,no_engine_substitution 6. Then we'll modify the Sql_mode7. Input:set GLOBAL sql_mode =' strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by _zero,no_auto_create_user,no_engine_substitution '; 9. Run your SQL again:Ten.SELECT id,ip .. From Ip_meta_backup.GROUP by. IPA . having. count (IP) > 1 18.***** Congratulations, you've succeeded. * * * 22. Then you run your SQL is not ready to run SET GLOBAL sql_mode =' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_ Zero,no_auto_create_user,no_engine_substitution '; .SELECT @ @GLOBAL. sql_mode; Solution 31. The third method does not need to modify the configuration file, using Navicat to modify2. Go to the Command line interface (F6)3. Input:SELECT @ @sql_mode; Note: This is the default SESSION, complete:select @ @SESSION. sql_mode; 4. Result: Only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_ Auto_create_user,no_engine_substitution 6. Then we'll modify the Sql_mode7. Input:set SESSION sql_mode =' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_ By_zero,no_auto_create_user,no_engine_substitution '; 9. Run your SQL again:Ten.SELECT id,ip .. From Ip_meta_backup.GROUP by. IPA . having. count (IP) > 1 18.***** still reported only_full_group_by wrong * * * * * 20.############# #解决方法 ################### 22. Before you query the statement set Sql_mode Set SESSION sql_mode =' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_ Zero,no_auto_create_user,no_engine_substitution '; .SELECT id,ip .. From Ip_meta_backup.GROUP byIPA . having count (IP) > 1 33. Create a new query (with GROUP by) in the future without adding set Sql_mode until you close the connection . 38. Then you run your SQL is not ready to run SET GLOBAL sql_mode =' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_ Zero,no_auto_create_user,no_engine_substitution '; A .SELECT @ @GLOBAL. sql_mode; Explanation for Solution 31. In fact, the third method of the time said SELECT @ @sql_mode; in fact, this is the default SESSION, the complete is:select @ @SESSION. sql_mode; 2.SESSION is the meaning of the current session ----> This sentence will explain until you close the connection. 4. Why is it useless to set sql_mode on the command line and then create a new query? 5. In fact, you just enter SELECT @ @sql_mode in the new query ; you'll find that your settings on the command line are not in effect .6. Therefore, we can only re-set sql_mode in the new query of this connection (session) , and then this connection (this session) no longer needs 7. This passage explains the phrase " set Sql_mode before you query the statement"Description of 32 Methods of approach 21. Both of the set Sql_mode methods will revert to my specified configuration file with the MySQL reboot on my Linux deployment. The contents of the sql-mode option set in CNF 2. This statement is more around3. The meaning is: after MySQL restarts on Linux, you are no longer valid on navicat, but according to the settings in the configuration file you specified, that is my. CNF is set in this file Attach other mode explanations1.strict_trans_tables: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restriction is made on the non-transactional table 3.no_zero_in_date: In strict mode, the date and month are not allowed to zero 5.no_zero_date: Setting this value, the MySQL database does not allow the insertion of the 0 period, and inserting the 0 period throws an error instead of a warning. 7.error_for_division_by_zero: During an insert or update process, if the data is removed by 0, an error is generated instead of a warning. If the pattern is not given, then MySQL returns NULL when the data is removed by 0 9.no_auto_create_user: Disallow grant to create a user with a blank password 11.no_engine_substitution: Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is substituted and an exception is thrown 13.pipes_as_concat: Will "| |" The concatenation operator, rather than the operator, that is treated as a string, which is the same as the Oracle database, and similar to the concatenation function concat of the string. 15.ansi_quotes: You cannot use double quotation marks to reference a string after Ansi_quotes is enabled because it is interpreted as a qualifier 17.no_auto_value_on_zero: This value affects the insertion of the self-growing column. By default, inserting 0 or null represents the generation of the next self-growth value. This option is useful if the user wants to insert a value of 0 and the column is self-growing. For other mode instructions1. Other mode uses have no changes to the configuration file, or independent changes (that is, methods 2 and 3)
Mysql Only_full_group_by and other detailed solutions for Sql_mode cause error
