mia
/
0x0
3
8
Fork 3
Code Issues 11 Pull Requests 2 Projects Releases Wiki Activity

"sqlalchemy.exc.OperationalError: no such table" error for freshly migrated database #70

New Issue
Open
opened 2022-11-08 20:20:08 +01:00 by Ember · 2 comments
Ember commented 2022-11-08 20:20:08 +01:00
Contributor
Copy Link

Howdy! I'm setting up 0x0 for some testing with the following series of commands:

git clone https://git.0x0.st/mia/0x0.git
cd 0x0/
python -m venv venv --prompt 0x0
source venv/bin/activate.fish
pip install -r requirements.txt
mkdir instance
echo "SQLALCHEMY_DATABASE_URI = 'sqlite:///database.db'" > instance/config.py
FLASK_APP=fhost flask db upgrade
FLASK_APP=fhost flask run

However, when executing most requests, I get the error sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: file or sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: url. An example of this error, and it's full stack trace, is attached at the bottom of this issue.

The database itself seems to have the mentioned tables (although url is URL rather than url):

$ sqlite3 database.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .tables
URL              alembic_version  file

Any advice you might have is greatly appreciated. Thank you!

Full stack trace:

 * Serving Flask app 'fhost.py'
 * Debug mode: off
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:5000
Press CTRL+C to quit
[2022-11-08 14:17:20,011] ERROR in app: Exception on / [POST]
Traceback (most recent call last):
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: file

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/tmp/0x0/fhost.py", line 339, in fhost
    return store_file(request.files["file"], request.remote_addr)
  File "/tmp/0x0/fhost.py", line 267, in store_file
    sf = File.store(f, addr)
  File "/tmp/0x0/fhost.py", line 178, in store
    f = File.query.filter_by(sha256=digest).first()
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2823, in first
    return self.limit(1)._iter().first()
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2915, in _iter
    result = self.session.execute(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: file
[SQL: SELECT file.id AS file_id, file.sha256 AS file_sha256, file.ext AS file_ext, file.mime AS file_mime, file.addr AS file_addr, file.removed AS file_removed, file.nsfw_score AS file_nsfw_score 
FROM file 
WHERE file.sha256 = ?
 LIMIT ? OFFSET ?]
[parameters: ('f4d3d598ee7df08d1683ebadc7d9294aa3baa46c6271773c8f3aeb20bc33a9ae', 1, 0)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
127.0.0.1 - - [08/Nov/2022 14:17:20] "POST / HTTP/1.1" 500 -
Howdy! I'm setting up 0x0 for some testing with the following series of commands: ```sh git clone https://git.0x0.st/mia/0x0.git cd 0x0/ python -m venv venv --prompt 0x0 source venv/bin/activate.fish pip install -r requirements.txt mkdir instance echo "SQLALCHEMY_DATABASE_URI = 'sqlite:///database.db'" > instance/config.py FLASK_APP=fhost flask db upgrade FLASK_APP=fhost flask run ``` However, when executing most requests, I get the error `sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: file` or `sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: url`. An example of this error, and it's full stack trace, is attached at the bottom of this issue. The database itself seems to have the mentioned tables (although url is `URL` rather than `url`): ``` $ sqlite3 database.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. sqlite> .tables URL alembic_version file ``` Any advice you might have is greatly appreciated. Thank you! Full stack trace: ``` * Serving Flask app 'fhost.py' * Debug mode: off WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Running on http://127.0.0.1:5000 Press CTRL+C to quit [2022-11-08 14:17:20,011] ERROR in app: Exception on / [POST] Traceback (most recent call last): File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: no such table: file The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 2525, in wsgi_app response = self.full_dispatch_request() File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1822, in full_dispatch_request rv = self.handle_user_exception(e) File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1820, in full_dispatch_request rv = self.dispatch_request() File "/tmp/0x0/venv/lib64/python3.9/site-packages/flask/app.py", line 1796, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) File "/tmp/0x0/fhost.py", line 339, in fhost return store_file(request.files["file"], request.remote_addr) File "/tmp/0x0/fhost.py", line 267, in store_file sf = File.store(f, addr) File "/tmp/0x0/fhost.py", line 178, in store f = File.query.filter_by(sha256=digest).first() File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2823, in first return self.limit(1)._iter().first() File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/query.py", line 2915, in _iter result = self.session.execute( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/orm/session.py", line 1714, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection return connection._execute_clauseelement( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement ret = self._execute_context( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context self._handle_dbapi_exception( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception util.raise_( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_ raise exception File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/tmp/0x0/venv/lib64/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: file [SQL: SELECT file.id AS file_id, file.sha256 AS file_sha256, file.ext AS file_ext, file.mime AS file_mime, file.addr AS file_addr, file.removed AS file_removed, file.nsfw_score AS file_nsfw_score FROM file WHERE file.sha256 = ? LIMIT ? OFFSET ?] [parameters: ('f4d3d598ee7df08d1683ebadc7d9294aa3baa46c6271773c8f3aeb20bc33a9ae', 1, 0)] (Background on this error at: https://sqlalche.me/e/14/e3q8) 127.0.0.1 - - [08/Nov/2022 14:17:20] "POST / HTTP/1.1" 500 - ```
Ember commented 2022-11-09 01:43:06 +01:00
Author
Contributor
Copy Link

Alright so the deal here seems to have been that flask db upgrade resolves relative file paths relative to pwd, whereas flask run resolves relative to the instance directory. This resulted in two different databases, one at :database.db and one at :instance/database.db, where the first contained the correct tables, and the second was used by the webserver.

I see two possibilities here:

  • I'm using Flask wrong, being a dummy. If this is the case, I'd like to propose to use this issue to recommend an addendum to the README, so that me and other dummy's don't encounter this issue in the future as much.
  • There's a way to change this using the code. If this is the case, I'd like to use this issue to propose that that change be made, so that this is easier in the future.

Also, the quick and dirty workaround that just got my setup running was just mv database.db instance/ from the project directory.

Alright so the deal here seems to have been that `flask db upgrade` resolves relative file paths relative to `pwd`, whereas `flask run` resolves relative to the instance directory. This resulted in two different databases, one at `:database.db` and one at `:instance/database.db`, where the first contained the correct tables, and the second was used by the webserver. I see two possibilities here: - I'm using Flask wrong, being a dummy. If this is the case, I'd like to propose to use this issue to recommend an addendum to the README, so that me and other dummy's don't encounter this issue in the future as much. - There's a way to change this using the code. If this is the case, I'd like to use this issue to propose that that change be made, so that this is easier in the future. Also, the quick and dirty workaround that just got my setup running was just `mv database.db instance/` from the project directory.
mia commented 2022-11-20 13:35:54 +01:00
Owner
Copy Link

Oh, that’s unfortunate. I suppose one way to solve this is to pass an absolute path in config.py like so:

from pathlib import Path

SQLALCHEMY_DATABASE_URI = f"sqlite:///{ Path(__file__).parent.absolute() }/db.sqlite"
Oh, that’s unfortunate. I suppose one way to solve this is to pass an absolute path in `config.py` like so: ```py from pathlib import Path SQLALCHEMY_DATABASE_URI = f"sqlite:///{ Path(__file__).parent.absolute() }/db.sqlite" ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
master
Labels
Clear labels   
bug

   
duplicate

   
enhancement

   
help wanted

   
invalid

   
question

   
wontfix
No Label
bug
duplicate
enhancement
help wanted
invalid
question
wontfix
Milestone
Clear milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: mia/0x0#70
No description provided.
Delete Branch "%!s(<nil>)"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?