search

Miserlou / Zappa

Serverless Python
https://blog.zappa.io/
MIT License
11.89k stars 1.2k forks source link

SQLite attempt to write a readonly database #1785

Open Vadorequest opened 5 years ago

Vadorequest commented 5 years ago

Context

I'm just getting started with a POC/dummy app to see how Zappa works, and I wanted to go fast and keep the SQLite DB despite it not being recommended for production grade apps.

Doesn't seem like it's possible to write into the DB though

Python 3.6.8 Django==2.1.7 jet-django==0.4.8 zappa==0.47.1

Actual Behavior

Trying to save a record from the Jet Django UI, get a 500 error from Zappa API

500 {"detail":"Server Error"}

[1551123210488] [ERROR] 2019-02-25T19:33:30.485Z a3f52261-cf99-44b0-a8c6-8fe127805e93 Jet view exception
Traceback (most recent call last):
  File "/var/task/django/db/backends/utils.py", line 85, in _execute
  return self.cursor.execute(sql, params)
  File "/var/task/django/db/backends/sqlite3/base.py", line 298, in execute
  return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: attempt to write a readonly database

I tried to chmod 777 db.sqlite3 and zappa update but didn't change anything.

Possible Fix

I guess it's a limitation of SQLite? But if it's fixable it'd be nice for getting started/trying things out.

Steps to Reproduce

  1. Deploy Zappa app with sqlite
  2. Try to perform a DB update (I tried through Jet Admin API)

Your Environment

madalinoprea commented 5 years ago

You should try to set sqlite db path under /tmp, which is a writeable for a lambda function. Please be aware that lamba invocations might run on different machines and you might have different instances of sqlite. Maybe an option would be to use aurora serverless.

This is not an issue related to zappa.

Vadorequest commented 5 years ago

I tried moving the db to a newly created tmp folder but same behaviour. :/

I'm thinking about Aurora Serverless as well for production-grade app, the sqlite is just to try things out first, since I'm doing R&D with Zappa/Jet Django and other tools to see if they're reliable;

madalinoprea commented 5 years ago

You need to place sqlite in existing root tmp folder, something like /tmp/sqlite.db. That's the only writable folder in aws lambda execution environment.

See https://aws.amazon.com/lambda/faqs/: image

Aurora serverless is great for dev environment because you can configure it to shut down when there is no activity so you will not pay for anything when your app is idle and when you will have a proper MySQL compatible database that is truly persistent between lambda invocations and deployments. Cons using Aurora serverless:

But if you need to play with sqlite you will be able to use the /tmp/ folder.

Vadorequest commented 5 years ago

@madalinoprea I basically created a tmp folder and moved the DB there, then ran zappa update, but I'm not sure if that will actually deploy the DB in the lambda tmp folder.

image

Anyway, it doesn't work, so I assume that's not the correct way to do it.

I wanted to avoid all the config like Aurora, VPC and alike, but it's really well documented in https://edgarroman.github.io/zappa-django-guide/walk_database/ so i'll give it a try I guess!

madalinoprea commented 5 years ago

Sorry @Vadorequest but I was trying to suggest to place sqlite under /tmp by changing Django settings.

For example, settings.py should include something like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': '/tmp/sqlite.db',
    }
}
Vadorequest commented 5 years ago

Ah... Makes sense!

I've configured AWS Aurora serverless since then, and those cold start are really slow (30 sec to wake up the DB when paused), using the DB directly in the project is not possible for a production environment so I tried S3 but failed for some reason.

I don't know if I'll give it another try, but maybe this very simple setup aimed at testing things out could be documented in the README? would help other getting started faster with a proof of concept. :)

raamshivajigoulikar commented 5 years ago

@Vadorequest , Are you able to solve the issue for the SQLite DB? What changes have you made to resolve this? Can you give a snapshot of the settings.py file and the folder structure created to resolve this?

Vadorequest commented 5 years ago

Yes, I was able to use SQLite with Zappa on AWS. But the DB was uploaded inside the lamda and not on S3 with my setup. I stopped this project so I don't remember exact details, but I think the DB was in readonly mode and didn't succeed storing it on S3. I eventually used Aurora on AWS and SQLite for local development.

Here is the code, but I don't think it'll help much:

if ENV == 'development':
  # Use sqlite for local development
  DATABASES = {
    'default': {
      'ENGINE': 'django.db.backends.sqlite3',
      'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    # 'default': {
    #   'ENGINE': 'django.db.backends.mysql',
    #   'NAME': 'tfp_development',  # Doesn't allow dash
    #   'USER': 'root',
    #   'PASSWORD': 'pass',
    #   'HOST': '127.0.0.1',
    #   'PORT': '3306',
    # }
  }
elif ENV == 'staging':
  # Use AWS RDS Aurora Serverless for AWS Staging environment
  DATABASES = {
    # 'default': {
    #   'ENGINE': 'zappa_django_utils.db.backends.s3sqlite',  # Uses zappa_django_utils
    #   'NAME': 'db.sqlite3',
    #   'BUCKET': 'tfp-backoffice-sqlite-db',
    # },
    'default': {
      'ENGINE': 'django.db.backends.mysql',
      'NAME': 'tfp_backoffice_test',  # Doesn't allow dash
      'USER': 'backoffice_test',
      'PASSWORD': 'backoffice_test',
      'HOST': 'cluster-aurora-serverless-tfp-backoffice-test.cluster-cujbasbnpgcg.eu-west-1.rds.amazonaws.com',
      'PORT': '3306',
    }
  }
else:
  raise ImproperlyConfigured('No DATABASES configured for ENV "{ENV}"'.format(ENV=ENV))
raamshivajigoulikar commented 5 years ago

@Vadorequest , Thank you very much for your solution. I am trying to to deploy the same application from local machine with SQLite DB to AWS lambda unfortunately while logging into Django Admin its throwing an error saying that the DB is readonly. So my question is was the SQLite DB successful on Lambda for you or did you had the same issue readonly? In case it was successful do you remember if you have changed the settings.py as suggested and created a /tmp/sqlite.db structure?

Vadorequest commented 5 years ago

It was read-only too. You must upload the dB on s3 in order to pull from s3, write data inside it and replace it on s3. Look for packages that does this, like s3 Django sqlite, I was unable to do it but didn't try much

On Fri, Apr 5, 2019, 14:43 raamshivajigoulikar notifications@github.com wrote:

@Vadorequest https://github.com/Vadorequest , Thank you very much for your solution. I am trying to to deploy the same application from local machine with SQLite DB to AWS lambda unfortunately while logging into Django Admin its throwing an error saying that the DB is readonly. So my question is was the SQLite DB successful on Lambda for you or did you had the same issue readonly? In case it was successful do you remember if you have changed the settings.py as suggested and created a /tmp/sqlite.db structure?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Miserlou/Zappa/issues/1785#issuecomment-480261603, or mute the thread https://github.com/notifications/unsubscribe-auth/ADoY4l76gQAOWPveLTABzNImWAa0X-J4ks5vd0T3gaJpZM4bQr9S .

amitfriedman93 commented 4 years ago

@raamshivajigoulikar Did you manage to solve this? I am having the same issue.

sriharshavardhant commented 4 years ago

The only solution to use sqlite db for AWS lambda is to serve sqlite db from S3 bucket. So pip install django-s3-sqlite and properly configured database settings as mentioned in instructions and remember to place _sqlite.so binary in project root directory as mentioned in documentation.