Social: "Failed to load more timeline entries" on timeline.

[jkolo]

Describe the bug
"Failed to load more timeline entries" on timeline.

To Reproduce
Steps to reproduce the behavior:

1. Go to social app
2. See error

Expected behavior
Posts are displayed on timeline view

Client details:

• OS: Arch Linux
• Browser Firefox
• Version 67.0.4
• Device: laptop

Server details

Social app version:
2.5

Operating system:
Linux

Web server:
nginx

Database:
PostgreSQL

PHP version:
7.3

Nextcloud version: (see Nextcloud admin page)
16.0.1

Logs

Nextcloud log (data/nextcloud.log)

{
  "reqId": "NxmhBzmHxrXrArXOBjZe",
  "level": 2,
  "time": "2019-06-23T21:46:42+02:00",
  "remoteAddr": "80.238.125.162",
  "user": "jurek",
  "app": "no app in context",
  "method": "GET",
  "url": "/apps/social/api/v1/stream/home?limit=25&since=1561319199",
  "message": "500 - {\"status\":-1,\"exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"message\":\"An exception occurred while executing 'SELECT DISTINCT \\\"s\\\".\\\"id\\\", \\\"s\\\".\\\"type\\\", \\\"s\\\".\\\"to\\\", \\\"s\\\".\\\"to_array\\\", \\\"s\\\".\\\"cc\\\", \\\"s\\\".\\\"bcc\\\", \\\"s\\\".\\\"content\\\", \\\"s\\\".\\\"summary\\\", \\\"s\\\".\\\"attachments\\\", \\\"s\\\".\\\"published\\\", \\\"s\\\".\\\"published_time\\\", \\\"s\\\".\\\"cache\\\", \\\"s\\\".\\\"object_id\\\", \\\"s\\\".\\\"attributed_to\\\", \\\"s\\\".\\\"in_reply_to\\\", \\\"s\\\".\\\"source\\\", \\\"s\\\".\\\"local\\\", \\\"s\\\".\\\"instances\\\", \\\"s\\\".\\\"creation\\\", \\\"s\\\".\\\"hidden_on_timeline\\\", \\\"f\\\".\\\"object_id\\\" AS \\\"following_actor_id\\\", \\\"ca\\\".\\\"id\\\" AS \\\"cacheactor_id\\\", \\\"ca\\\".\\\"type\\\" AS \\\"cacheactor_type\\\", \\\"ca\\\".\\\"account\\\" AS \\\"cacheactor_account\\\", \\\"ca\\\".\\\"following\\\" AS \\\"cacheactor_following\\\", \\\"ca\\\".\\\"followers\\\" AS \\\"cacheactor_followers\\\", \\\"ca\\\".\\\"inbox\\\" AS \\\"cacheactor_inbox\\\", \\\"ca\\\".\\\"shared_inbox\\\" AS \\\"cacheactor_shared_inbox\\\", \\\"ca\\\".\\\"outbox\\\" AS \\\"cacheactor_outbox\\\", \\\"ca\\\".\\\"featured\\\" AS \\\"cacheactor_featured\\\", \\\"ca\\\".\\\"url\\\" AS \\\"cacheactor_url\\\", \\\"ca\\\".\\\"preferred_username\\\" AS \\\"cacheactor_preferred_username\\\", \\\"ca\\\".\\\"name\\\" AS \\\"cacheactor_name\\\", \\\"ca\\\".\\\"summary\\\" AS \\\"cacheactor_summary\\\", \\\"ca\\\".\\\"public_key\\\" AS \\\"cacheactor_public_key\\\", \\\"ca\\\".\\\"source\\\" AS \\\"cacheactor_source\\\", \\\"ca\\\".\\\"creation\\\" AS \\\"cacheactor_creation\\\", \\\"ca\\\".\\\"local\\\" AS \\\"cacheactor_local\\\", \\\"sa\\\".\\\"id\\\" AS \\\"streamaction_id\\\", \\\"sa\\\".\\\"actor_id\\\" AS \\\"streamaction_actor_id\\\", \\\"sa\\\".\\\"stream_id\\\" AS \\\"streamaction_stream_id\\\", \\\"sa\\\".\\\"values\\\" AS \\\"streamaction_values\\\" FROM \\\"oc_social_a2_stream\\\" \\\"s\\\" INNER JOIN \\\"oc_social_a2_follows\\\" \\\"f\\\" ON ((\\\"to\\\" = ?) OR (\\\"to_array\\\" ILIKE ?) OR (\\\"cc\\\" ILIKE ?) OR (\\\"bcc\\\" ILIKE ?)) OR (((LOWER(\\\"s\\\".\\\"to\\\") = LOWER(\\\"f\\\".\\\"follow_id\\\")) OR (\\\"s\\\".\\\"to_array\\\" ILIKE (? || (\\\"f\\\".\\\"follow_id\\\" || ?))) OR (\\\"s\\\".\\\"cc\\\" ILIKE (? || (\\\"f\\\".\\\"follow_id\\\" || ?))) OR (\\\"s\\\".\\\"bcc\\\" ILIKE (? || (\\\"f\\\".\\\"follow_id\\\" || ?)))) AND (LOWER(\\\"f\\\".\\\"actor_id\\\") = LOWER(?)) AND (\\\"f\\\".\\\"accepted\\\" = ?)) LEFT JOIN \\\"oc_social_a2_cache_actors\\\" \\\"ca\\\" ON LOWER(\\\"f\\\".\\\"object_id\\\") = LOWER(\\\"ca\\\".\\\"id\\\") LEFT JOIN \\\"oc_social_a2_stream_action\\\" \\\"sa\\\" ON ((LOWER(\\\"s\\\".\\\"id\\\") = LOWER(\\\"sa\\\".\\\"stream_id\\\")) OR (LOWER(\\\"s\\\".\\\"object_id\\\") = LOWER(\\\"sa\\\".\\\"stream_id\\\"))) AND (LOWER(\\\"sa\\\".\\\"actor_id\\\") = ?) LEFT JOIN \\\"oc_social_a2_follows\\\" \\\"fs\\\" ON (LOWER(\\\"fs\\\".\\\"actor_id\\\") = LOWER(?)) AND (LOWER(\\\"s\\\".\\\"attributed_to\\\") = LOWER(\\\"fs\\\".\\\"object_id\\\")) AND (\\\"fs\\\".\\\"accepted\\\" = ?) WHERE (\\\"s\\\".\\\"published_time\\\" <= ?) AND ((\\\"s\\\".\\\"hidden_on_timeline\\\" = ?) OR ((LOWER(\\\"attributed_to\\\") <> LOWER(?)) AND (\\\"fs\\\".\\\"id\\\" IS NULL))) GROUP BY \\\"s\\\".\\\"id\\\" ORDER BY \\\"s\\\".\\\"published_time\\\" desc LIMIT 25' with params [\\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\\\\/followers\\\", \\\"%\\\\\\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\\\\/followers\\\\\\\"%\\\", \\\"%\\\\\\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\\\\/followers\\\\\\\"%\\\", \\\"%\\\\\\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\\\\/followers\\\\\\\"%\\\", \\\"%\\\\\\\"\\\", \\\"\\\\\\\"%\\\", \\\"%\\\\\\\"\\\", \\\"\\\\\\\"%\\\", \\\"%\\\\\\\"\\\", \\\"\\\\\\\"%\\\", \\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\", 1, \\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\", \\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\", 1, \\\"2019-06-23 19:46:39\\\", 0, \\\"https:\\\\\\/\\\\\\/kolosowscy.cloud\\\\\\/apps\\\\\\/social\\\\\\/@jurek\\\"]:\\n\\nSQLSTATE[42803]: Grouping error: 7 ERROR:  column \\\"s.type\\\" must appear in the GROUP BY clause or be used in an aggregate function\\nLINE 1: SELECT DISTINCT \\\"s\\\".\\\"id\\\", \\\"s\\\".\\\"type\\\", \\\"s\\\".\\\"to\\\", \\\"s\\\".\\\"to_arra...\\n                                  ^\"}",
  "userAgent": "Mozilla/5.0 (X11; Linux x86_64; rv:67.0) Gecko/20100101 Firefox/67.0",
  "version": "16.0.1.1"
}

