Problem (incl. workaround) when running grocy v3.0.0 with SQLite >= 3.8.3 < 3.9.0

[JuergenLeber]

EDIT by @berrnd:
See #1209 (comment) for the fix/workaround when you are affected.

---

I just downloaded and installed release 3.0.0 but after modifying config.php and opening the application in /public I get an error "SQLSTATE[HY000]: General error: 1 SELECTs to the left and right of UNION do not have the same number of result columns". So I tried it again by downloading again but no luck. Next try was release 2.7.1 and it worked flawlessly. So I tried an upgrade to release 3.0.0 but same error again. I think there's something wrong with the database structure in the packed release zip file.

Error source:

/var/www/services/DatabaseService.php:33

Error message:

SQLSTATE[HY000]: General error: 1 SELECTs to the left and right of UNION do not have the same number of result columns

Stack trace:

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

If you need more information please let me know!

[berrnd]

Are you sure that you used a released version before and not the master branch (anytime inbetween)?

The same was recently reported here: https://www.reddit.com/r/grocy/comments/jogh32/slim_application_error_selects_to_the_left_and/gcjscc4

[JuergenLeber]

Yes, downloaded https://github.com/grocy/grocy/releases/download/v3.0.0/grocy_3.0.0.zip. Tried it twice as a fresh install and once as an update from 2.7.1.

[berrnd]

Starting from scratch definitely works, the demo instances do that hourly without an issue.

Upgrading at least worked for a couple of users and my private instance, so I doubt there is a general problem - unfortunately your error message doesn't say which migration fails...

Next thing to check: The used SQLite version needs to be >= 3.8.3 (not a new requirement).

Otherwise, as always, I can offer that you can send me your v2.7.1 database file (data/grocy.db), if that's ok for you, and I will look into.

[JuergenLeber]

That's strange. The 2.7.1 database file is also an empty one, just installed 2.7.1 from scratch so there's no valuable data in it. But the 2.7.1 worked without any problems. Here the queries that are being executed until the problem appears:

CREATE TABLE IF NOT EXISTS migrations (migration INTEGER NOT NULL PRIMARY KEY UNIQUE, execution_time_timestamp DATETIME DEFAULT (datetime('now', 'localtime'))) SELECT COUNT(*) FROM migrations WHERE migration = 1 SELECT COUNT(*) FROM migrations WHERE migration = 2 SELECT COUNT(*) FROM migrations WHERE migration = 3 SELECT COUNT(*) FROM migrations WHERE migration = 4 SELECT COUNT(*) FROM migrations WHERE migration = 5 SELECT COUNT(*) FROM migrations WHERE migration = 6 SELECT COUNT(*) FROM migrations WHERE migration = 7 SELECT COUNT(*) FROM migrations WHERE migration = 8 SELECT COUNT(*) FROM migrations WHERE migration = 9 SELECT COUNT(*) FROM migrations WHERE migration = 10 SELECT COUNT(*) FROM migrations WHERE migration = 11 SELECT COUNT(*) FROM migrations WHERE migration = 12 SELECT COUNT(*) FROM migrations WHERE migration = 13 SELECT COUNT(*) FROM migrations WHERE migration = 14 SELECT COUNT(*) FROM migrations WHERE migration = 15 SELECT COUNT(*) FROM migrations WHERE migration = 16 SELECT COUNT(*) FROM migrations WHERE migration = 17 SELECT COUNT(*) FROM migrations WHERE migration = 18 SELECT COUNT(*) FROM migrations WHERE migration = 19 SELECT COUNT(*) FROM migrations WHERE migration = 20 SELECT COUNT(*) FROM migrations WHERE migration = 21 SELECT COUNT(*) FROM migrations WHERE migration = 22 SELECT COUNT(*) FROM migrations WHERE migration = 23 SELECT COUNT(*) FROM migrations WHERE migration = 24 SELECT COUNT(*) FROM migrations WHERE migration = 25 SELECT COUNT(*) FROM migrations WHERE migration = 26 SELECT COUNT(*) FROM migrations WHERE migration = 27 SELECT COUNT(*) FROM migrations WHERE migration = 28 SELECT COUNT(*) FROM migrations WHERE migration = 29 SELECT COUNT(*) FROM migrations WHERE migration = 30 SELECT COUNT(*) FROM migrations WHERE migration = 31 SELECT COUNT(*) FROM migrations WHERE migration = 32 SELECT COUNT(*) FROM migrations WHERE migration = 33 SELECT COUNT(*) FROM migrations WHERE migration = 34 SELECT COUNT(*) FROM migrations WHERE migration = 35 SELECT COUNT(*) FROM migrations WHERE migration = 36 SELECT COUNT(*) FROM migrations WHERE migration = 37 SELECT COUNT(*) FROM migrations WHERE migration = 38 SELECT COUNT(*) FROM migrations WHERE migration = 39 SELECT COUNT(*) FROM migrations WHERE migration = 40 SELECT COUNT(*) FROM migrations WHERE migration = 41 SELECT COUNT(*) FROM migrations WHERE migration = 42 SELECT COUNT(*) FROM migrations WHERE migration = 43 SELECT COUNT(*) FROM migrations WHERE migration = 44 SELECT COUNT(*) FROM migrations WHERE migration = 45 SELECT COUNT(*) FROM migrations WHERE migration = 46 SELECT COUNT(*) FROM migrations WHERE migration = 47 SELECT COUNT(*) FROM migrations WHERE migration = 48 SELECT COUNT(*) FROM migrations WHERE migration = 49 SELECT COUNT(*) FROM migrations WHERE migration = 50 SELECT COUNT(*) FROM migrations WHERE migration = 51 SELECT COUNT(*) FROM migrations WHERE migration = 52 SELECT COUNT(*) FROM migrations WHERE migration = 53 SELECT COUNT(*) FROM migrations WHERE migration = 54 SELECT COUNT(*) FROM migrations WHERE migration = 55 SELECT COUNT(*) FROM migrations WHERE migration = 56 SELECT COUNT(*) FROM migrations WHERE migration = 57 SELECT COUNT(*) FROM migrations WHERE migration = 58 SELECT COUNT(*) FROM migrations WHERE migration = 59 SELECT COUNT(*) FROM migrations WHERE migration = 60 SELECT COUNT(*) FROM migrations WHERE migration = 61 SELECT COUNT(*) FROM migrations WHERE migration = 62 SELECT COUNT(*) FROM migrations WHERE migration = 63 SELECT COUNT(*) FROM migrations WHERE migration = 64 SELECT COUNT(*) FROM migrations WHERE migration = 65 SELECT COUNT(*) FROM migrations WHERE migration = 66 SELECT COUNT(*) FROM migrations WHERE migration = 67 SELECT COUNT(*) FROM migrations WHERE migration = 68 SELECT COUNT(*) FROM migrations WHERE migration = 69 SELECT COUNT(*) FROM migrations WHERE migration = 70 SELECT COUNT(*) FROM migrations WHERE migration = 71 SELECT COUNT(*) FROM migrations WHERE migration = 72 SELECT COUNT(*) FROM migrations WHERE migration = 73 SELECT COUNT(*) FROM migrations WHERE migration = 74 SELECT COUNT(*) FROM migrations WHERE migration = 75 SELECT COUNT(*) FROM migrations WHERE migration = 76 SELECT COUNT(*) FROM migrations WHERE migration = 77 SELECT COUNT(*) FROM migrations WHERE migration = 78 SELECT COUNT(*) FROM migrations WHERE migration = 79 SELECT COUNT(*) FROM migrations WHERE migration = 80 SELECT COUNT(*) FROM migrations WHERE migration = 81 SELECT COUNT(*) FROM migrations WHERE migration = 82 SELECT COUNT(*) FROM migrations WHERE migration = 83 SELECT COUNT(*) FROM migrations WHERE migration = 84 SELECT COUNT(*) FROM migrations WHERE migration = 85 SELECT COUNT(*) FROM migrations WHERE migration = 86 SELECT COUNT(*) FROM migrations WHERE migration = 87 SELECT COUNT(*) FROM migrations WHERE migration = 88 SELECT COUNT(*) FROM migrations WHERE migration = 89 SELECT COUNT(*) FROM migrations WHERE migration = 90 SELECT COUNT(*) FROM migrations WHERE migration = 91 SELECT COUNT(*) FROM migrations WHERE migration = 92 SELECT COUNT(*) FROM migrations WHERE migration = 93 SELECT COUNT(*) FROM migrations WHERE migration = 94 SELECT COUNT(*) FROM migrations WHERE migration = 95 SELECT COUNT(*) FROM migrations WHERE migration = 96 SELECT COUNT(*) FROM migrations WHERE migration = 97 SELECT COUNT(*) FROM migrations WHERE migration = 98 SELECT COUNT(*) FROM migrations WHERE migration = 99 SELECT COUNT(*) FROM migrations WHERE migration = 100 SELECT COUNT(*) FROM migrations WHERE migration = 101 SELECT COUNT(*) FROM migrations WHERE migration = 102 SELECT COUNT(*) FROM migrations WHERE migration = 103 SELECT COUNT(*) FROM migrations WHERE migration = 104 SELECT COUNT(*) FROM migrations WHERE migration = 105 CREATE VIEW uihelper_stock_current_overview_including_opened AS SELECT p.id, sc.amount_opened AS amount_opened, p.tare_weight AS tare_weight, p.enable_tare_weight_handling AS enable_tare_weight_handling, sc.amount AS amount, sc.value as value, sc.product_id AS product_id, sc.best_before_date AS best_before_date, EXISTS(SELECT id FROM stock_missing_products_including_opened WHERE id = sc.product_id) AS product_missing, (SELECT name FROM quantity_units WHERE quantity_units.id = p.qu_id_stock) AS qu_unit_name, (SELECT name_plural FROM quantity_units WHERE quantity_units.id = p.qu_id_stock) AS qu_unit_name_plural, p.name AS product_name, (SELECT name FROM product_groups WHERE product_groups.id = product_group_id) AS product_group_name, EXISTS(SELECT * FROM shopping_list WHERE shopping_list.product_id = sc.product_id) AS on_shopping_list, (SELECT name FROM quantity_units WHERE quantity_units.id = p.qu_id_purchase) AS qu_purchase_unit_name, (SELECT name_plural FROM quantity_units WHERE quantity_units.id = p.qu_id_purchase) AS qu_purchase_unit_name_plural, sc.is_aggregated_amount, sc.amount_opened_aggregated, sc.amount_aggregated, p.calories AS product_calories, sc.amount * p.calories AS calories, sc.amount_aggregated * p.calories AS calories_aggregated, p.quick_consume_amount, p.due_type FROM ( SELECT * FROM stock_current WHERE best_before_date IS NOT NULL UNION SELECT m.id, 0, 0, 0, null, 0, 0, 0, p.due_type FROM stock_missing_products_including_opened m JOIN products p ON m.id = p.id WHERE m.id NOT IN (SELECT product_id FROM stock_current) ) sc LEFT JOIN products p ON sc.product_id = p.id WHERE p.hide_on_stock_overview = 0; CREATE VIEW uihelper_stock_current_overview AS SELECT p.id, sc.amount_opened AS amount_opened, p.tare_weight AS tare_weight, p.enable_tare_weight_handling AS enable_tare_weight_handling, sc.amount AS amount, sc.value as value, sc.product_id AS product_id, sc.best_before_date AS best_before_date, EXISTS(SELECT id FROM stock_missing_products WHERE id = sc.product_id) AS product_missing, (SELECT name FROM quantity_units WHERE quantity_units.id = p.qu_id_stock) AS qu_unit_name, (SELECT name_plural FROM quantity_units WHERE quantity_units.id = p.qu_id_stock) AS qu_unit_name_plural, p.name AS product_name, (SELECT name FROM product_groups WHERE product_groups.id = product_group_id) AS product_group_name, EXISTS(SELECT * FROM shopping_list WHERE shopping_list.product_id = sc.product_id) AS on_shopping_list, (SELECT name FROM quantity_units WHERE quantity_units.id = p.qu_id_purchase) AS qu_purchase_unit_name, (SELECT name_plural FROM quantity_units WHERE quantity_units.id = p.qu_id_purchase) AS qu_purchase_unit_name_plural, sc.is_aggregated_amount, sc.amount_opened_aggregated, sc.amount_aggregated, p.calories AS product_calories, sc.amount * p.calories AS calories, sc.amount_aggregated * p.calories AS calories_aggregated, p.quick_consume_amount, p.due_type FROM ( SELECT * FROM stock_current WHERE best_before_date IS NOT NULL UNION SELECT m.id, 0, 0, 0, null, 0, 0, 0, p.due_type FROM stock_missing_products m JOIN products p ON m.id = p.id WHERE m.id NOT IN (SELECT product_id FROM stock_current) ) sc LEFT JOIN products p ON sc.product_id = p.id WHERE p.hide_on_stock_overview = 0;

The used SQLite version is 3.8.11 and I'll send you the grocy.db file but as said it's from scratch.

[berrnd]

As I assumed above, this problem is about that the database file in question was not one from a released version.

Database migrations only work between releases (since ever and as mentioned in README).

[JuergenLeber]

Sorry to bother you again but I did never use the master branch and I also don't have composer on that machine it's running. I started completely from scratch and the DB file is the one that gets created by the release. The one I sent was created by the 2.7.1 release and also new ones get created by 3.0.0 but that is then not working and throwing the error. I also deleted the file and started again and then it just gets created again.

[berrnd]

You sent me a database where migrations have run up to 0104.sql.

When a migration has been executed, the migration number is inserted into the migrations table.

This is the output of SELECT * FROM migrations ORDER BY migration DESC LIMIT 5 executed against your sent database:

In contrast, v2.7.1 only includes migrations up to 0101.sql.

The v2.7.1 tag tree proves this: https://github.com/grocy/grocy/tree/v2.7.1/migrations

The v2.7.1 release ZIP proves this: https://releases.grocy.info/v?2.7.1

Running SELECT * FROM migrations ORDER BY migration DESC LIMIT 5 against a database created by the v2.7.1 release shows this:

I have no explanation how your database got those migrations > 101, but it's not possible that you used a released version to get this.

As written by mail, each created/changed view since migration 102 would need to be checked manually if you want to fix this - running an unreleased version is up to you as mentioned in README.

I also deleted the file and started again and then it just gets created again.

Have you downloaded the v2.7.1 release ZIP from here or here (the only "official" sources)? It's simply impossible that the database you sent me was populated by those release as explained above...

[JuergenLeber]

The database was from a 2.7.1 which I updated to 3.0.0 - and then the error came again. I'll make a fresh install (without DB) of 3.0.0 now and send you the DB file that gets created by default.