Bug: on database migration: Unsupported product nesting level detected

[renarena]

I updated the homeassistants grocy integration, which updates grocy to 3.1.
After this i see this database migration error in grocy:

Error source:
/var/www/grocy/services/DatabaseService.php:35

Error message:
SQLSTATE[23000]: Integrity constraint violation: 19 Unsupported product nesting level detected (currently only 1 level is supported)

Stack trace:
#0 /var/www/grocy/services/DatabaseService.php(35): PDO->exec()
#1 /var/www/grocy/services/DatabaseMigrationService.php(63): Grocy\Services\DatabaseService->ExecuteDbStatement()
#2 /var/www/grocy/services/DatabaseMigrationService.php(33): Grocy\Services\DatabaseMigrationService->ExecuteSqlMigrationWhenNeeded()
#3 /var/www/grocy/controllers/SystemController.php(27): Grocy\Services\DatabaseMigrationService->MigrateDatabase()
#4 /var/www/grocy/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(43): Grocy\Controllers\SystemController->Root()
#5 /var/www/grocy/vendor/slim/slim/Slim/Routing/Route.php(384): Slim\Handlers\Strategies\RequestResponse->__invoke()
#6 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\Routing\Route->handle()
#7 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\MiddlewareDispatcher->handle()
#8 /var/www/grocy/vendor/slim/slim/Slim/Routing/Route.php(341): Slim\MiddlewareDispatcher->handle()
#9 /var/www/grocy/vendor/slim/slim/Slim/Routing/RouteRunner.php(84): Slim\Routing\Route->run()
#10 /var/www/grocy/middleware/LocaleMiddleware.php(16): Slim\Routing\RouteRunner->handle()
#11 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(313): Grocy\Middleware\LocaleMiddleware->__invoke()
#12 /var/www/grocy/middleware/AuthMiddleware.php(31): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#13 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(313): Grocy\Middleware\AuthMiddleware->__invoke()
#14 /var/www/grocy/vendor/slim/slim/Slim/Middleware/RoutingMiddleware.php(59): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#15 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(147): Slim\Middleware\RoutingMiddleware->process()
#16 /var/www/grocy/vendor/slim/slim/Slim/Middleware/ErrorMiddleware.php(107): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#17 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(147): Slim\Middleware\ErrorMiddleware->process()
#18 /var/www/grocy/middleware/CorsMiddleware.php(30): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#19 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(313): Grocy\Middleware\CorsMiddleware->__invoke()
#20 /var/www/grocy/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#21 /var/www/grocy/vendor/slim/slim/Slim/App.php(215): Slim\MiddlewareDispatcher->handle()
#22 /var/www/grocy/vendor/slim/slim/Slim/App.php(199): Slim\App->handle()
#23 /var/www/grocy/app.php(106): Slim\App->run()
#24 /var/www/grocy/public/index.php(45): require_once('...')
#25 {main}

[berrnd]

It's like the message says, you have a parent/child product relation with more than 1 level which is currently not supported (already requested in #1097 to extend that).

This check is in place (database/backend-side) since v3.0.0 and it was never possible on the frontend (the parent product field is disabled when the product already has childs, example) to create such a nesting (but not enforced on the backend before v3.0.0).

So I can only guess that you maybe created this not via the UI, but maybe via the API (or any external tool/app) and before v3.0.0...

My suggestion would be to revert the upgrade / restore your backup, check/fix the problematic parent/child product relation and upgrade again.

I can also try to reproduce this situation manually and find a potential "migration workaround/fix", but this will need some days or weeks, I currently don't have the time to dig into this.

[Didgeridrew]

After rolling back to my backup again I was able to identify the 2 offending parent products by opening a copy of the db in sqlitestudio. Simply clearing the "parent product" field in the Grocy UI (which was editable, not greyed out) did not fix the problem... I had to delete the product/item in the UI, restart Home Assistant, and then apply the update to the Grocy add-on.

As you state in your reply to OP, both of the items were originally created by an external app (Pantry Party android app), but I'm pretty sure it was in Grocy v3.0.0. The child/parent products were also weird combinations (for instance tortillas as child to coffee) that I wouldn't have done on purpose.

[berrnd]

I've added a workaround to also handle having unsupported parent/child product nesting levels.

If you want to have this now, replace the file migrations/0130.sql with that one and the upgrade will work.
(It simply clears the "Parent product" field for any nesting level > 1.)

---

