There's not many of them, and there's a race condition where this script deletes
a dir at the same time the rails page cache wants to write a new file to it,
leading to 500s.
This is the error I'm seeing [in the latest commit](5338e5a4b3)
Seems to be related to a MySQL flag `sql_mode=only_full_group_by` enabled by default.
```ruby
(byebug) Comment.where(user: user).group(:thread_id).order("id desc").limit(20).pluck(:thread_id)
*** ActiveRecord::StatementInvalid Exception: Mysql2::Error:
Expression #1 of ORDER BY clause is not in GROUP BY clause and
contains nonaggregated column 'lobsters_test.comments.id' which is not
functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
nil
```
I don't think is a good idea disabling that flag, so, I just updated the query.
Old query
```ruby
(byebug) Comment.where(user: user).group(:thread_id).order("id desc").limit(20)
(byebug) Comment.where(user: user).group(:thread_id).order("id desc").limit(20).to_sql
"SELECT `comments`.* FROM `comments` WHERE `comments`.`user_id` = 1 GROUP BY `comments`.`thread_id` ORDER BY id desc LIMIT 20"
```
New query
```ruby
(byebug) Comment.where(user: user).select(:thread_id).order("id desc").limit(20)
(byebug) Comment.where(user: user).select(:thread_id).order("id desc").limit(20).to_sql
"SELECT `comments`.`thread_id` FROM `comments` WHERE `comments`.`user_id` = 1 ORDER BY id desc LIMIT 20"
(byebug) Comment.where(user: user).select(:thread_id).order("id desc").limit(20).pluck(:thread_id)
[1]
```
```mysql
mysql> explain SELECT DISTINCT `comments`.* FROM `comments` WHERE `comments`.`user_id` = 1 ORDER BY `comments`.`thread_id` DESC LIMIT 20;
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | comments | NULL | ref | index_comments_on_user_id | index_comments_on_user_id | 8 | const | 1 | 100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain SELECT comments.* FROM comments WHERE comments.user_id = 1 GROUP BY comments.thread_id ORDER BY comments.thread_id DESC LIMIT 20;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lobsters_dev.comments.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```
Links:
* https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc
* https://stackoverflow.com/questions/54547279/mysql-contains-nonaggregated-column-error-when-using-group-rails-5-2
* https://gitlab.com/gitlab-org/gitlab-foss/-/issues/48428
Writes a vote directly to avoid vote_thusly doing round trips to check if one
exists, etc.
Removes redundant transactions from controllers from #899. Rails already creates
a transaction for the .save.
Unifies Story cache updating. Previously recalculate_hotness! was called twice
on comment creation. Moves comment counting into the db.
Shorter transaction should reduce the frequence of
lobsters/lobsters-ansible/issues/39 but seems unlikely to eliminate it as the
create + upvote transactions for stories + comments still read/write from
stories, comments, and votes.
Ben Harris
Kevin Nobel
Peter Bhat Harkins
Tiago Ilieve
Peter Bhat Harkins
Cristian Guerrero
Micah Magruder
Robey Pointer
Joan Nyambura
Juan Vasquez
thiagofportella