Manage Course breaks (rails-5-upgrade; mysql 5.7.28)

[wallaceswsu]

Pressing the "Manage Course" button in the navbar on the rails-5 branch throws an error:

     Error Information

--- !ruby/exception:ActiveRecord::StatementInvalid
message: 'Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, references
  column ''autolab_production.assessments.due_at'' which is not in SELECT list; this
  is incompatible with DISTINCT: SELECT DISTINCT category_name FROM `assessments`
  WHERE `assessments`.`course_id` = 4 ORDER BY due_at ASC, name ASC'

This appears to be tied to the grade book.

Although this can be solved with a change to the Autolab/config/database.yml file, it seems like a better solution would be to modify the queries involved so there are no ambiguities.

Environment:

• Autolab rails-5-upgrade (b9a70d)
• rails production environment
• Ubuntu 18.04.3
• mysql:
  • mysql-server-5.7/bionic-updates,bionic-security,now 5.7.28-0ubuntu0.18.04.4 amd64
  • mysql-server-core-5.7/bionic-updates,bionic-security,now 5.7.28-0ubuntu0.18.04.4 amd64

Steps to reproduce behavior:

• create a new course
• press Manage Course in nav bar
• Error will be thrown, however re-pressing 'Manage Course' will subsequently work.
• Add a student to the course (since Manage Course is working)
• Click Gradebook in nav bar and refresh the gradebook (right side of table)
• Error will be thrown again
• At this point, both the Gradebook and Manage Course buttons in the nav bar will continuously throw the error

Working Solution:

• modify Autolab/config/database.yml so that all mysql2 adapters include:

  variables:
  sql_mode: TRADITIONAL

Working Solution: (alternate)

• modify /etc/mysql/mysql.conf.d/mysql.cnf with the line: sql-mode = "" in the mysqld block
  • restart mysqld
  • restart apache

Leads:

• https://github.com/rails/rails/issues/25924
• https://gabi.dev/2016/03/03/group-by-are-you-sure-you-know-it/
• https://stackoverflow.com/questions/36829911/how-to-resolve-order-by-clause-is-not-in-select-list-caused-mysql-5-7-with-sel/39353160
• https://gitlab.com/gitlab-org/gitlab-foss/merge_requests/20256

[victorhuangwq]

Hi @wallaceswsu May I know if this issue has already been fixed in the current master? If so I will proceed to close this issue.

[wallaceswsu]

@victorhuangwq It doesn't appear to be fixed as of 0c68d9f (which is my most recent pull from your repo -- (June 14)).

Environment

• Autolab 0c68d9f
• Rails development environment
• Ubuntu 18.04.3
• mysql
  • mysql-server-5.7/bionic-updates,bionic-security,now 5.7.30-0ubuntu0.18.04.1 amd64
  • mysql-server-core-5.7/bionic-updates,bionic-security,now 5.7.30-0ubuntu0.18.04.1 amd64

Behavior

• Install autolab in configuration above
• run rake autolab:populate this will fail with the following result

>rake autolab:populate
Creating Course AutoPopulated and config file
Creating Assessments
Creating Problems
Fast-creating Users
Fast-creating Submissions and Scores (might take a while)
Fast-creating AUDs
rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'autolab_development.submissions.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT `submissions`.* FROM `submissions` WHERE `submissions`.`assessment_id` = 1 GROUP BY `submissions`.`course_user_datum_id` HAVING (MAX(submissions.version))
...
Caused by:
Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'autolab_development.submissions.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• add variables block to database.yml as documented in Working Solution above.
• run rake db:create followed by rake autolab:populate. This now succeeds
• log in, look at gradebook
• remove variables block from database.yml
• restart apache
• view gradebook now throws an exception:

Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'autolab_development.assessments.due_at' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT category_name FROM `assessments` WHERE `assessments`.`course_id` = 1 ORDER BY due_at ASC, name ASC 

• put back the variables block, restart apache. All is fixed.

[wallaceswsu]

I also verified the problem still exists in the latest master: bbfb72f

[victorhuangwq]

@wallaceswsu Our autolab.ml site uses MySQL for its database, and I was wondering why it does not have the issue that has mentioned. I checked the sql_mode parameters and see that the only value set is NO_ENGINE_SUBSTITUTION.

Because of this, I'm more inclined towards the alternate solution that you mentioned earlier which is to set, sql-mode = "", which by MySQL documentation, clears out the modes set. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

The specific queries themselves are executed by an abstraction layer in rails, which might be worth investigating in the future, but the abstraction layer is meant to help us be cross compatible with different databases as well.

I have made a patch here at https://github.com/autolab/Autolab/pull/1194. Let me know if this works for you?

[victorhuangwq]

If the patch works I will proceed to add the settings into the documentation as well

[wallaceswsu]

Thanks for the follow up! I looked into this a bit further here are a few thoughts:

• yes, your patch works and setting sql_mode: '' seems more reasonable than my initial suggestion of TRADITIONAL
• the mode ONLY_FULL_GROUP_BY, seems to be causing the issue (i.e., if I set it explicitly in the database.yml file, I get an exception); and this was added to mysql relatively recently
• NO_ENGINE_SUBSTITUTION has been a default for much longer, and if I set

   variables:
     sql_mode: NO_ENGINE_SUBSTITUTION

  I don't get an exception.

Perhaps adding a comment about this (or setting NO_ENGINE_SUBSTITUTION) in the config is appropriate?

[victorhuangwq]

@wallaceswsu Yes, as it seems, NO_ENGINE_SUBSTITUTION was the default prior to MySQL 5.7, and it works for autolab.ml. I think it would be better to set that instead. Thank you for testing it out as well.

Edit: Updated as such in the documentation as well

[fanpu]

FYI: I'm not able to replicate this issue on 10.4.13-MariaDB on default configuration, so I guess there are differences between the two