django.db.utils.InterfaceError: (0, '') when using django model

Question

I have django.db.utils.InterfaceError: (0, '') error on django.

I googled around and found this error is related with django mysql connection.

What I have done is just like this ,

from django.core.management.base import BaseCommand
from ...models import Issue
class Command(BaseCommand):
    def handle(self, *args, **options):
        print("dbconnection test:")
        obj = Issue.objects.get(id=1)
        print(obj.id)
        exit()

Some articles show the solution with , connection close

cursor = connection.cursor()
cursor.execute(query)
cursor.close()

but I don't even have the chance to connection.close()

Problem happens here /usr/local/lib/python3.6/site-packages/MySQLdb/connections.py

def query(self, query):
    # Since _mysql releases GIL while querying, we need immutable buffer.                                                                                            
    if isinstance(query, bytearray):
        query = bytes(query)
    _mysql.connection.query(self, query)

I really appreciate any help. thank you very much.

I added the CONN_MAX_AGE None in db settings but in vain.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
         ....
        'HOST': env('DATABASE_HOST'),
        'PORT': env('DATABASE_PORT'),
        'OPTIONS': {
            'charset': 'utf8mb4',
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
        },
        'CONN_MAX_AGE' : None ## add here
    }
}

These are the stacktrace

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.InterfaceError: (0, '')

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

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    main()
  File "manage.py", line 19, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.6/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.6/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/code/tweet/management/commands/handle_tweet.py", line 521, in handle
    twitterApi.search_tweet(keyword)
  File "/code/tweet/management/commands/handle_tweet.py", line 329, in search_tweet
    cnt = self.tagByAi()
  File "/code/tweet/management/commands/handle_tweet.py", line 103, in tagByAi
    crowded = Issue.objects.get(id=382)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 411, in get
    num = len(clone)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1137, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 74, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
django.db.utils.InterfaceError: (0, '')

myenvironment is here

absl-py                  0.9.0     
asgiref                  3.2.7     
astor                    0.8.1     
boto3                    1.12.28   
botocore                 1.15.28   
cachetools               4.0.0     
certifi                  2019.11.28
chardet                  3.0.4     
cycler                   0.10.0    
Django                   3.0.1     
django-environ           0.4.5     
django-extensions        2.2.6     
django-filter            2.2.0     
django-mysql             3.3.0     
djangorestframework      3.11.0    
docutils                 0.15.2    
gast                     0.2.2     
gensim                   3.8.1     
google-api-core          1.16.0    
google-auth              1.11.3    
google-cloud-core        1.3.0     
google-cloud-storage     1.26.0    
google-pasta             0.2.0     
google-resumable-media   0.5.0     
googleapis-common-protos 1.51.0    
grpcio                   1.27.2    
h5py                     2.10.0    
idna                     2.9       
jmespath                 0.9.5     
Keras                    2.3.1     
Keras-Applications       1.0.8     
Keras-Preprocessing      1.1.0     
kiwisolver               1.1.0     
Markdown                 3.2.1     
matplotlib               3.0.3     
mecab-python3            0.996.3   
mysqlclient              1.3.13    
neologdn                 0.4       
numpy                    1.16.2    
oauthlib                 3.1.0     
opt-einsum               3.2.0     
pandas                   0.24.2    
pandas-schema            0.3.5     
pandocfilters            1.4.2     
pip                      20.0.2    
protobuf                 3.11.3    
pyasn1                   0.4.8     
pyasn1-modules           0.2.8     
pyparsing                2.4.6     
python-dateutil          2.8.1     
pytz                     2019.3    
PyYAML                   5.3.1     
requests                 2.23.0    
requests-oauthlib        1.3.0     
rsa                      4.0       
s3transfer               0.3.3     
scikit-learn             0.20.3    
scipy                    1.4.1     
setuptools               45.2.0    
six                      1.14.0    
smart-open               1.10.0    
sqlparse                 0.3.1     
tensorboard              1.15.0    
tensorflow               1.15.2    
tensorflow-estimator     1.15.1    
tensorflow-hub           0.7.0     
termcolor                1.1.0     
urllib3                  1.25.8    
uWSGI                    2.0.17    
Werkzeug                 1.0.0     
wheel                    0.34.2    
wrapt                    1.12.1

Does this answer your question? [InterfaceError (0, '')](https://stackoverflow.com/questions/6650940/interfaceerror-0) — Tiago Martins Peres, Mar 25 '20 at 19:18
Situation is bit different , I dont use raw query just use `from django.db import models` — whitebear, Mar 25 '20 at 19:55

score 2 · Answer 1 · answered Aug 23 '21 at 15:28

My code was very similar to what is in the question:

class Command(BaseCommand):
    help = "Check order"

    def add_arguments(self, parser):
        parser.add_argument("--order-no", nargs="?", type=str)

    def handle(self, *args, **options):
        order = Orders.objects.get(order_no=options["order_no"])
        print(order)

I figured out by accident that the connection is randomly closed every now and then and it causes InterfaceError. In my case probably some other section of the code uses models on global level or maybe django.setup() is loading module that creates a connection to database, or perhaps connection is kept in memory and re-used between consecutive django calls. (No idea so far, need to dig more).

But solution to it was pretty simple, add transaction context manager.

It will look like this:

from django.db import transaction 


class Command(BaseCommand):
    help = "Check order"

    def add_arguments(self, parser):
        parser.add_argument("--order-no", nargs="?", type=str)

    def handle(self, *args, **options):
        with transaction.atomic():
            order = Orders.objects.get(order_no=options["order_no"])
            print(order)

score 0 · Answer 2 · answered Dec 04 '22 at 17:46

MySQL is lazily connected to Django. If connection.connection is None means you have not connected to MySQL before.

If the connection is closed and you try to access the cursor, you will get an InterfaceError. You can close the database connection so that when you use ORM methods Django will reconnect to open a new connection

I have created a helper reconnect() which we are using in production.

def reconnect():
  from django.db import connections
  from logging import getLogger
  
  for alias in list(connections):
    conn = connections[alias]
    if conn.connection and not conn.is_usable():
      conn.close()
      del connections[alias]
      closed.append(alias)
    getLogger(__name__).warn('Closing unusable connections: %s', closed)

I am further attaching a few helpful examples on maintaining database connections healthy

https://www.programcreek.com/python/example/100987/django.db.connection.is_usable

django.db.utils.InterfaceError: (0, '') when using django model

2 Answers2