Browser log

The network log:

{
  "status": -1,
  "exception": "Doctrine\\DBAL\\Exception\\DriverException",
  "message": "An exception occurred while executing 'SELECT DISTINCT \"s\".\"id\", \"s\".\"type\", \"s\".\"to\", \"s\".\"to_array\", \"s\".\"cc\", \"s\".\"bcc\", \"s\".\"content\", \"s\".\"summary\", \"s\".\"attachments\", \"s\".\"published\", \"s\".\"published_time\", \"s\".\"cache\", \"s\".\"object_id\", \"s\".\"attributed_to\", \"s\".\"in_reply_to\", \"s\".\"source\", \"s\".\"local\", \"s\".\"instances\", \"s\".\"creation\", \"s\".\"hidden_on_timeline\", \"f\".\"object_id\" AS \"following_actor_id\", \"ca\".\"id\" AS \"cacheactor_id\", \"ca\".\"type\" AS \"cacheactor_type\", \"ca\".\"account\" AS \"cacheactor_account\", \"ca\".\"following\" AS \"cacheactor_following\", \"ca\".\"followers\" AS \"cacheactor_followers\", \"ca\".\"inbox\" AS \"cacheactor_inbox\", \"ca\".\"shared_inbox\" AS \"cacheactor_shared_inbox\", \"ca\".\"outbox\" AS \"cacheactor_outbox\", \"ca\".\"featured\" AS \"cacheactor_featured\", \"ca\".\"url\" AS \"cacheactor_url\", \"ca\".\"preferred_username\" AS \"cacheactor_preferred_username\", \"ca\".\"name\" AS \"cacheactor_name\", \"ca\".\"summary\" AS \"cacheactor_summary\", \"ca\".\"public_key\" AS \"cacheactor_public_key\", \"ca\".\"source\" AS \"cacheactor_source\", \"ca\".\"creation\" AS \"cacheactor_creation\", \"ca\".\"local\" AS \"cacheactor_local\", \"sa\".\"id\" AS \"streamaction_id\", \"sa\".\"actor_id\" AS \"streamaction_actor_id\", \"sa\".\"stream_id\" AS \"streamaction_stream_id\", \"sa\".\"values\" AS \"streamaction_values\" FROM \"oc_social_a2_stream\" \"s\" INNER JOIN \"oc_social_a2_follows\" \"f\" ON ((\"to\" = ?) OR (\"to_array\" ILIKE ?) OR (\"cc\" ILIKE ?) OR (\"bcc\" ILIKE ?)) OR (((LOWER(\"s\".\"to\") = LOWER(\"f\".\"follow_id\")) OR (\"s\".\"to_array\" ILIKE (? || (\"f\".\"follow_id\" || ?))) OR (\"s\".\"cc\" ILIKE (? || (\"f\".\"follow_id\" || ?))) OR (\"s\".\"bcc\" ILIKE (? || (\"f\".\"follow_id\" || ?)))) AND (LOWER(\"f\".\"actor_id\") = LOWER(?)) AND (\"f\".\"accepted\" = ?)) LEFT JOIN \"oc_social_a2_cache_actors\" \"ca\" ON LOWER(\"f\".\"object_id\") = LOWER(\"ca\".\"id\") LEFT JOIN \"oc_social_a2_stream_action\" \"sa\" ON ((LOWER(\"s\".\"id\") = LOWER(\"sa\".\"stream_id\")) OR (LOWER(\"s\".\"object_id\") = LOWER(\"sa\".\"stream_id\"))) AND (LOWER(\"sa\".\"actor_id\") = ?) LEFT JOIN \"oc_social_a2_follows\" \"fs\" ON (LOWER(\"fs\".\"actor_id\") = LOWER(?)) AND (LOWER(\"s\".\"attributed_to\") = LOWER(\"fs\".\"object_id\")) AND (\"fs\".\"accepted\" = ?) WHERE (\"s\".\"published_time\" <= ?) AND ((\"s\".\"hidden_on_timeline\" = ?) OR ((LOWER(\"attributed_to\") <> LOWER(?)) AND (\"fs\".\"id\" IS NULL))) GROUP BY \"s\".\"id\" ORDER BY \"s\".\"published_time\" desc LIMIT 25' with params [\"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\\/followers\", \"%\\\"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\\/followers\\\"%\", \"%\\\"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\\/followers\\\"%\", \"%\\\"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\\/followers\\\"%\", \"%\\\"\", \"\\\"%\", \"%\\\"\", \"\\\"%\", \"%\\\"\", \"\\\"%\", \"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\", 1, \"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\", \"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\", 1, \"2019-06-23 19:46:39\", 0, \"https:\\/\\/kolosowscy.cloud\\/apps\\/social\\/@jurek\"]:\n\nSQLSTATE[42803]: Grouping error: 7 ERROR:  column \"s.type\" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT DISTINCT \"s\".\"id\", \"s\".\"type\", \"s\".\"to\", \"s\".\"to_arra...\n                                  ^"
}

