Shulou(Shulou.com)05/31 Report--

Today, the editor will share with you the relevant knowledge about how to solve the problems caused by sql mode setting. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

1 the error is similar to the following

Database error: Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The error may exist in URL [jarvav fileVlGUSERGUR USERGUR USARULARUGULAR localLex.BOOTRAN BOTRAINFUBUBUBUBUBUBUBUR 4.6.0.JARREGULAR Mapper.xml]

The error may involve com.ruoyi.system.mapper.SuOrgProgramMapper.selectSuOrgProgramList-Inline

The error occurred while setting parameters

SQL: select sop.*,so.org_name as orgName,sa.activity_name as activityName,sa.activity_name_en as activityNameEN, ss.student_id as matchedStudentId,ss.name as matchedStudentName, su.login_name as email, su.real_name AS firstName Su.nickname as nickname from su_org_program sop inner join su_activity sa on sop.activity_id=sa.activity_id inner join su_org so on so.org_id = sop.org_id left JOIN sys_user su ON sop.main_teacher_id = su.sub left join (select * from su_student_program where is_matched=1) ssp on ssp.org_program_id= sop.org_program_id left JOIN su_student ss on ssp.student_id=ss.student_id WHERE sop.org_id =? GROUP BY sop.org_program_id

Cause: java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\ n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression 39 of SELECT list is not in GROUP BY clause and contains nonaggregated column' test.ss.student_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2 workaround 2.1 View global sql mode

Use the following m command:

Select @ @ global.sql_mode

As follows:

Mysql > select @ @ global.sql_mode +- -+ | @ @ global.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 | +- -+ 1 row in set (0.00 sec) 2.2 set global sql mode

Use the following command:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

As follows:

Ysql > SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > 2.3 View current sql mode

Use the following command:

Select @ @ sql_mode

As follows:

Mysql > select @ @ sql_mode +- -+ | @ @ sql_mode | +-- -+ | STRICT_TRANS_TABLES NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION | +- -+ 1 row in set (0.00 sec) mysql > 2.4 sets the current sql mode

Use the following command:

Set @ @ sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

As follows:

Mysql > set @ @ sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > 3 permanent solution 3.1 find the my.cnf file

You can find it using the following command, usually under / etc/ or / etc/mysql/ directory

Find / etc/-name my.cnf3.2 Editing the my.cnf file vi / etc/mysql/my.cnf

Then add the following:

Sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION3.3 restart database service systemctl restart mysqld above is all the content of this article "how to solve the problems caused by sql mode setting". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.