action #28429

[tools][sprint 201711.2] Sqlite support busted

Added by zluo about 2 years ago. Updated almost 2 years ago.

Status:ResolvedStart date:27/11/2017
Priority:HighDue date:
Assignee:coolo% Done:

0%

Category:Feature requests
Target version:Done
Difficulty:
Duration:

Description

I have problem with checking my local openQA server for workers:

I got then bad gateway:
log for openqa-webui looks as following:

Nov 27 10:37:44 e13 openqa[13646]: [13658:debug] Key is for user 'Demo'
Nov 27 10:37:44 e13 openqa[13646]: [13658:debug] API auth by user: Demo, operator: 1
Nov 27 10:37:44 e13 openqa[13646]: [13658:debug] Routing to controller "OpenQA::WebAPI::Controller::API::V1::Job" and action "update_status"
Nov 27 10:37:44 e13 openqa[13646]: [13658:debug] 200 OK (0.010465s, 95.557/s)
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] POST "/api/v1/jobs/4603/status"
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] Routing to controller "OpenQA::WebAPI::Controller::API::V1" and action "auth_operator"
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] API key from client: 93D1DF6E12C1B5FE
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] Key is for user 'Demo'
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] API auth by user: Demo, operator: 1
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] Routing to controller "OpenQA::WebAPI::Controller::API::V1::Job" and action "update_status"
Nov 27 10:37:54 e13 openqa[13646]: [13664:debug] 200 OK (0.010832s, 92.319/s)
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] POST "/api/v1/jobs/4603/status"
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] Routing to controller "OpenQA::WebAPI::Controller::API::V1" and action "auth_operator"
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] API key from client: 93D1DF6E12C1B5FE
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] Key is for user 'Demo'
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] API auth by user: Demo, operator: 1
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] Routing to controller "OpenQA::WebAPI::Controller::API::V1::Job" and action "update_status"
Nov 27 10:38:04 e13 openqa[13646]: [13675:debug] 200 OK (0.012499s, 80.006/s)
Nov 27 10:38:08 e13 openqa[13646]: [13674:debug] GET "/tests/4603/status"
Nov 27 10:38:08 e13 openqa[13646]: [13674:debug] Routing to controller "OpenQA::WebAPI::Controller::Test" and action "referer_check"
Nov 27 10:38:08 e13 openqa[13646]: [13674:debug] Unrecognized referer 'e13.suse.de'
Nov 27 10:38:08 e13 openqa[13646]: [13674:debug] Routing to controller "OpenQA::WebAPI::Controller::Running" and action "status"
Nov 27 10:38:08 e13 openqa[13646]: [13674:debug] 200 OK (0.008843s, 113.084/s)
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] GET "/admin/workers"
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] Routing to controller "OpenQA::WebAPI::Controller::Admin::Workers" and action "index"
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [1]
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] IPC finished
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [2]
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] IPC finished
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [3]
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] IPC finished
Nov 27 10:38:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [4]
Nov 27 10:39:08 e13 openqa[13646]: [13656:warn] IPC failed: org.freedesktop.DBus.Error.NoReply: Did not receive a reply. Possible causes include: the remote application did not send a reply, the message bus secu
Nov 27 10:39:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [5]
Nov 27 10:39:43 e13 openqa[13646]: [13665:error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::SQLite::db do failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/Utils.pm line 687
Nov 27 10:39:43 e13 openqa[13646]: [13665:debug] Template "exception.production.html.ep" not found
Nov 27 10:39:43 e13 openqa[13646]: [13665:debug] Rendering template "exception.html.ep"
Nov 27 10:39:43 e13 openqa[13646]: [13665:debug] Rendering template "layouts/error.html.ep"
Nov 27 10:39:45 e13 openqa[13646]: [13659:error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::SQLite::db do failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/Utils.pm line 687
Nov 27 10:40:50 e13 openqa[13646]: [13669:error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::SQLite::db do failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/Utils.pm line 687
Nov 27 10:40:58 e13 openqa[13646]: [13659:error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::SQLite::db do failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/Utils.pm line 687
Nov 27 10:40:58 e13 openqa[13646]: [13659:debug] Template "exception.production.html.ep" not found
Nov 27 10:40:58 e13 openqa[13646]: [13659:debug] Rendering template "exception.html.ep"
Nov 27 10:40:58 e13 openqa[13646]: [13659:debug] Rendering template "layouts/error.html.ep"
Nov 27 10:41:08 e13 openqa[13646]: [13656:warn] IPC failed: org.freedesktop.DBus.Error.NoReply: Did not receive a reply. Possible causes include: the remote application did not send a reply, the message bus secu
Nov 27 10:41:08 e13 openqa[13646]: [13656:debug] dispatching IPC ws_is_worker_connected to websockets: [6]
Nov 27 10:41:17 e13 openqa[13646]: [13674:error] DBIx::Class::Storage::DBI::_do_query(): DBI Exception: DBD::SQLite::db do failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/Utils.pm line 687
Nov 27 10:41:17 e13 openqa[13646]: [13674:debug] Template "exception.production.html.ep" not found
Nov 27 10:41:17 e13 openqa[13646]: [13674:debug] Rendering template "exception.html.ep"
Nov 27 10:41:17 e13 openqa[13646]: [13674:debug] Rendering cached template "layouts/error.html.ep"


Related issues

Related to openQA Project - action #25970: Profile/Optimize _workers_checker in WebSockets server Resolved 11/10/2017
Related to openQA Project - action #28961: openQA in openQA tests are broken Resolved 06/12/2017

History

#1 Updated by nicksinger about 2 years ago

I've the same issue on my local installation using the packages provided for TW. My current version is:

rpm -qf $(which openqa)
openQA-4.5.1505912862.696bb634-99.1.noarch

with the following entries in /var/log/openqa:

[Mon Nov 27 10:57:24 2017] [3508:debug] GET "/admin/workers"
[Mon Nov 27 10:57:24 2017] [3508:debug] Routing to controller "OpenQA::WebAPI::Controller::Admin::Workers" and action "index"
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [1]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [2]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [3]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [4]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [5]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [6]
[Mon Nov 27 10:57:24 2017] [websockets:debug] IPC calling ws_is_worker_connected
[Mon Nov 27 10:57:24 2017] [3508:debug] IPC finished
[Mon Nov 27 10:57:24 2017] [3508:debug] dispatching IPC ws_is_worker_connected to websockets: [7]
[Mon Nov 27 10:57:24 2017] [websockets:debug] Received from worker "45" worker_status message "$VAR1 = {
          'job' => {},
          'status' => 'free',
          'type' => 'worker_status'
        };
"
[Mon Nov 27 10:57:24 2017] [websockets:debug] Possibly worker 45 should be freed.
[Mon Nov 27 10:57:54 2017] [websockets:debug] Failed parsing status message : DBIx::Class::Storage::DBI::_exec_txn_commit(): DBI Exception: DBD::SQLite::db commit failed: database is locked at /usr/share/openqa/script/../lib/OpenQA/WebSockets/Server.pm line 306

The webui is unusable afterwards and has to be restarted to be responsive again.

#2 Updated by coolo about 2 years ago

  • Subject changed from openQA WebUI - checking Wokers encounters error for bad gateway to Sqlite support busted

ok, we will drop sqlite support - it's just not fit for the task. then we can also reduce dbix usage and concentrate on postgresql features offering better performance.

So reset your installations and use postgresql

#3 Updated by zluo about 2 years ago

@coolo I've changed database. openQA uses postgresql now and Workers can be showed correctly... thanks!

#4 Updated by coolo about 2 years ago

  • Status changed from New to In Progress
  • Assignee set to coolo
  • Target version set to Ready

I'm working to adapt the test suite to use postgresql. I'm currently playing with a temporary postgresql server started only for the test suites - to avoid permission setup problems and thelike.

#5 Updated by okurz about 2 years ago

Having real tests with postgres is of course a good idea but I would prefer to keep sqlite support. You can tell me what you expect from me to keep the support.

#6 Updated by coolo about 2 years ago

Sorry, I'm not going down that route. Sqlite just no longer works for us - developers setup their systems following the instructions and what they get is a time bomb.

#7 Updated by EDiGiacinto about 2 years ago

coolo wrote:

Sorry, I'm not going down that route. Sqlite just no longer works for us - developers setup their systems following the instructions and what they get is a time bomb.


Where is the +1 button here? :P

#8 Updated by okurz about 2 years ago

I don't know if the original error report was about #12950 . This is one limitation of sqlite I know is making a problem for us. Of course it's not suitable for a multi-user production instance but for in-memory testing as well as the initial setup system IMHO it's a preferrable choice. I also have in mind when we ask bug assignees to install openQA themselves: For them especially we should make openQA as lightweight and easy to setup as possible. I think sqlite is easier in this context then postgres.

#9 Updated by coolo about 2 years ago

So you know nothing about sqlite limitation, but want to keep it. Your arguments are not quite consistent. Anyway, I don't want to argue with you anyway: sqlite support will be dropped. And once that is done, we can even reduce the dbix usage to a sane level.

#10 Updated by okurz about 2 years ago

coolo wrote:

So you know nothing about sqlite limitation,

I know that it's based on a single file. Parallel access and write performance is low, scalability is low, setup and backup is easy. I developed programs that use sqlite but sure, never programmed on sqlite source code myself.

but want to keep it.

Sure, I want to keep the easy setup and backup. I don't see how my arguments are not consistent. Sure, don't argue with me. You made your decision and the world will not stop :) No need to answer.

#11 Updated by szarate about 2 years ago

  • Subject changed from Sqlite support busted to [tools] Sqlite support busted
  • Target version changed from Ready to Current Sprint

#12 Updated by EDiGiacinto about 2 years ago

  • Related to action #25970: Profile/Optimize _workers_checker in WebSockets server added

#13 Updated by coolo about 2 years ago

  • Subject changed from [tools] Sqlite support busted to [tools][sprint 201711.2] Sqlite support busted
  • Status changed from In Progress to Resolved

sqlite support is dropped

#14 Updated by okurz about 2 years ago

  • Status changed from Resolved to In Progress

https://github.com/os-autoinst/openQA/pull/1515 was the according PR

  • the openQA-in-openQA test for "openqa_from_git" fails with "Installing the dependencies failed Module 'Dbd::Pg' is not installed"
  • the openQA-in-openQA test for "openqa_install+publish" fails with the webui service being unable to connect to a database, probably because it is not started and configured automatically -> special setup package in spec file?

#15 Updated by szarate about 2 years ago

  • Status changed from In Progress to Resolved

@okurz that's a new issue that needs to be open.

Setting up the database during the install http://mailman.suse.de/mlarch/SuSE/openqa/2017/openqa.2017.12/msg00004.html

#16 Updated by szarate about 2 years ago

  • Related to action #28961: openQA in openQA tests are broken added

#17 Updated by zluo about 2 years ago

@okurz I have a running setup with postgres. Maybe you need to run command like: grant all privileges on database dbname to dbuser;

#18 Updated by szarate almost 2 years ago

  • Target version changed from Current Sprint to Done

Also available in: Atom PDF