The Elearning Community • SQL Error when entering "Teacher's area"
Page 1 of 1

SQL Error when entering "Teacher's area"

Posted: Fri Oct 26, 2018 10:56 am
by 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

Re: SQL Error when entering "Teacher's area"

Posted: Fri Oct 26, 2018 1:30 pm
by alfa24
Can't reproduce the error. Would you provide a demo access to investigate (with a Private Message)?

Re: SQL Error when entering "Teacher's area"

Posted: Fri Oct 26, 2018 3:49 pm
by 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"

Re: SQL Error when entering "Teacher's area"

Posted: Sun Oct 28, 2018 10:18 am
by 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

Re: SQL Error when entering "Teacher's area"

Posted: Mon Nov 04, 2019 2:06 pm
by alfa24
sql_mode not sql-mode

Re: SQL Error when entering "Teacher's area"

Posted: Mon Nov 04, 2019 11:08 pm
by max
alfa24 wrote:
Mon Nov 04, 2019 2:06 pm
sql_mode not sql-mode
Fixed anellic's original piece of code, thank you

This site uses cookies.

Some of the cookies we use are essential for parts of the site to operate and have already been set. We also use Google Analytics scripts, which all use cookies.
You may delete or block all cookies from this site in your browser options.