kcal-app / kcal

the personal food nutrition journal
Mozilla Public License 2.0
295 stars 27 forks source link

SQL error causing 500 Internal Server Error on both /recipes and /foods #63

Closed Amolith closed 7 months ago

Amolith commented 9 months ago

When I browse to both /recipes and /food, I get the 500 SERVER ERROR page and see an accompanying error and stack trace in the Laravel logs. I've included both that stack trace and my redacted .env file.

Expand .env file
APP_NAME=kcal
APP_ENV=production
APP_KEY=[REDACTED]
APP_DEBUG=false
APP_URL=https://[REDACTED]
APP_PORT=80
APP_SERVICE=app
APP_TIMEZONE=UTC
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=kcal
DB_USERNAME=kcal
DB_PASSWORD=[REDACTED]
REDIS_HOST=localhost
REDIS_PORT=6379
SCOUT_DRIVER=algolia
ALGOLIA_APP_ID=[REDACTED]
ALGOLIA_SECRET=[REDACTED]
MEDIA_DISK=media
QUEUE_CONVERSIONS_BY_DEFAULT=false
Expand stacktrace
[2023-12-12 00:57:20] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1055 'kcal.tags.name' isn't in GROUP BY (SQL: select `id`, `name`, count(*) as total from `tags` inner join `taggables` on `taggables`.`tag_id` = `id` where `taggables`.`taggable_type` = App\Models\Food group by `id` order by json_unquote(json_extract(`name`, '$."en"')) asc) {"userId":12,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1055 'kcal.tags.name' isn't in GROUP BY (SQL: select `id`, `name`, count(*) as total from `tags` inner join `taggables` on `taggables`.`tag_id` = `id` where `taggables`.`taggable_type` = App\\Models\\Food group by `id` order by json_unquote(json_extract(`name`, '$.\"en\"')) asc) at /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php:760)
[stacktrace]
#0 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php(720): Illuminate\\Database\\Connection->runQueryCallback()
#1 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Connection.php(405): Illuminate\\Database\\Connection->run()
#2 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2705): Illuminate\\Database\\Connection->select()
#3 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2694): Illuminate\\Database\\Query\\Builder->runSelect()
#4 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3230): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#5 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2693): Illuminate\\Database\\Query\\Builder->onceWithColumns()
#6 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(710): Illuminate\\Database\\Query\\Builder->get()
#7 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(694): Illuminate\\Database\\Eloquent\\Builder->getModels()
#8 /var/www/kcal/app/Models/Traits/Ingredient.php(59): Illuminate\\Database\\Eloquent\\Builder->get()
#9 /var/www/kcal/app/Http/Controllers/FoodController.php(21): App\\Models\\Food::getTagTotals()
#10 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): App\\Http\\Controllers\\FoodController->index()
#11 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(43): Illuminate\\Routing\\Controller->callAction()
#12 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Route.php(259): Illuminate\\Routing\\ControllerDispatcher->dispatch()
#13 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\\Routing\\Route->runController()
#14 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php(798): Illuminate\\Routing\\Route->run()
#15 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}()
#16 /var/www/kcal/app/Http/Middleware/DisableBrowserCache.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#17 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): App\\Http\\Middleware\\DisableBrowserCache->handle()
#18 /var/www/kcal/vendor/spatie/laravel-csp/src/AddCspHeaders.php(13): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#19 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Spatie\\Csp\\AddCspHeaders->handle()
#20 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#21 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle()
#22 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(44): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#23 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Auth\\Middleware\\Authenticate->handle()
#24 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(78): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#25 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\VerifyCsrfToken->handle()
#26 /var/www/kcal/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#27 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\View\\Middleware\\ShareErrorsFromSession->handle()
#28 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(121): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#29 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(64): Illuminate\\Session\\Middleware\\StartSession->handleStatefulRequest()
#30 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Session\\Middleware\\StartSession->handle()
#31 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#32 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Cookie\\Middleware\\AddQueuedCookiesToResponse->handle()
#33 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(67): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#34 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Cookie\\Middleware\\EncryptCookies->handle()
#35 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#36 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php(797): Illuminate\\Pipeline\\Pipeline->then()
#37 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php(776): Illuminate\\Routing\\Router->runRouteWithinStack()
#38 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php(740): Illuminate\\Routing\\Router->runRoute()
#39 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Routing/Router.php(729): Illuminate\\Routing\\Router->dispatchToRoute()
#40 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(190): Illuminate\\Routing\\Router->dispatch()
#41 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}()
#42 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#43 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php(31): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle()
#44 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ConvertEmptyStringsToNull->handle()
#45 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#46 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle()
#47 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle()
#48 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#49 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle()
#50 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(86): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#51 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle()
#52 /var/www/kcal/vendor/fruitcake/laravel-cors/src/HandleCors.php(38): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#53 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Fruitcake\\Cors\\HandleCors->handle()
#54 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php(39): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#55 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(180): Illuminate\\Http\\Middleware\\TrustProxies->handle()
#56 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}()
#57 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(165): Illuminate\\Pipeline\\Pipeline->then()
#58 /var/www/kcal/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(134): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter()
#59 /var/www/kcal/public/index.php(51): Illuminate\\Foundation\\Http\\Kernel->handle()
#60 {main}

I'm happy to help debug further, but I don't know any of the frameworks in use or even PHP.

drizt commented 7 months ago

See #64. I fixed this error.