Group By errors on Mysql/MariaDB 5.7 #80

matzex · 2016-12-28T14:13:00Z

IMPORTANT

Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)

I have read the CONTRIBUTING.md and followed the provided tips

Explain the Problem

After upgrading from ownCloud 8.2.9 to Nextcloud 11.0.0 (via several upgrades) the News app failed to activate. So I removed all folders related to the news app and removed the tables for the news app from the database. Afterwards I reinstalled the news app, but the app fails when I try to open the app. There is an internal server error.

Steps to Reproduce

Explain what you did to encounter the issue

Upgrade to Nextcloud 11.0.0
Install News App
Open News App as a user

System Information

News app version: 10.1.0
Nextcloud version: 11.0.0
PHP version: 7.0.14-2+deb.sury.org~xenial+1
Database and version: mysql Ver 15.1 Distrib 10.0.28-MariaDB
Browser and version: Chromium 56.0.2924.14 Ubuntu 16.04 (64-bit)
Distribution and version: Ubuntu 16.04

Contents of nextcloud/data/nextcloud.log

{"reqId":"WGPHu1Gp@poAAFu7Nm0AAAAE","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"filesize(): stat failed for \/var\/www\/domain.tld\/cloud\/data\/news\/config\/config.ini at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#211","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/folders","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFu7Nm0AAAAE","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fopen(\/var\/www\/domain.tld\/cloud\/data\/news\/config\/config.ini): failed to open stream: No such file or directory at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#216","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/folders","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFu7Nm0AAAAE","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fread() expects parameter 1 to be resource, boolean given at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#217","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/folders","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFu7Nm0AAAAE","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fclose() expects parameter 1 to be resource, boolean given at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#218","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/folders","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFu7Nm0AAAAE","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"news","message":"Configuration invalid. Ignoring values.","level":2,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/folders","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"filesize(): stat failed for \/var\/www\/domain.tld\/cloud\/data\/news\/config\/config.ini at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#211","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fopen(\/var\/www\/domain.tld\/cloud\/data\/news\/config\/config.ini): failed to open stream: No such file or directory at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#216","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fread() expects parameter 1 to be resource, boolean given at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#217","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"PHP","message":"fclose() expects parameter 1 to be resource, boolean given at \/var\/www\/domain.tld\/cloud\/lib\/private\/Files\/Storage\/Local.php#218","level":0,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"news","message":"Configuration invalid. Ignoring values.","level":2,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPHu1Gp@poAAFvB02MAAAAA","remoteAddr":"2001:a61:3443:c00:f2de:f1ff:fe77:5ea9","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` FROM `oc_news_feeds` `feeds` LEFT OUTER JOIN `oc_news_folders` `folders` ON `feeds`.`folder_id` = `folders`.`id` LEFT JOIN `oc_news_items` `items` ON `feeds`.`id` = `items`.`feed_id` AND (`items`.`status` & 2) = 2 WHERE `feeds`.`user_id` = ? AND (`feeds`.`folder_id` = 0 OR `folders`.`deleted_at` = 0)AND `feeds`.`deleted_at` = 0 GROUP BY `feeds`.`id`' with params [\\\"matthias\\\"]:\\n\\nSQLSTATE[42000]: Syntax error or access violation: 1055 'cloud.domain.tld.feeds.user_id' isn't in GROUP BY\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(177): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'SELECT `feeds`....', Array)\\n#2 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(252): Doctrine\\\\DBAL\\\\Statement->execute()\\n#3 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(346): OCP\\\\AppFramework\\\\Db\\\\Mapper->execute('SELECT `feeds`....', Array, NULL, NULL)\\n#4 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Db\\\/FeedMapper.php(70): OCP\\\\AppFramework\\\\Db\\\\Mapper->findEntities('SELECT `feeds`....', Array)\\n#5 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Service\\\/FeedService.php(72): OCA\\\\News\\\\Db\\\\FeedMapper->findAllFromUser('matthias')\\n#6 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Controller\\\/FeedController.php(64): OCA\\\\News\\\\Service\\\\FeedService->findAll('matthias')\\n#7 [internal function]: OCA\\\\News\\\\Controller\\\\FeedController->index()\\n#8 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(160): call_user_func_array(Array, Array)\\n#9 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(90): OC\\\\AppFramework\\\\Http\\\\Dispatcher->executeController(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'index')\\n#10 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/App.php(114): OC\\\\AppFramework\\\\Http\\\\Dispatcher->dispatch(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'index')\\n#11 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Routing\\\/RouteActionHandler.php(47): OC\\\\AppFramework\\\\App::main('OCA\\\\\\\\News\\\\\\\\Contro...', 'index', Object(OC\\\\AppFramework\\\\DependencyInjection\\\\DIContainer), Array)\\n#12 [internal function]: OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler->__invoke(Array)\\n#13 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/Route\\\/Router.php(299): call_user_func(Object(OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler), Array)\\n#14 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/base.php(1010): OC\\\\Route\\\\Router->match('\\\/apps\\\/news\\\/feed...')\\n#15 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/index.php(40): OC::handleRequest()\\n#16 {main}\",\"File\":\"\\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":116}","level":3,"time":"2016-12-28T14:10:04+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}

BernhardPosselt · 2016-12-28T14:22:06Z

Several things: your config.ini is invalid, check data/news/config/config.ini and see if it roughly matches with the example in the README. The next thing is that while your paste contains an exception that is interesting, that exception wont prevent the app from being enabled. Clear your caches (do you use a PHP cache? if so restart your webserver) and paste the exact exception that is being thrown after you hit the enable button.

matzex · 2016-12-28T14:48:51Z

It looks like the folder data/news/config/ and the file config.ini were not created by the app. I reinstalled it another time and this folder and file were also not there. So I created it manually with the content from the README.
Now I get this error:

{"reqId":"WGPPr1Gp@poAABVVlioAAAAC","remoteAddr":"2001:XYZ","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` FROM `oc_news_feeds` `feeds` LEFT OUTER JOIN `oc_news_folders` `folders` ON `feeds`.`folder_id` = `folders`.`id` LEFT JOIN `oc_news_items` `items` ON `feeds`.`id` = `items`.`feed_id` AND (`items`.`status` & 2) = 2 WHERE `feeds`.`user_id` = ? AND (`feeds`.`folder_id` = 0 OR `folders`.`deleted_at` = 0)AND `feeds`.`deleted_at` = 0 GROUP BY `feeds`.`id`' with params [\\\"matthias\\\"]:\\n\\nSQLSTATE[42000]: Syntax error or access violation: 1055 'cloud.domain.tld.feeds.user_id' isn't in GROUP BY\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(177): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'SELECT `feeds`....', Array)\\n#2 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(252): Doctrine\\\\DBAL\\\\Statement->execute()\\n#3 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(346): OCP\\\\AppFramework\\\\Db\\\\Mapper->execute('SELECT `feeds`....', Array, NULL, NULL)\\n#4 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Db\\\/FeedMapper.php(70): OCP\\\\AppFramework\\\\Db\\\\Mapper->findEntities('SELECT `feeds`....', Array)\\n#5 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Service\\\/FeedService.php(72): OCA\\\\News\\\\Db\\\\FeedMapper->findAllFromUser('matthias')\\n#6 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Controller\\\/FeedController.php(64): OCA\\\\News\\\\Service\\\\FeedService->findAll('matthias')\\n#7 [internal function]: OCA\\\\News\\\\Controller\\\\FeedController->index()\\n#8 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(160): call_user_func_array(Array, Array)\\n#9 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(90): OC\\\\AppFramework\\\\Http\\\\Dispatcher->executeController(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'index')\\n#10 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/App.php(114): OC\\\\AppFramework\\\\Http\\\\Dispatcher->dispatch(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'index')\\n#11 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Routing\\\/RouteActionHandler.php(47): OC\\\\AppFramework\\\\App::main('OCA\\\\\\\\News\\\\\\\\Contro...', 'index', Object(OC\\\\AppFramework\\\\DependencyInjection\\\\DIContainer), Array)\\n#12 [internal function]: OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler->__invoke(Array)\\n#13 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/Route\\\/Router.php(299): call_user_func(Object(OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler), Array)\\n#14 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/base.php(1010): OC\\\\Route\\\\Router->match('\\\/apps\\\/news\\\/feed...')\\n#15 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/index.php(40): OC::handleRequest()\\n#16 {main}\",\"File\":\"\\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":116}","level":3,"time":"2016-12-28T14:43:59+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds","user":"matthias","version":"11.0.0.10"}
{"reqId":"WGPPr1Gp@poAABVU3iYAAAAB","remoteAddr":"2001:XYZ","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` FROM `oc_news_feeds` `feeds` LEFT JOIN `oc_news_items` `items` ON `feeds`.`id` = `items`.`feed_id` AND (`items`.`status` & 2) = 2 WHERE `feeds`.`id` = ? AND `feeds`.`user_id` = ? GROUP BY `feeds`.`id`' with params [0, \\\"matthias\\\"]:\\n\\nSQLSTATE[42000]: Syntax error or access violation: 1055 'cloud.domain.tld.feeds.user_id' isn't in GROUP BY\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Statement.php(177): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'SELECT `feeds`....', Array)\\n#2 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(252): Doctrine\\\\DBAL\\\\Statement->execute()\\n#3 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(281): OCP\\\\AppFramework\\\\Db\\\\Mapper->execute('SELECT `feeds`....', Array, NULL, NULL)\\n#4 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/public\\\/AppFramework\\\/Db\\\/Mapper.php(373): OCP\\\\AppFramework\\\\Db\\\\Mapper->findOneQuery('SELECT `feeds`....', Array, NULL, NULL)\\n#5 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Db\\\/FeedMapper.php(45): OCP\\\\AppFramework\\\\Db\\\\Mapper->findEntity('SELECT `feeds`....', Array)\\n#6 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Service\\\/Service.php(54): OCA\\\\News\\\\Db\\\\FeedMapper->find(0, 'matthias')\\n#7 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/apps\\\/news\\\/lib\\\/Controller\\\/FeedController.php(100): OCA\\\\News\\\\Service\\\\Service->find(0, 'matthias')\\n#8 [internal function]: OCA\\\\News\\\\Controller\\\\FeedController->active()\\n#9 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(160): call_user_func_array(Array, Array)\\n#10 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Http\\\/Dispatcher.php(90): OC\\\\AppFramework\\\\Http\\\\Dispatcher->executeController(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'active')\\n#11 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/App.php(114): OC\\\\AppFramework\\\\Http\\\\Dispatcher->dispatch(Object(OCA\\\\News\\\\Controller\\\\FeedController), 'active')\\n#12 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/AppFramework\\\/Routing\\\/RouteActionHandler.php(47): OC\\\\AppFramework\\\\App::main('OCA\\\\\\\\News\\\\\\\\Contro...', 'active', Object(OC\\\\AppFramework\\\\DependencyInjection\\\\DIContainer), Array)\\n#13 [internal function]: OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler->__invoke(Array)\\n#14 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/private\\\/Route\\\/Router.php(299): call_user_func(Object(OC\\\\AppFramework\\\\Routing\\\\RouteActionHandler), Array)\\n#15 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/lib\\\/base.php(1010): OC\\\\Route\\\\Router->match('\\\/apps\\\/news\\\/feed...')\\n#16 \\\/var\\\/www\\\/domain.tld\\\/cloud\\\/index.php(40): OC::handleRequest()\\n#17 {main}\",\"File\":\"\\\/var\\\/www\\\/domain.tld\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":116}","level":3,"time":"2016-12-28T14:43:59+00:00","method":"GET","url":"\/index.php\/apps\/news\/feeds\/active","user":"matthias","version":"11.0.0.10"}

BernhardPosselt · 2016-12-28T14:51:26Z

They are usually created by default unless your access rights are wrong (e.g. webserver cant write into that directory). Can you rule that out?

matzex · 2016-12-28T15:02:59Z

The permissions should be okay, I used this script before the installation:
https://docs.nextcloud.com/server/11.0/admin_manual/maintenance/update.html#set-updating-permissions-label

And will run this afterwards:
https://docs.nextcloud.com/server/11.0/admin_manual/installation/installation_wizard.html#setting-strong-directory-permissions

Also the folder data/news/cache were created succesfully only the config folder were not created. But as I mentioned I created the config manually and got 2 other errors. Can you help me with them?

BernhardPosselt · 2016-12-28T15:06:07Z

Reason for the other 2 errors is basically http://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by

Kinda makes sense, kinda doesnt. Postgres is very picky about that but everything works fine for me. Also people on other Mysql/MariaDB instances don't have this issue so I don't know if its a bug or not.

matzex · 2016-12-28T15:14:50Z

Thanks, do I understand this correct:
I have to change at least the 2 database queries of the News app mentioned in the errors? In the file: apps/news/lib/Db/FeedMapper.php

And I have to change them every time after an update of the app? Do you know how I have to change the queries?

BernhardPosselt · 2016-12-28T15:21:32Z

It basically means: everything that you select should be in the GROUP BY if full group by is enabled (which your mysql/mariadb has apparently).

TL;DR: you cant

SELECT * FROM table GROUP BY field;

but you need

SELECT * FROM table GROUP BY every, field, in, that, table;

matzex · 2016-12-28T15:33:35Z

Okay, I just disabled the "FULL_GROUP_BY" feature in my MariaDB Installation. The App does work now. Thank you for your help with this.
For me this issue is fixed, I don't know if there should be a Warning in the future or something like this.

BernhardPosselt · 2016-12-28T15:34:10Z

Was that feature enabled by default btw?

matzex · 2016-12-28T15:35:10Z

Yes it was (if I remember correctly). Standard MariaDB on Ubuntu 16.04 LTS Server.

BernhardPosselt · 2016-12-28T15:55:49Z

Ok, asked around in the #mysql channel on freenode and apparently the default behavior was changed (so backwards compat break). Will try to reproduce this on a new vagrant instance and check what is affected :)

The thing that really bugs me however is why it works on postgres :P

vengefulpunk · 2017-02-09T22:49:31Z

I appear to be running into this same issue.

Here is one of the lines from the error log.

{"reqId":"c3v0hVq+G61C2LYjV5B8","remoteAddr":"76.113.21.184","app":"news","message":"Unexpected error when updating feed An exception occurred while executing 'INSERT INTO oc_news_items(status,url,guid,guid_hash,pub_date,rtl,title,author,body,search_index,fingerprint,content_hash,feed_id,last_modified) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)' with params

I switched by install from Postgre to mysql while troubleshooting a cron issue and the mysql instance is having this error now.

I am also running Ubuntu 16.04 and full_group by was enabled however I disabled it and verified that was changed on the mysql server and that the changes loaded.

Switch to xenial. Set sql_mode to `ONLY_FULL_GROUP_BY`

Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`

* Group by moved to subquery, get feed details from outer query (see #406) Signed-off-by: Talon <talontalon24@gmail.com>

Grotax · 2020-03-12T07:20:33Z

Should be fixed with #407

Changed - Basic Media-RSS support (#599) - Database index improvements (#637) Fixed - Call to a member function getUrlHash() on null" when adding a feed (#640) - Don't install symfony/console via composer (#636) - Fix for for ONLY_FULL_GROUP_BY (see #406) (Issue #80) (#407) - Catch invalid feeds (#646) Signed-off-by: Benjamin Brahmer <info@b-brahmer.de>

matzex closed this as completed Dec 28, 2016

BernhardPosselt reopened this Dec 28, 2016

BernhardPosselt changed the title ~~Internal Server Error~~ Group By errors on Mysql/MariaDB Dec 28, 2016

BernhardPosselt added bug help wanted labels Dec 28, 2016

BernhardPosselt changed the title ~~Group By errors on Mysql/MariaDB~~ Group By errors on Mysql/MariaDB 5.7 Jan 4, 2017

blizzard4591 mentioned this issue Feb 13, 2019

Syntax error or access violation: 1055 'feeds.user_id' isn't in GROUP BY #394

Closed

1 task

Grotax self-assigned this Mar 7, 2019

Grotax added a commit that referenced this issue Mar 7, 2019

Fix Group By errors on Mysql/MariaDB 5.7 #80

07e4c85

Switch to xenial. Set sql_mode to `ONLY_FULL_GROUP_BY`

Grotax added a commit that referenced this issue Mar 7, 2019

Fix Group By errors on Mysql/MariaDB 5.7 #80

4693972

Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`

Grotax added a commit that referenced this issue Mar 7, 2019

Fix Group By errors on Mysql/MariaDB 5.7 #80

d5f5c7c

Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`

Grotax added a commit that referenced this issue Mar 7, 2019

Fix Group By errors on Mysql/MariaDB 5.7 #80

f1eb28c

Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`

Talon24 mentioned this issue Mar 8, 2019

Different solution for ONLY_FULL_GROUP_BY (see #406) (Issue #80) #407

Merged

Grotax added in progress and removed help wanted labels Mar 14, 2019

Grotax mentioned this issue Jul 7, 2019

Update to feedio4 and php 7.1 #494

Merged

Grotax pushed a commit that referenced this issue Mar 1, 2020

Different solution for ONLY_FULL_GROUP_BY (see #406) (Issue #80) (#407)

cc051c6

* Group by moved to subquery, get feed details from outer query (see #406) Signed-off-by: Talon <talontalon24@gmail.com>

Grotax closed this as completed Mar 12, 2020

Grotax mentioned this issue Mar 12, 2020

Release 14.1.4-rc1 #649

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Group By errors on Mysql/MariaDB 5.7 #80

Group By errors on Mysql/MariaDB 5.7 #80

matzex commented Dec 28, 2016 •

edited by jospoortvliet

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016 •

edited

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016 •

edited

vengefulpunk commented Feb 9, 2017

Grotax commented Mar 12, 2020

Group By errors on Mysql/MariaDB 5.7 #80

Group By errors on Mysql/MariaDB 5.7 #80

Comments

matzex commented Dec 28, 2016 • edited by jospoortvliet

IMPORTANT

Explain the Problem

Steps to Reproduce

System Information

Contents of nextcloud/data/nextcloud.log

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016 • edited

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016

matzex commented Dec 28, 2016

BernhardPosselt commented Dec 28, 2016 • edited

vengefulpunk commented Feb 9, 2017

Grotax commented Mar 12, 2020

matzex commented Dec 28, 2016 •

edited by jospoortvliet

matzex commented Dec 28, 2016 •

edited

BernhardPosselt commented Dec 28, 2016 •

edited