sqlite3.OperationalError: no such table: dag

[pdavis156879]

Apache Airflow version: 2.0.2

Environment:

Cloud provider or hardware configuration:
OS (e.g. from /etc/os-release): Ubuntu 20.04.2 LTS

First of all, I apologize for opening a new issue, however, and unfortunately, my previous issue was incorrectly closed since another user responded the problem was solved - which was not.

My webserver was failing to start and would boot recurrently with a series of cryptic errors. @mik-laj then suggested I increase the log verbosity via logging_level option in the [logging] section. This did indeed clarify the problem, but I have to say, its even stranger than before:

• Apparently, Airflow cannot find the 'dag' table, even though airflow-init was ran successfully.

To clarify:

• airflow-init log
• airflow-webserver log

In summary, the problem seems to be the 'dag' table not being found, as evidenced by this recurrent piece of log error:

{"log":"Traceback (most recent call last):\n","stream":"stderr","time":"2021-05-19T22:32:51.673116491Z"}
{"log":"  File \"/home/airflow/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py\", line 1277, in _execute_context\n","stream":"stderr","time":"2021-05-19T22:32:51.673193803Z"}
{"log":"    cursor, statement, parameters, context\n","stream":"stderr","time":"2021-05-19T22:32:51.673212995Z"}
{"log":"  File \"/home/airflow/.local/lib/python3.6/site-packages/sqlalchemy/engine/default.py\", line 608, in do_execute\n","stream":"stderr","time":"2021-05-19T22:32:51.673420732Z"}
{"log":"    cursor.execute(statement, parameters)\n","stream":"stderr","time":"2021-05-19T22:32:51.673437988Z"}
{"log":"sqlite3.OperationalError: no such table: dag\n","stream":"stderr","time":"2021-05-19T22:32:51.673446786Z"}
{"log":"\n","stream":"stderr","time":"2021-05-19T22:32:51.673454973Z"}
{"log":"The above exception was the direct cause of the following exception:\n","stream":"stderr","time":"2021-05-19T22:32:51.673462998Z"}

[potiuk]

The problem is that you are using sqlite database. Sqlite works in different way than postgres/mysql. Instead of having a server where different containers connect to, sqlites writes local file where the database is kept. Unless you create a shared volume which is mounted in all the containers, the database initialization you do in in "init" container is gone at the moment the container finishes. In your case, when webserver or scheduler start - each of them have their own new, fresh, non-initialized sqlite database and it is not going to work, because all of them should share a single database.

There are two ways you can approach it:

1. modify the docker compose.yaml to include such a shared volume, mount it in all containers. This requires a bit more intrisic knowledge of how docker/docker-compose works. Ideally this should be a named volume that can survive stopping/starting docker containers, so that the DB is more persistent.

2. use postgres database - in the way that the original docker compose of ours uses it. It starts a postgres service which then all the containers connect to, so the init container will initialize the database and other containers will be able to us it..

Please let me know what worked for you and I will close the issue then.

[potiuk]

I will close it for now. let me know if the issue is not solved, then I re-open it.

[pdavis156879]

The problem is that you are using sqlite database. Sqlite works in different way than postgres/mysql. Instead of having a server where different containers connect to, sqlites writes local file where the database is kept. Unless you create a shared volume which is mounted in all the containers, the database initialization you do in in "init" container is gone at the moment the container finishes. In your case, when webserver or scheduler start - each of them have their own new, fresh, non-initialized sqlite database and it is not going to work, because all of them should share a single database.

There are two ways you can approach it:

1. modify the docker compose.yaml to include such a shared volume, mount it in all containers. This requires a bit more intrisic knowledge of how docker/docker-compose works. Ideally this should be a named volume that can survive stopping/starting docker containers, so that the DB is more persistent.
2. use postgres database - in the way that the original docker compose of ours uses it. It starts a postgres service which then all the containers connect to, so the init container will initialize the database and other containers will be able to us it..

Please let me know what worked for you and I will close the issue then.

Hi @potiuk ,

Thank you so much for your assistance.

Regarding your suggestions, I would prefer to go with number 2, since it's the most standard and requires less intervention - and less place to mess up. However, I am using Airflow's original image (slightly modified for testing, which includes a common network to debug possible communication issues between services), in fact, here it is:

• docker-compose.yaml

Which makes it even stranger for the message to appear. In fact, the alchemy connection and result backend are hardcoded with a postgres connection string, so the sqlite error becomes even odder.

[pdavis156879]

@potiuk can you re-open the issue?

[potiuk]

Not really. The docker compose.yaml ONLY supports Postgres. It does not support sqlite. You can read more about why it is just a quick start and example and not anything that is customizable and supposed to work in various combinations here: #16041 . Also there is an issue about adding more examples (maybe one including sqlite as well in #16031 ) - so you can comment in that issue and maybe even you could contribute a docker compose example with working SQLite if you would like to (similarly as one user who volunteered to contribute LocalExecutor example).

[pdavis156879]

Not really. The docker compose.yaml ONLY supports Postgres. It does not support sqlite. You can read more about why it is just a quick start and example and not anything that is customizable and supposed to work in various combinations here: #16041 . Also there is an issue about adding more examples (maybe one including sqlite as well in #16031 ) - so you can comment in that issue and maybe even you could contribute a docker compose example with working SQLite if you would like to (similarly as one user who volunteered to contribute LocalExecutor example).

I understand, but I'm not trying to use it with SQL Lite. I am using the default docker image, so shouldn't it automatically use the Postgres version? There is nothing in my docker-compose that references SQL Lite.

[potiuk]

Well this is at least what your log says: "sqlite3.OperationalError: no such table: dag\n" - so likely somewhere, something modifies the variables to switch to sqlite - or maybe the variables are removed. Not sure what other modifications you have (image? docker-compose).

Did you run the "orignal" docker-compose and original image before and after your modifications to see if it's your modification that causes it? I think this is the easiest way to diagnose it (and if you can send what exactly modifications you made and where (diff's ? ) that could help.

[pdavis156879]

Problem fixed. I downed the docker-compose deleting the volume and rebooted with the official image. Seems to be running smoothly.

Thank you for your help.

[mik-laj]

We had race condition, but I fixed the problem, so we are safe now. #16180