-
Notifications
You must be signed in to change notification settings - Fork 190
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Group By errors on Mysql/MariaDB 5.7 #80
Comments
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. |
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. {"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"} |
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? |
The permissions should be okay, I used this script before the installation: And will run this afterwards: 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? |
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. |
Thanks, do I understand this correct: And I have to change them every time after an update of the app? Do you know how I have to change the queries? |
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; |
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. |
Was that feature enabled by default btw? |
Yes it was (if I remember correctly). Standard MariaDB on Ubuntu 16.04 LTS Server. |
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 |
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 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`
Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`
Switch to xenial. Set sql_mode to `STRICT_ALL_TABLES, ONLY_FULL_GROUP_BY`
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>
IMPORTANT
Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)
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
System Information
Contents of nextcloud/data/nextcloud.log
The text was updated successfully, but these errors were encountered: