0.5.2 fails to run with postgres database

JoshuaPettus commented 5 months ago

When the user installs otpmanager 0.5.2 with postgres, it will install. However after the user enters in their database password they will get in, but the otp codes do not show. Instead it will spin indefinitely

Logs show: [index] Error: An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "sharedaccounts" LINE 1: ...INNER JOIN "oc_otpmanager_accounts" "accounts" ON sharedAcco... ^ GET /index.php/apps/otpmanager/accounts from IP by myAccount at Apr 11, 2024, 4:00:01 PM

matteo-convertino commented 5 months ago

ok so the problem is not so much during the installation or update, but later when you try to use it. The error does not specify the stacktrace? Or the complete query?

brycethorup commented 5 months ago

The log on the OP seemed a bit truncated. I'm getting what appears to be the same issue. Here is my log:

2024-04-15 16:05:33 2024-04-15 16:05:33.079 MDT [304] ERROR:  missing FROM-clause entry for table "sharedaccounts" at character 251
2024-04-15 16:05:33 2024-04-15 16:05:33.079 MDT [304] STATEMENT:  SELECT "sharedAccounts".*, "accounts"."period", "accounts"."digits", "accounts"."type", "accounts"."algorithm", "accounts"."counter", "accounts"."user_id" FROM "oc_otpmanager_shared" "sharedAccounts" INNER JOIN "oc_otpmanager_accounts" "accounts" ON sharedAccounts.account_id = accounts.id WHERE ("receiver_id" = $1) AND (("expired_at" IS NULL) OR ("expired_at" >= $2))

matteo-convertino commented 4 months ago

Update: this morning I simulated the error with an instance of nextcloud with postgres. Now I see how to resolve the error.

JoshuaPettus commented 4 months ago

That's great! So sorry, this week I have been traveling around and hadn't had a chance to put more information

matteo-convertino commented 4 months ago

I just published the new release. I tested on postgres, mysql and sqlite and I didn't encounter any problems, I hope it will be the same for you too. Let me know.

P.S. version 0.5.3 allows synchronization to the mobile app if it is at least version 1.5.1 (which is on github but not yet on playstore).

DaniJohni commented 4 months ago

I just tried to update from 0.4.0 to 0.5.3, sadly I still get the spinning wheel and this error, also after trying it twice

DbalException An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_otpmanager_shared" does not exist LINE 1: "accounts"."counter", "accounts"."user_id" FROM "oc_otpman... ^

/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.phpline 295

OC\DB\Exceptions\DbalException::wrap(
  [
    "Doctrine\\DBAL\\Exception\\TableNotFoundException"
  ]
)

/var/www/html/custom_apps/otpmanager/lib/Db/SharedAccountMapper.phpline 132

OC\DB\QueryBuilder\QueryBuilder->executeQuery()

/var/www/html/custom_apps/otpmanager/lib/Controller/AccountController.phpline 57

OCA\OtpManager\Db\SharedAccountMapper->findAllByReceiverJoin(
  "user_account"
)

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 230

OCA\OtpManager\Controller\AccountController->getAll()

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 137

OC\AppFramework\Http\Dispatcher->executeController(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/AppFramework/App.phpline 184

OC\AppFramework\Http\Dispatcher->dispatch(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/Route/Router.phpline 315

OC\AppFramework\App::main(
  "OCA\\OtpManager\\Controller\\AccountController",
  "getAll",
  [
    "OC\\AppFramework\\DependencyInjection\\DIContainer"
  ],
  [
    "otpmanager.account.getAll"
  ]
)

/var/www/html/lib/base.phpline 1069

OC\Route\Router->match(
  "/apps/otpmanager/accounts"
)

/var/www/html/index.phpline 39

OC::handleRequest()

Sorry that this is another negative post, please tell me if there is any information I can provide to help or anything to try.

Edit: I am too on Nextcloud AIO with Nextcloud Version 28.0.4, PostgreSQL 15.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit, PHP version 8.2.17

brycethorup commented 4 months ago

I can confirm it is working on my instance. I am running the AIO docker image in beta if that helps.

Edit: this was going from 0.5.2 to 0.5.3

matteo-convertino commented 4 months ago

I just tried to update from 0.4.0 to 0.5.3, sadly I still get the spinning wheel and this error, also after trying it twice

DbalException An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_otpmanager_shared" does not exist LINE 1: "accounts"."counter", "accounts"."user_id" FROM "oc_otpman... ^
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.phpline 295

OC\DB\Exceptions\DbalException::wrap(
  [
    "Doctrine\\DBAL\\Exception\\TableNotFoundException"
  ]
)

/var/www/html/custom_apps/otpmanager/lib/Db/SharedAccountMapper.phpline 132

OC\DB\QueryBuilder\QueryBuilder->executeQuery()

/var/www/html/custom_apps/otpmanager/lib/Controller/AccountController.phpline 57

OCA\OtpManager\Db\SharedAccountMapper->findAllByReceiverJoin(
  "user_account"
)

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 230

OCA\OtpManager\Controller\AccountController->getAll()

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 137

OC\AppFramework\Http\Dispatcher->executeController(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/AppFramework/App.phpline 184

OC\AppFramework\Http\Dispatcher->dispatch(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/Route/Router.phpline 315

OC\AppFramework\App::main(
  "OCA\\OtpManager\\Controller\\AccountController",
  "getAll",
  [
    "OC\\AppFramework\\DependencyInjection\\DIContainer"
  ],
  [
    "otpmanager.account.getAll"
  ]
)

/var/www/html/lib/base.phpline 1069

OC\Route\Router->match(
  "/apps/otpmanager/accounts"
)

/var/www/html/index.phpline 39

OC::handleRequest()
Sorry that this is another negative post, please tell me if there is any information I can provide to help or anything to try.

Edit: I am too on Nextcloud AIO with Nextcloud Version 28.0.4, PostgreSQL 15.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit, PHP version 8.2.17

The fact that it tells you Undefined table and TableNotFoundException makes me think that there is something wrong with the migrations. Could you manually check if, indeed, that table is present in your db? If it isn't there (and you're on 0.5.3) it makes me think that you hadn't removed the latest migration from the oc_migration table before doing the update.

In this case you could (instead of downgrading again and then upgrading) perform the migration manually by executing:

./occ migrations:execute otpmanager 000013Date20240213150000

If this command gives you an error (for example "This migration does not exist"), then I recommend you try this again.

JoshuaPettus commented 4 months ago

Thank you! I finally got to give it a try for my instance with the manual postgres install. Seems to work perfectly! Thanks!

DaniJohni commented 4 months ago

I just tried to update from 0.4.0 to 0.5.3, sadly I still get the spinning wheel and this error, also after trying it twice DbalException An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "oc_otpmanager_shared" does not exist LINE 1: "accounts"."counter", "accounts"."user_id" FROM "oc_otpman... ^
/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.phpline 295

OC\DB\Exceptions\DbalException::wrap(
  [
    "Doctrine\\DBAL\\Exception\\TableNotFoundException"
  ]
)

/var/www/html/custom_apps/otpmanager/lib/Db/SharedAccountMapper.phpline 132

OC\DB\QueryBuilder\QueryBuilder->executeQuery()

/var/www/html/custom_apps/otpmanager/lib/Controller/AccountController.phpline 57

OCA\OtpManager\Db\SharedAccountMapper->findAllByReceiverJoin(
  "user_account"
)

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 230

OCA\OtpManager\Controller\AccountController->getAll()

/var/www/html/lib/private/AppFramework/Http/Dispatcher.phpline 137

OC\AppFramework\Http\Dispatcher->executeController(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/AppFramework/App.phpline 184

OC\AppFramework\Http\Dispatcher->dispatch(
  [
    "OCA\\OtpManager\\Controller\\AccountController"
  ],
  "getAll"
)

/var/www/html/lib/private/Route/Router.phpline 315

OC\AppFramework\App::main(
  "OCA\\OtpManager\\Controller\\AccountController",
  "getAll",
  [
    "OC\\AppFramework\\DependencyInjection\\DIContainer"
  ],
  [
    "otpmanager.account.getAll"
  ]
)

/var/www/html/lib/base.phpline 1069

OC\Route\Router->match(
  "/apps/otpmanager/accounts"
)

/var/www/html/index.phpline 39

OC::handleRequest()
Sorry that this is another negative post, please tell me if there is any information I can provide to help or anything to try. Edit: I am too on Nextcloud AIO with Nextcloud Version 28.0.4, PostgreSQL 15.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit, PHP version 8.2.17
The fact that it tells you Undefined table and TableNotFoundException makes me think that there is something wrong with the migrations. Could you manually check if, indeed, that table is present in your db? If it isn't there (and you're on 0.5.3) it makes me think that you hadn't removed the latest migration from the oc_migration table before doing the update.

In this case you could (instead of downgrading again and then upgrading) perform the migration manually by executing:
./occ migrations:execute otpmanager 000013Date20240213150000
If this command gives you an error (for example "This migration does not exist"), then I recommend you try this again.

Sorry, I am not so that familiar with databases yet 😅. How would I go about checking if that table is present in my db? When I try running "./occ migrations:execute otpmanager 000013Date20240213150000" I get "There are no commands defined in the "migrations" namespace", I found online that I should activate debug mode, couldn't really figure out how, so I tried the other method you linked to again after downgrading to 0.4.0 but when I try running "DROP TABLE oc_otpmanager_shared;" I get "ERROR: table "oc_otpmanager_shared" does not exist"

matteo-convertino commented 4 months ago

but when I try running "DROP TABLE oc_otpmanager_shared;" I get "ERROR: table "oc_otpmanager_shared" does not exist"

Ok perfect, so the table was never created. Then I advise you to continue as you were doing, i.e. downgrading and then updating. You can safely run the other two commands.

DaniJohni commented 4 months ago

I just tried to run the other two commands, and I get "ERROR: column "otpmanager" does not exist LINE 1: DELETE FROM oc_migrations WHERE app="otpmanager" AND version..." with both commands, but when on 0.4.0 I can access the accounts, but then after upgrading it doesn't work again. Sorry to bother you and thank you so much for helping me :)

Edit: I just noticed when exiting the database I get the following warning, in case that helps "could not save history to file "/var/lib/postgresql/.psql_history": Read-only file system"

matteo-convertino commented 4 months ago

ERROR: column "otpmanager"

Oh sorry, the syntax for postgres is slightly different. Instead of double quotes, use single quotes.

DaniJohni commented 4 months ago

Thank you so much, everything works now 👍

matteo-convertino / otpmanager-nextcloud

0.5.2 fails to run with postgres database #43