[fbrinker]

I'm having the same problem. The DBAL\Exception looks like if there is a missing ANY_VALUE for s.type regarding a possible ONLY_FULL_GROUP_BY MySQL server directive?

It's this query:
https://github.com/nextcloud/social/blob/20a4cb0b6f547fc85b3e05cd5a1de247d33ad918/lib/Db/StreamRequestBuilder.php#L95

After removing the groupBy it worked (but with a changed functionality I guess... ;)
I couldn't find a proper way to add ANY_VALUE, MIN, or MAX MySQL functions to that Doctrine DBAL Query. But since I'm at work right now... I didn't had much time to test it. Seems like Doctrine doesn't recognize those functions in that Query. Or is it even DQL?

Maybe it is necessary (and the easiest way) to rewrite that query to plain MySQL, since there is no need to use a query builder as far as i can see (the query isn't build with any conditions).
Edit: Okay the qb is returned. Don't know what happens afterwards... :D

[ArtificialOwl]

fixed in 0.2.6 ?

[fbrinker]

Yes, it's working again, at least for me. Thank you! :)
What was the solution? There are package updates only, or am I missing something?

But the global timeline is loading very slowly, but that's another story

[mcphail]

Still not working for me on 0.2.6

[fbrinker]

With the same error message? Could you provide any more details @mcphail ?

Since, as far as I can see, the group by is removed from that query in the current version, it should work, at least there shouldn't be any error with missing aggregator functions in a group by query :)

I have the problem that loading the global timeline takes ages, but that's another story

[mcphail]

Yes, get the same error message. I'm using the Nextcloud snap. Social had been working reasonably well on the 0.1.x versions but hasn't worked for me at all since 0.2.x. I've performed a reset since then and lost my followers list. I can't make any posts. Updated to the latest version of Social last night and still got the error message from the title. Logs show:

executing 'SELECT DISTINCT `s`.`id`, `s`.`type`, `s`.`to`, `s`.`to_array`, `s`.`cc`, `s`.`bcc`, `s`.`content`, `s`.`summary`, `s`.`attachments`, `s`.`published`, `s`.`published_time`, `s`.`cache`, `s`.`object_id`, `s`.`attributed_to`, `s`.`in_reply_to`, `s`.`source`, `s`.`local`, `s`.`instances`, `s`.`creation`, `s`.`hidden_on_timeline`, `f`.`object_id` AS `following_actor_id`, `ca`.`id` AS `cacheactor_id`, `ca`.`type` AS `cacheactor_type`, `ca`.`account` AS `cacheactor_account`, `ca`.`following` AS `cacheactor_following`, `ca`.`followers` AS `cacheactor_followers`, `ca`.`inbox` AS `cacheactor_inbox`, `ca`.`shared_inbox` AS `cacheactor_shared_inbox`, `ca`.`outbox` AS `cacheactor_outbox`, `ca`.`featured` AS `cacheactor_featured`, `ca`.`url` AS `cacheactor_url`, `ca`.`preferred_username` AS `cacheactor_preferred_username`, `ca`.`name` AS `cacheactor_name`, `ca`.`summary` AS `cacheactor_summary`, `ca`.`public_key` AS `cacheactor_public_key`, `ca`.`source` AS `cacheactor_source`, `ca`.`creation` AS `cacheactor_creation`, `ca`.`local` AS `cacheactor_local`, `sa`.`id` AS `streamaction_id`, `sa`.`actor_id` AS `streamaction_actor_id`, `sa`.`stream_id` AS `streamaction_stream_id`, `sa`.`values` AS `streamaction_values` FROM `oc_social_a2_stream` `s` INNER JOIN `oc_social_a2_follows` `f` ON ((`to` = ?) OR (`to_array` COLLATE utf8mb4_general_ci LIKE ?) OR (`cc` COLLATE utf8mb4_general_ci LIKE ?) OR (`bcc` COLLATE utf8mb4_general_ci LIKE ?)) OR (((LOWER(`s`.`to`) = LOWER(`f`.`follow_id`)) OR (`s`.`to_array` COLLATE utf8mb4_general_ci LIKE CONCAT(?, CONCAT(`f`.`follow_id`, ?))) OR (`s`.`cc` COLLATE utf8mb4_general_ci LIKE CONCAT(?, CONCAT(`f`.`follow_id`, ?))) OR (`s`.`bcc` COLLATE utf8mb4_general_ci LIKE CONCAT(?, CONCAT(`f`.`follow_id`, ?)))) AND (LOWER(`f`.`actor_id`) = LOWER(?)) AND (`f`.`accepted` = ?)) LEFT JOIN `oc_social_a2_cache_actors` `ca` ON LOWER(`f`.`object_id`) = LOWER(`ca`.`id`) LEFT JOIN `oc_social_a2_stream_action` `sa` ON ((LOWER(`s`.`id`) = LOWER(`sa`.`stream_id`)) OR (LOWER(`s`.`object_id`) = LOWER(`sa`.`stream_id`))) AND (LOWER(`sa`.`actor_id`) = ?) LEFT JOIN `oc_social_a2_follows` `fs` ON (LOWER(`fs`.`actor_id`) = LOWER(?)) AND (LOWER(`s`.`attributed_to`) = LOWER(`fs`.`object_id`)) AND (`fs`.`accepted` = ?) WHERE (`s`.`published_time` <= ?) AND ((`s`.`hidden_on_timeline` = ?) OR ((LOWER(`attributed_to`) <> LOWER(?)) AND (`fs`.`id` IS NULL))) GROUP BY `s`.`id` ORDER BY `s`.`published_time` desc LIMIT 25' with params [\"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\\\/followers\", \"%\\\"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\\\/followers\\\"%\", \"%\\\"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\\\/followers\\\"%\", \"%\\\"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\\\/followers\\\"%\", \"%\\\"\", \"\\\"%\", \"%\\\"\", \"\\\"%\", \"%\\\"\", \"\\\"%\", \"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\", 1, \"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\", \"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\", 1, \"2019-07-01 06:59:55\", 0, \"https:\\\/\\\/my.redacted.url\\\/index.php\\\/apps\\\/social\\\/@neil\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nextcloud.s.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"}

[ArtificialOwl]

as @fbrinker said, GROUP BY are totally removed in 0.2.6 so this request should not exist anymore.
If you think that some files were not replaced during the upgrade, I would advice you to totally remove social from your nextcloud and reinstall the code.

[mcphail]

I've disabled then removed the Social app, then selected Download and enable. The version is 0.2.6. I'm still getting the "Failed to load more timeline entries" error message and those same errors in the log. Is there something else I should be doing to purge the app before I reinstall?

[ArtificialOwl]

I have no idea, I would check that the social app is unique and the right version is installed

• run the occ command: ./occ config:app:get social installed_version
• Within the social app folder, run: grep groupBy * -r

To fully purge the app:

• BACKUP YOUR FILES/DATABASE !
• remove the social folder in your apps folder (check all custom apps folders)
• run those SQL commands:
• DELETE FROM oc_appconfig WHERE appid='social'
• DELETE FROM oc_migrations WHERE app='social'
• DROP TABLE oc_social_a2_actors
• DROP TABLE oc_social_a2_cache_actors
• DROP TABLE oc_social_a2_cache_documts
• DROP TABLE oc_social_a2_follows
• DROP TABLE oc_social_a2_hashtags
• DROP TABLE oc_social_a2_request_queue
• DROP TABLE oc_social_a2_stream
• DROP TABLE oc_social_a2_stream_action
• DROP TABLE oc_social_a2_stream_queue

[mcphail]

Thanks for the help. Unfortunately that didn't help. However, in a fit of pique I ran snap restart nextcloud which seems to have miraculously fixed things! I'm seeing posts now, albeit they are from 18 days ago. I assume this was a snap issue and will ping @kyrofa to see if he wants any information to debug.

Thanks for your help and sorry for the noise.

[fbrinker]

So i guess we can close this, @jkolo ?
@daita thanks for the fast fix :)

[KopfKrieg]

@daita Thanks, purging everything fixed the issue for me :)