SQL Error when entering "Teacher's area"

[PeterP]

Hi!

I am evaluating different open source LMS. I really like the look and feel of Forma but I get stuck at the point providing content. Had no problems with the install procedure.

MySQL information

sql vers: 5.7.24-0ubuntu0.16.04.1
sql mode: ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql encoding: utf8
sql collation: utf8_general_ci
sql timezone: SYSTEM


Info about PHP

PHP version: 7.0.32-0ubuntu0.16.04.1
Site Timezone: Europe/Berlin
"safe_mode" configuration: Off
Configuration of "register_global": Off
Configuration of "magic_quotes_gpc": Off
Configuration of "upload_max_filesize": 16M
Configuration of "post_max_size": 16M
Configuration of "max_execution_time": 60s
LDAP: Off Consider this parameter only if you need to authenticate through LDAP
Configuration of "allow_url_include": Off
PHP extension php_openssl: On
Configuration of "allow_url_fopen": On

Whenever I want to enter the "Teachers's area" I get the following error:

Expression #1 of ORDER BY clause is not in SELECT list, references column 'formalms.learning_organization.path' which is not in SELECT list; this is incompatible with DISTINCT [ SELECT DISTINCT learning_organization.idOrg FROM learning_organization WHERE ((learning_organization.idParent = '0') AND (learning_organization.idCourse = '1')) ORDER BY learning_organization.path ]

I reinstalled Forma for 2 times from the scratch and wanted just to make a basic test: One course, everything on default.

Can anyone help? What further infos from my side would help?

Best regards
Peter

[alfa24]

Can't reproduce the error. Would you provide a demo access to investigate (with a Private Message)?

[canelli]

the error you get depends from msyql version 5.7.x

Mysql 5.7.x introduced many changes , one of this is the behavior of aggregate functions. as reported in mysql 5.7 documentation

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

this change affects also "SELECT DISTINCT" statement

to fix the issue , try change the sql_mode setting in the my.cnf file removing the option ONLY_FULL_GROUP_BY
find row

Code: Select all

sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and change as

Code: Select all

sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[PeterP]

Hi guys,

thank you for reply. As this is a multi domain server I have to ask If I can change those settings without disturbing the other things around. There should be something like a my.cnf for my webserver only. I'll find out an post the results.

Regards,
Peter

[alfa24]

sql_mode not sql-mode

[max]

sql_mode not sql-mode

Fixed anellic's original piece of code, thank you