ara server error in ara-web console

I'm trying to use distributed sqlite, but when I copy the database in /var/www/log/ara-report/ansible.sqlite , I'm unable to see data in the ara-web console. Here is the set up. Installed ara with API server dependencies python3 -m pip install --user ansible "ara[server]" Updated settings.yml DISTRIBUTED_SQLITE: true Started the server ara-manage runserver localhost:80 generated and copied the ansible.sqlite from another machine to the location /var/www/log/ara-report/ansible.sqlite when I hit the URL: http://localhost/ara-report


root@ritik-Latitude-E7440:/home/ritik# ara-manage runserver localhost:80
[ara] Using settings file: /root/.ara/server/settings.yaml
[ara] Using settings file: /root/.ara/server/settings.yaml
2020-05-15 16:53:00,734 INFO django.utils.autoreload: Watching for file changes with StatReloader
Performing system checks...
System check identified no issues (0 silenced).
You have 23 unapplied migration(s). Your project may not work properly until you apply the migrations for app(s): admin, api, auth, contenttypes, db, sessions.
Run 'python manage.py migrate' to apply them.
May 15, 2020 - 16:53:01
Django version 2.2.12, using settings 'ara.server.settings'
Starting development server at http://localhost:80/
Quit the server with CONTROL-C.
2020-05-15 16:53:10,429 ERROR django.request: Internal Server Error: /ara-report/
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such table: playbooks
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/views/generic/base.py", line 71, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/ara/ui/views.py", line 37, in get
    page = self.paginate_queryset(query)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/generics.py", line 171, in paginate_queryset
    return self.paginator.paginate_queryset(queryset, self.request, view=self)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/pagination.py", line 373, in paginate_queryset
    self.count = self.get_count(queryset)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/pagination.py", line 510, in get_count
    return queryset.count()
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/query.py", line 392, in count
    return self.query.get_count(using=self.db)
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/query.py", line 504, in get_count
    number = obj.get_aggregation(using, ['_count'])['_count']
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/query.py", line 489, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/compiler.py", line 1140, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/utils.py", line 89, in _exit_
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such table: playbooks
[15/May/2020 16:53:10] "GET /ara-report HTTP/1.1" 500 27
2020-05-15 16:54:12,832 ERROR django.request: Internal Server Error: /ara-report/
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such table: playbooks
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/usr/local/lib/python3.6/dist-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/django/views/generic/base.py", line 71, in view
    return self.dispatch(request, *args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/ara/ui/views.py", line 37, in get
    page = self.paginate_queryset(query)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/generics.py", line 171, in paginate_queryset
    return self.paginator.paginate_queryset(queryset, self.request, view=self)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/pagination.py", line 373, in paginate_queryset
    self.count = self.get_count(queryset)
  File "/usr/local/lib/python3.6/dist-packages/rest_framework/pagination.py", line 510, in get_count
    return queryset.count()
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/query.py", line 392, in count
    return self.query.get_count(using=self.db)
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/query.py", line 504, in get_count
    number = obj.get_aggregation(using, ['_count'])['_count']
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/query.py", line 489, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/usr/local/lib/python3.6/dist-packages/django/db/models/sql/compiler.py", line 1140, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/utils.py", line 89, in _exit_
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/dist-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such table: playbooks
[15/May/2020 16:54:12] "GET /ara-report HTTP/1.1" 500 27

Screenshot from 2020-05-15 17-00-17

May 20 '20 12:05 hritz220

Hi, I haven't looked at this yet but I'll close this issue because you've already reported it in a previous issue: https://github.com/ansible-community/ara/issues/135

May 27 '20 00:05 dmsimard

@dmsimard No it's not same as #135 , in this issue I'm creating database in different machine , and copying db in local machine using distributed sqlite, but in #135 I've created and copy db in distributed sqlite location in the same machine.

May 27 '20 15:05 hritz220

@hritz220 I highly doubt that the location where the database originates has anything to do with the problem -- whether it comes from the same machine or a different one shouldn't have any incidence.

My first guess would be something to do with linux permissions (user/group/chmod/chown) or selinux preventing access but let's find out in the other issue when I have time.

May 27 '20 17:05 dmsimard

@dmsimard FYI:When I copied the db in default location(root/.ara/server/ansible.sqlite) and distributed sqlite location(var/www/log/ara-report/ansible.sqlite) both, then only I'm able to see db of distributed sqlite in ARA web console.Please check it will be very helpfull. thanks.

May 28 '20 00:05 hritz220

Hi @hritz220,

I believe I've reproduced your issue. Could you confirm if you had left DATABASE_ENGINE to the default sqlite backend ?

I found that setting ARA_DISTRIBUTED_SQLITE: true isn't sufficient and you also need to set DATABASE_ENGINE: ara.server.db.backends.distributed_sqlite.

I think our integration tests didn't pick up this issue because there is logic that sets DATABASE_ENGINE accordingly: https://github.com/ansible-community/ara/blob/37499b64a081784438f94683f74e3d36e23e48d5/roles/ara_api/defaults/main.yaml#L182

The problem is probably because the default sqlite backend gets picked up instead of the distributed one here: https://github.com/ansible-community/ara/blob/37499b64a081784438f94683f74e3d36e23e48d5/ara/server/settings.py#L104-L106

So I guess we can either improve documentation or /really/ force the database backend which feels like what was the initial intent, anyway. I'll fix it by the next release.

Jun 06 '20 16:06 dmsimard

By changing DATABASE_ENGINE: ara.server.db.backends.distributed_sqlite, the server error is not anymore but still the HTML is not loading that I've mentioned in #135 ,please have a look on this it will be very helpful.

Jun 06 '20 19:06 hritz220

@dmsimard when using distributed sqlite, I'm using prefix (ara-report) to dynamically load different databases for example: http://localhost/ara-report is working fine for me, but when I'm hitting the url http://localhost/ , there should be some homepage which has to be loaded, currently I'm getting this response:

2020-06-07 14:11:51,794 WARNING ara.server.wsgi: Ignoring request: URL does not contain delegated prefix (ara-report)
[07/Jun/2020 14:11:51] "GET / HTTP/1.1" 404 77

Screenshot from 2020-06-07 13-55-09

Jun 07 '20 08:06 hritz220

Hi, indeed, there's no index when running distributed sqlite but I'd say this is by design because the use case is to query individual databases at the requested path.

The distributed sqlite backend is more about serving individual databases than aggregating them.

The application has no way to "guess" what databases are available and there could be hundreds or thousands.

What kind of index do you think we should have ?

Jun 07 '20 17:06 dmsimard

@dmsimard it would be better to display the list of sqlite database names in the home page.what do you think?

Jun 14 '20 17:06 hritz220

@dmsimard Moving conversation from from https://github.com/ansible-community/ara/issues/135 David: does it work if you add a trailing slash to the URL ? Me: After adding a trailing slash, it worked! Is this bug? please let me know, it will be very helpfull.

Jun 21 '20 17:06 hritz220

@hritz220 I'm not sure I would call it a bug more so than how it just happens to work that way right now.

I wouldn't be against making it work without a trailing slash but I'm not sure how and would need to research. It might have to do with how django routes URLs and how we re-map the SCRIPT_NAME and PATH_INFO: https://github.com/ansible-community/ara/blob/c60d8b194dc54f456f69c93f63308b7c4f01180c/ara/server/wsgi.py#L56-L57 https://github.com/ansible-community/ara/blob/c60d8b194dc54f456f69c93f63308b7c4f01180c/ara/server/wsgi.py#L93-L95

Jun 21 '20 20:06 dmsimard