Some queries fail with sql_mode=only_full_group_by

[ghost]

I setup a directory site at Directory Pagol. But unfortunately getting 500 internal error. My server configs: Ubuntu 18.04 Apache 2.4.29 PHP 7.2.17 MySQL 5.7.26

I migrated the database, but when browsing the directory, got:

Slim Application Error
A website error has occurred. Sorry for the temporary inconvenience.

[AlfredSK]

Have you checked you have the document root right? The Webserver has to serve the .../public folder, not the root folder. And does the database connection work for the db-user you are using?

Also, there are logfiles in the .../logs folder. They might give you a hint what's wrong.

[MrPetovan]

Hi @karnakitab and thanks for using the Friendica Directory! Slim Application errors are logged in logs/http.log for browser access.

[ghost]

The ../logs/http.log error log is as follows:

[12-May-2019 18:02:50 Europe/Helsinki] Slim Application Error:
Type: PDOException
Code: 42000
Message: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'directory_friendica.p.filled_fields' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
File: .../...../.......//friendica-directory/vendor/atlas/pdo/src/Connection.php
Line: 138
Trace: #0 .../...../.......//friendica-directory/vendor/atlas/pdo/src/Connection.php(138): PDOStatement->execute()
#1 .../...../.......//friendica-directory/vendor/atlas/pdo/src/Connection.php(186): Atlas\Pdo\Connection->perform('SELECT `account...', Array)
#2 .../...../.......//friendica-directory/src/classes/Views/Widget/AccountTypeTabs.php(44): Atlas\Pdo\Connection->fetchAll('SELECT `account...', Array)
#3 .../...../.......//friendica-directory/src/classes/Controllers/Web/Directory.php(77): Friendica\Directory\Views\Widget\AccountTypeTabs->render('directory', 'favicon.ico')
#4 .../...../.......//friendica-directory/src/classes/Routes/Web/BaseRoute.php(39): Friendica\Directory\Controllers\Web\Directory->render(Object(Slim\Http\Request), Object(Slim\Http\Response), Array)
#5 [internal function]: Friendica\Directory\Routes\Web\BaseRoute->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response), Array)
#6 .../...../.......//friendica-directory/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(41): call_user_func(Array, Object(Slim\Http\Request), Object(Slim\Http\Response), Array)
#7 .../...../.......//friendica-directory/vendor/slim/slim/Slim/Route.php(356): Slim\Handlers\Strategies\RequestResponse->__invoke(Array, Object(Slim\Http\Request), Object(Slim\Http\Response), Array)
#8 .../...../.......//friendica-directory/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response))
#9 .../...../.......//friendica-directory/vendor/slim/slim/Slim/Route.php(334): Slim\Route->callMiddlewareStack(Object(Slim\Http\Request), Object(Slim\Http\Response))
#10 .../...../.......//friendica-directory/vendor/slim/slim/Slim/App.php(516): Slim\Route->run(Object(Slim\Http\Request), Object(Slim\Http\Response))
#11 .../...../.......//friendica-directory/vendor/gofabian/negotiation-middleware/src/NegotiationMiddleware.php(81): Slim\App->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response))
#12 [internal function]: Gofabian\Negotiation\NegotiationMiddleware->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Slim\App))
#13 .../...../.......//friendica-directory/vendor/slim/slim/Slim/DeferredCallable.php(43): call_user_func_array(Object(Gofabian\Negotiation\NegotiationMiddleware), Array)
#14 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Slim\App))
#15 .../...../.......//friendica-directory/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Slim\App))
#16 .../...../.......//friendica-directory/vendor/boronczyk/localization-middleware/src/LocalizationMiddleware.php(166): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(Slim\Http\Response))
#17 [internal function]: Boronczyk\LocalizationMiddleware->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Closure))
#18 .../...../.......//friendica-directory/vendor/slim/slim/Slim/DeferredCallable.php(43): call_user_func_array(Object(Boronczyk\LocalizationMiddleware), Array)
#19 [internal function]: Slim\DeferredCallable->__invoke(Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Closure))
#20 .../...../.......//friendica-directory/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func(Object(Slim\DeferredCallable), Object(Slim\Http\Request), Object(Slim\Http\Response), Object(Closure))
#21 .../...../.......//friendica-directory/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim\{closure}(Object(Slim\Http\Request), Object(Slim\Http\Response))
#22 .../...../.......//friendica-directory/vendor/slim/slim/Slim/App.php(407): Slim\App->callMiddlewareStack(Object(Slim\Http\Request), Object(Slim\Http\Response))
#23 .../...../.......//friendica-directory/vendor/slim/slim/Slim/App.php(315): Slim\App->process(Object(Slim\Http\Request), Object(Slim\Http\Response))
#24 .../...../.......//friendica-directory/public/index.php(36): Slim\App->run()
#25 {main}
View in rendered output by enabling the "displayErrorDetails" setting.

Wheres the cron ../logs/app.log says,

2019-05-12 18:00:02 - ERROR {"uid":"5d265da"}: SQLSTATE[HY000]: General error: 1364 Field 'path' doesn't have a default value: #0 .../...../.......//friendica-directory/vendor/atlas/pdo/src/Connection.php(138): PDOStatement->execute() #1 .../...../.......//friendica-directory/src/classes/Pollers/Server.php(106): Atlas\Pdo\Connection->perform('INSERT INTO `se...', Array) #2 .../...../.......//friendica-directory/src/classes/Controllers/Cron.php(148): Friendica\Directory\Pollers\Server->__invoke('https://connect...') #3 .../...../.......//friendica-directory/src/classes/Controllers/Cron.php(77): Friendica\Directory\Controllers\Cron->pollServers(24) #4 .../...../.......//friendica-directory/bin/cron.php(22): Friendica\Directory\Controllers\Cron->execute() #5 {main} []
2019-05-12 18:00:02 - ERROR {"uid":"5d265da"}: SQLSTATE[HY000]: General error: 1364 Field 'path' doesn't have a default value: #0 .../...../.......//friendica-directory/vendor/atlas/pdo/src/Connection.php(138): PDOStatement->execute() #1 .../...../.......//friendica-directory/src/classes/Pollers/Server.php(106): Atlas\Pdo\Connection->perform('INSERT INTO `se...', Array) #2 .../...../.......//friendica-directory/src/classes/Controllers/Cron.php(148): Friendica\Directory\Pollers\Server->__invoke('https://network...') #3 .../...../.......//friendica-directory/src/classes/Controllers/Cron.php(77): Friendica\Directory\Controllers\Cron->pollServers(24) #4 .../...../.......//friendica-directory/bin/cron.php(22): Friendica\Directory\Controllers\Cron->execute() #5 {main} []

[MrPetovan]

Thanks, what database software are you using? It seems to be more restrictive than what I used to write this query on, triggering this error.

[MrPetovan]

Can you please pull the latest master and report if you still experience the same error when browsing your directory?

[ghost]

I already used the latest master version (git clone https://github.com/friendica/friendica-directory.git). The database server is the default Ubuntu MySQL 5.7.26.

[AlfredSK]

There was an update... https://github.com/friendica/friendica-directory/commit/a0cfd3352145bdc81aa737afe962acb29c257f2b That's why you were ask to please pull again. :-)

[ghost]

Thank you. It is working now at Directory.

[MrPetovan]

Fixed by https://github.com/friendica/friendica-directory/commit/a0cfd3352145bdc81aa737afe962acb29c257f2b