Just for reference and reproducibility, here is a v3.0.1 (demo) database which has this problem (manually created - Chocolate, which is already a parent for Milk Chocolate and Dark Chocolate, is a child of Cookies at the same time):

grocy_v3.0.1_unsupported_product_nesting_level.zip

And only to note that again:

• It was never possible via the UI/frontend to produce this situation
• It was only possible via the API (or any tool/app which uses it) before v3.0.0
  • since v3.0.0 this is also enforced via the API/backend

[DerFips]

Hey, I'm on 4.0.1 and got the same error (again). I replaced the sql file and restarted the server. Or is there any other way to redo the migration part? Thanks!

---

Error source:

/app/www/services/DatabaseService.php:39

Error message:

SQLSTATE[23000]: Integrity constraint violation: 19 Unsupported product nesting level detected (currently only 1 level is supported)

Stack trace:

#0 /app/www/services/DatabaseService.php(39): PDO->exec()
#1 /app/www/services/DatabaseMigrationService.php(69): Grocy\Services\DatabaseService->ExecuteDbStatement()
#2 /app/www/services/DatabaseMigrationService.php(35): Grocy\Services\DatabaseMigrationService->ExecuteSqlMigrationWhenNeeded()
#3 /app/www/controllers/SystemController.php(30): Grocy\Services\DatabaseMigrationService->MigrateDatabase()
#4 /app/www/packages/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(38): Grocy\Controllers\SystemController->Root()
#5 /app/www/packages/slim/slim/Slim/Routing/Route.php(358): Slim\Handlers\Strategies\RequestResponse->__invoke()
#6 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(65): Slim\Routing\Route->handle()
#7 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(65): Slim\MiddlewareDispatcher->handle()
#8 /app/www/packages/slim/slim/Slim/Routing/Route.php(315): Slim\MiddlewareDispatcher->handle()
#9 /app/www/packages/slim/slim/Slim/Routing/RouteRunner.php(68): Slim\Routing\Route->run()
#10 /app/www/middleware/LocaleMiddleware.php(16): Slim\Routing\RouteRunner->handle()
#11 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\LocaleMiddleware->__invoke()
#12 /app/www/middleware/AuthMiddleware.php(32): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#13 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\AuthMiddleware->__invoke()
#14 /app/www/packages/slim/slim/Slim/Middleware/RoutingMiddleware.php(45): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#15 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(121): Slim\Middleware\RoutingMiddleware->process()
#16 /app/www/packages/slim/slim/Slim/Middleware/ErrorMiddleware.php(76): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#17 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(121): Slim\Middleware\ErrorMiddleware->process()
#18 /app/www/middleware/CorsMiddleware.php(27): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#19 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(269): Grocy\Middleware\CorsMiddleware->__invoke()
#20 /app/www/packages/slim/slim/Slim/MiddlewareDispatcher.php(65): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#21 /app/www/packages/slim/slim/Slim/App.php(199): Slim\MiddlewareDispatcher->handle()
#22 /app/www/packages/slim/slim/Slim/App.php(183): Slim\App->handle()
#23 /app/www/app.php(112): Slim\App->run()
#24 /app/www/public/index.php(45): require_once('...')
#25 {main}

System info:

{
    "grocy_version": {
        "Version": "4.0.1",
        "ReleaseDate": "2023-08-06"
    },
    "php_version": "8.2.8",
    "sqlite_version": "3.41.2",
    "os": "Linux 5.4.0-155-generic #172-Ubuntu SMP Fri Jul 7 16:10:02 UTC 2023 x86_64",
    "client": "Mozilla\/5.0 (Macintosh; Intel Mac OS X 10.15; rv:109.0) Gecko\/20100101 Firefox\/116.0"
}

[berrnd]

I'm on 4.0.1 and got the same error (again)

I can only guess that you have, however, made it once again to have product nesting levels > 1, as the error message implies. As pointed out above, this was never possible via the UI and is prevented on database level since v3.0.0. And furthermore, for v3.1.1 there was an automatic clean up for that (this one, maybe simply execute it once again manually if you don't want the rule out the problematic product relations by hand).

By all that, I really have no idea how you managed to get to that point, so in any case: Please provide a reproducible example and open another issue, just as usual and mentioned on the issue template.

 

I replaced the sql file and restarted the server.

Migrations run of course once, so unless you really upgrade from < v3.1.1, doing that again will not have any effect at all.

[DerFips]

Just executed the SQL statement and now it is working again. Thanks a lot!

And as I said, I didn't do any manual upgrade. I'm "just" using the docker image and updating this.