Sqlite3 Error when running task #13241

bmccorm2 · 2020-12-22T03:19:10Z

bmccorm2
Dec 22, 2020

Hi all -

I think i have a an environment issue but I was able to find much on the error. When i run my tasks (through CLI or the UI) they seem to complete successfully but they never report a success because it is throwing this exception when trying to write to the db. I just have a vanilla installation and a simple DAG to run (prints out the date).

Any help would be appreciated. Thanks!

CLI

(venv) br640814@ts.jhg.global@dldensbxapp05 ~/a/dags> airflow tasks test test date 2020-12-21
[2020-12-21 20:09:25,151] {dagbag.py:440} INFO - Filling up the DagBag from /home/ts.jhg.global/br640814/airflow/dags
[2020-12-21 20:09:25,181] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): create_entry_group>, delete_entry_group already registered
[2020-12-21 20:09:25,181] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): delete_entry_group>, create_entry_group already registered
[2020-12-21 20:09:25,181] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): create_entry_gcs>, delete_entry already registered
[2020-12-21 20:09:25,182] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): delete_entry>, create_entry_gcs already registered
[2020-12-21 20:09:25,182] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): create_tag>, delete_tag already registered
[2020-12-21 20:09:25,182] {baseoperator.py:1151} WARNING - Dependency <Task(BashOperator): delete_tag>, create_tag already registered
[2020-12-21 20:09:25,184] {baseoperator.py:1151} WARNING - Dependency <Task(_PythonDecoratedOperator): prepare_email>, send_email already registered
[2020-12-21 20:09:25,185] {baseoperator.py:1151} WARNING - Dependency <Task(EmailOperator): send_email>, prepare_email already registered
[2020-12-21 20:09:25,232] {taskinstance.py:826} INFO - Dependencies all met for <TaskInstance: test.date 2020-12-21T00:00:00+00:00 [None]>
[2020-12-21 20:09:25,242] {taskinstance.py:826} INFO - Dependencies all met for <TaskInstance: test.date 2020-12-21T00:00:00+00:00 [None]>
[2020-12-21 20:09:25,242] {taskinstance.py:1017} INFO -
--------------------------------------------------------------------------------
[2020-12-21 20:09:25,242] {taskinstance.py:1018} INFO - Starting attempt 1 of 2
[2020-12-21 20:09:25,243] {taskinstance.py:1019} INFO -
--------------------------------------------------------------------------------
[2020-12-21 20:09:25,243] {taskinstance.py:1038} INFO - Executing <Task(BashOperator): date> on 2020-12-21T00:00:00+00:00
[2020-12-21 20:09:25,301] {taskinstance.py:1396} ERROR - (sqlite3.OperationalError) near ",": syntax error
[SQL: DELETE FROM rendered_task_instance_fields WHERE rendered_task_instance_fields.dag_id = ? AND rendered_task_instance_fields.task_id = ? AND (rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id, rendered_task_instance_fields.execution_date) NOT IN (SELECT rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id, rendered_task_instance_fields.execution_date
FROM rendered_task_instance_fields
WHERE rendered_task_instance_fields.dag_id = ? AND rendered_task_instance_fields.task_id = ? ORDER BY rendered_task_instance_fields.execution_date DESC
 LIMIT ? OFFSET ?)]
[parameters: ('test', 'date', 'test', 'date', 30, 0)]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Packages

