I am at my wits end and realise the problem I have is not very defined. I am happy to provide anything else necessary for trouble shooting.
I am trying to deploy a web app that I have build. It is running fine on my local computer running a python 3.10 venv on pycharm and a local mysql database. I have cloned the git repository on the Linux VPS running ubuntu 22.04. I have installed all requirements and a mysql server with a user 'gnome' and a database 'gnome_hat'.
on sudo mysql -u root I can use gnome_hat, but SHOW TABLES; indicates it is empty (this is expected since I want them to be created when I first initialise my db.models through flask
my .env is set to DATABASE_URL=mysql+pymysql://gnome:@localhost:3306/gnome_hat I know this is working because I used mysql root on the local machine and that (obviously) didn't work and I was told I couldnt use that user, changing the .env worked.
However when I use flask db upgrade in the venv I am given a number of problems in the code described as sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'gnome_hat.stakeholder' doesn't exist") and (Background on this error at: https://sqlalche.me/e/20/f405). However, I expect the database to be empty and the table stakeholders to not exist yet, since this initial flask db upgrade is supposed to create the tables.
(venv) root@ubuntu:~/gnome_hat# flask db upgrade
Traceback (most recent call last):
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table 'gnome_hat.stakeholder' doesn't exist")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/root/gnome_hat/venv/bin/flask", line 8, in <module>
sys.exit(main())
^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 1063, in main
cli.main()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1682, in invoke
cmd_name, cmd, args = self.resolve_command(ctx, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/click/core.py", line 1729, in resolve_command
cmd = self.get_command(ctx, cmd_name)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 578, in get_command
app = info.load_app()
^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 312, in load_app
app = locate_app(import_name, None, raise_if_not_found=False)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/flask/cli.py", line 218, in locate_app
__import__(module_name)
File "/root/gnome_hat/app/__init__.py", line 23, in <module>
from app import routes, models, errors
File "/root/gnome_hat/app/routes.py", line 4, in <module>
import main
File "/root/gnome_hat/main.py", line 11, in <module>
for x in names:
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2832, in __iter__
result = self._iter()
^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/query.py", line 2846, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2141, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
result = conn.execute(
^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
return meth(
^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
self._handle_dbapi_exception(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
self.dialect.do_execute(
File "/root/gnome_hat/venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
cursor.execute(statement, parameters)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/root/gnome_hat/venv/lib/python3.11/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'gnome_hat.stakeholder' doesn't exist")
[SQL: SELECT stakeholder.id AS stakeholder_id, stakeholder.first_name AS stakeholder_first_name, stakeholder.last_name AS stakeholder_last_name, stakeholder.`function` AS stakeholder_function, stakeholder.organisation_id AS stakeholder_organisation_id, stakeholder.national_party_id AS stakeholder_national_party_id, stakeholder.eu_party_id AS stakeholder_eu_party_id, stakeholder.email AS stakeholder_email, stakeholder.website AS stakeholder_website, stakeholder.facebook AS stakeholder_facebook, stakeholder.twitter AS stakeholder_twitter
FROM stakeholder]
(Background on this error at: https://sqlalche.me/e/20/f405)
I have made sure the MySQL server has the relevant database and the user+password are correct
The problem that you have is that you deleted/renamed a migration script somewhere along the way. When you're working in a local environment, this can fly under the radar - the database keeps track of the UUID of the last upgrade, and checks for changes from that point forward.
It's totally different when you move to a different system; each of the modifications will be made in order. It's a chain of events that will happen, in order from the very first, and you removed a link from that chain.
Before reading further, if you suspect that this might be the case, it's worth going back through git and trying to find where this happened to restore the file(s).
There is a nuclear option to fix this (and I would welcome any other answers for a less drastic solution). You basically delete all of the existing migrations and start from scratch; this is the same as adding migrations to an existing database. The trick suggested there is to briefly swap to an in-memory SQLite3 database to generate the new migrations:
$ DATABASE_URL=sqlite:/// flask db migrate
You will also need to delete the alembic_version table from your actual database
This should be cheap and easy to do. However, there are some differences with SQLite3 and most other RDBMS and so I think it would be better if you also included naming conventions:
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData
meta = MetaData(naming_convention={
"ix": "ix_%(column_0_label)s",
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(column_0_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
})
db = SQLAlchemy(metadata=meta)
def create_app():
...
db.init_app(app)
...