(venv) br640814@ts.jhg.global@dldensbxapp05 ~/a/dags> pip list
Package                         Version
------------------------------- ---------
alembic                         1.4.3
apache-airflow                  2.0.0
apache-airflow-providers-ftp    1.0.0
apache-airflow-providers-http   1.0.0
apache-airflow-providers-imap   1.0.0
apache-airflow-providers-sqlite 1.0.0
apispec                         3.3.2
argcomplete                     1.12.2
attrs                           20.3.0
Babel                           2.9.0
cached-property                 1.5.2
cachetools                      4.2.0
cattrs                          1.0.0
certifi                         2020.12.5
cffi                            1.14.4
chardet                         3.0.4
click                           7.1.2
clickclick                      20.10.2
colorama                        0.4.4
colorlog                        4.0.2
commonmark                      0.9.1
connexion                       2.7.0
croniter                        0.3.36
cryptography                    3.3.1
dataclasses                     0.7
defusedxml                      0.6.0
dill                            0.3.3
dnspython                       2.0.0
docutils                        0.16
email-validator                 1.1.2
Flask                           1.1.2
Flask-AppBuilder                3.1.1
Flask-Babel                     1.0.0
Flask-Caching                   1.9.0
Flask-JWT-Extended              3.25.0
Flask-Login                     0.4.1
Flask-OpenID                    1.2.5
Flask-SQLAlchemy                2.4.4
flask-swagger                   0.2.13
Flask-WTF                       0.14.3
funcsigs                        1.0.2
google-auth                     1.24.0
graphviz                        0.15
gunicorn                        19.10.0
idna                            2.10
importlib-metadata              1.7.0
importlib-resources             1.5.0
inflection                      0.5.1
iso8601                         0.1.13
itsdangerous                    1.1.0
Jinja2                          2.11.2
json-merge-patch                0.2
jsonschema                      3.2.0
kubernetes                      12.0.1
lazy-object-proxy               1.4.3
lockfile                        0.12.2
Mako                            1.1.3
Markdown                        3.3.3
MarkupSafe                      1.1.1
marshmallow                     3.10.0
marshmallow-enum                1.5.1
marshmallow-oneofschema         2.1.0
marshmallow-sqlalchemy          0.23.1
natsort                         7.1.0
numpy                           1.19.4
oauthlib                        3.1.0
openapi-spec-validator          0.2.9
pandas                          1.1.5
pendulum                        2.1.2
pep562                          1.0
pip                             20.2.4
prison                          0.1.3
psutil                          5.8.0
pyasn1                          0.4.8
pyasn1-modules                  0.2.8
pycparser                       2.20
Pygments                        2.7.3
PyJWT                           1.7.1
pyrsistent                      0.17.3
python-daemon                   2.2.4
python-dateutil                 2.8.1
python-editor                   1.0.4
python-nvd3                     0.15.0
python-slugify                  4.0.1
python3-openid                  3.2.0
pytz                            2020.4
pytzdata                        2020.1
PyYAML                          5.3.1
requests                        2.23.0
requests-oauthlib               1.3.0
rich                            9.2.0
rsa                             4.6
setproctitle                    1.2.1
setuptools                      51.1.0
six                             1.15.0
SQLAlchemy                      1.3.22
SQLAlchemy-JSONField            1.0.0
SQLAlchemy-Utils                0.36.8
swagger-ui-bundle               0.0.8
tabulate                        0.8.7
tenacity                        6.2.0
termcolor                       1.1.0
text-unidecode                  1.3
thrift                          0.13.0
typing                          3.7.4.3
typing-extensions               3.7.4.3
tzlocal                         1.5.1
unicodecsv                      0.14.1
urllib3                         1.25.11
websocket-client                0.57.0
Werkzeug                        1.0.1
wheel                           0.29.0
WTForms                         2.3.3
zipp                            3.4.0
WARNING: You are using pip version 20.2.4; however, version 20.3.3 is available.
You should consider upgrading via the '/home/ts.jhg.global/br640814/airflow/venv/bin/python3 -m pip install --upgrade pip' command.

DAG

from airflow import DAG
from airflow.operators.bash_operator import BashOperator
from datetime import datetime, timedelta


default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2020, 12, 22),
    'email': ['airflow@example.com'],
    'email_on_failure': False,
    'email_on_retry': False,
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
    # 'queue': 'bash_queue',
    # 'pool': 'backfill',
    # 'priority_weight': 10,
    # 'end_date': datetime(2016, 1, 1),
}

dag = DAG(
    'test',
    default_args=default_args,
    schedule_interval=timedelta(days=1))

# t1, t2 and t3 are examples of tasks
# created by instantiating operators
t1 = BashOperator(
    task_id='date',
    bash_command='date',
    dag=dag)

Answered by potiuk

Dec 22, 2020

This is the version:

root@cdc6c7cda35e:/opt/airflow# sqlite3 --version
3.27.2 2019-02-25 16:06:06 bd49a8271d650fa89e446b42e513b595a717b9212c91dd384aab871fc1d0alt1

I tried to manually run the query in my sqlite and I did not get syntax error, so i think it is indeed the version problem. you can try it yourself:

airfow db shell

Above should drop you to the interactive sqlite3 connected to your database.

Then copy&paste this query:

DELETE FROM rendered_task_instance_fields WHERE rendered_task_instance_fields.dag_id = 'test' AND rendered_task_instance_fields.task_id = 'date' AND (rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id, rendered_task_instance_fields.execu…

View full answer

potiuk · 2020-12-22T15:27:26Z

potiuk
Dec 22, 2020
Collaborator

I think it might be due to some old sqlite3 version you have installed. Can you please upgrade it to the latest and see if it continue to happen ?

1 reply

bmccorm2 Dec 22, 2020
Author

Would you happen to know if this version is supported? Otherwise i'll start asking our admin if i can use different repos; the system is fully upgraded as of now.

(venv) br640814@ts.jhg.global@dldensbxapp05 ~/a/dags> yum list installed | grep sql
postgresql-libs.x86_64            9.2.24-4.el7_8             @rhel-7-server-rpms
sqlite.x86_64                     3.7.17-8.el7_7.1           @rhel-7-server-rpms

potiuk · 2020-12-22T16:57:06Z

potiuk
Dec 22, 2020
Collaborator

This is the version:

root@cdc6c7cda35e:/opt/airflow# sqlite3 --version
3.27.2 2019-02-25 16:06:06 bd49a8271d650fa89e446b42e513b595a717b9212c91dd384aab871fc1d0alt1

I tried to manually run the query in my sqlite and I did not get syntax error, so i think it is indeed the version problem. you can try it yourself:

airfow db shell

Above should drop you to the interactive sqlite3 connected to your database.

Then copy&paste this query:

DELETE FROM rendered_task_instance_fields WHERE rendered_task_instance_fields.dag_id = 'test' AND rendered_task_instance_fields.task_id = 'date' AND (rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id, rendered_task_instance_fields.execution_date) NOT IN (SELECT rendered_task_instance_fields.dag_id, rendered_task_instance_fields.task_id, rendered_task_instance_fields.execution_date
FROM rendered_task_instance_fields
WHERE rendered_task_instance_fields.dag_id = 'test' AND rendered_task_instance_fields.task_id = 'date' ORDER BY rendered_task_instance_fields.execution_date DESC
 LIMIT 30 OFFSET 0);

In my case it just run (not deleting anything but there was no syntax error).

I guess you will get syntax error when you try it yourself. Your version is REALLY OLD (7 years). :

https://sqlite.org/changes.html

2013-05-20 (3.7.17)

So I am pretty sure that's the problem.

0 replies

bmccorm2 · 2020-12-22T23:38:01Z

bmccorm2
Dec 22, 2020
Author

Yup you were right. I was able to get an upgrade to RHEL8 with sqlite 3.26.0 (welcome to 2018 me!) and it works perfectly fine now! Thanks again.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sqlite3 Error when running task #13241

{{title}}

Replies: 3 comments 1 reply

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Sqlite3 Error when running task #13241

bmccorm2 Dec 22, 2020

Replies: 3 comments · 1 reply

potiuk Dec 22, 2020 Collaborator

bmccorm2 Dec 22, 2020 Author

potiuk Dec 22, 2020 Collaborator

bmccorm2 Dec 22, 2020 Author

bmccorm2
Dec 22, 2020

Replies: 3 comments 1 reply

potiuk
Dec 22, 2020
Collaborator

bmccorm2 Dec 22, 2020
Author

potiuk
Dec 22, 2020
Collaborator

bmccorm2
Dec 22, 2020
Author