InterfaceError (0, '')

Question

I have built a site using Django and I am receiving this annoying error when I am trying to execute a query.

If I restart the Apache server, the error will go away for a short time.

Traceback:
File "/usr/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
100.                     response = callback(request, *callback_args, **callback_kwargs)
File "/home/fran/cron/views/set_caches.py" in set_caches
24.         cursor.execute(query, [category['id']])
File "/usr/local/lib/python2.7/site-packages/django/db/backends/util.py" in execute
15.             return self.cursor.execute(sql, params)
File "/usr/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
86.             return self.cursor.execute(query, args)
File "build/bdist.linux-i686/egg/MySQLdb/cursors.py" in execute
155.         charset = db.character_set_name()

Exception Type: InterfaceError at /blablabla/
Exception Value: (0, '')

Remember to accept if the solutions below work for you. – Sofia Bravo Jun 09 '15 at 02:18 — Sofia Bravo, Jun 09 '15 at 02:18

scum · Accepted Answer · 2015-06-10T16:57:20.687

77

This is caused by a global cursor. Try creating and closing the cursor within each method a raw query is needed.

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

edited Jun 10 '15 at 16:57

answered Sep 03 '11 at 14:10

scum

3,202
1
29
27

2

What is wrong with a global cursor? I'm having this problem and trying to figure out what's going on. – xitrium Oct 27 '11 at 18:07
Nothing wrong with a global cursor. It's just the cause of this particular problem. I was never able to actually figure out what was going on. Since it is an inconsistent error, I assume it is a bug in MySQL-python. – scum Jun 29 '16 at 15:56
1

Look at the answer below from Moberg - it was the right cause in my case – davalo Sep 04 '16 at 00:52
1

I am using a local cursor, and still facing the issue, can anyone help? – Bhargav Dec 13 '17 at 05:52

Moberg · Answer 2 · 2016-10-03T08:39:06.150

53

You get this error when you have a db.close() call and later try to access the database without creating a new connection. Try to find if you close the connection to the database when you don't mean to.

edited Oct 03 '16 at 08:39

answered Jan 15 '15 at 11:47

Moberg

5,253
4
38
54

6

this can be done by checking: `if( not db_handle.open): db_handle = MySQLdb.connect(**login_data)` – hardmooth Aug 18 '15 at 11:27
Personally, I am not sure that this is the problem, at least in my case. I did not have any `.close()` in my python scripts. However, I did get this error every time I was restarting my local server in the morning (after closing in the evening the day before). The only relevant thing that I had in my python scripts is a global cursor and not a local cursor for each function which access my database. So I am more in favour of @scrum and his answer... – Outcast Jun 15 '18 at 16:37
@Poete_Maudit: Well it is definitely the case that you can get this exception as described. Perhaps there are other ways to trigger the same exception. – Moberg Jun 19 '18 at 06:41
2

Also happens after timeouts, not just after an active close() – Andreas Apr 28 '22 at 08:26

score 5 · Answer 3 · answered Jan 05 '16 at 06:50

I agreed with Moberg. This error is caused when we try to access the database after we have closed the connection. This could be caused by some wrong indentation in the code. Below is my code.

conn = connect()
cur = conn.cursor()
tk = get_tickers(cur)
for t in tk:
    prices = read_price(t, cur)
    if prices != None:
        update_price(t, cur)
        print 'Price after update of ticker ', t, ':'
        p_open, p_high, p_low, p_close = read_price(t, cur)
        print p_open, p_high, p_low, p_close
    else:
        print 'Price for ', t, ' is not available'
    conn.close()

I got the same error as reported by Marian. After dedenting conn.close(), everything worked well. Confirmed that global conn is not an issue.

score 3 · Answer 4 · answered Jun 28 '12 at 14:12

3

I can confirm this is caused by a global cursor which is then later used in some functions. My symptoms were the exact same: intermittent interface errors that would temporarily be cleared up by an apache restart.

from django.db import connection
cursor = connection.cursor() # BAD

def foo():
    cursor.execute('select * from bar')

But, I am using Django on top of Oracle 11.2 so I do not believe this is a bug in the MySQL/python driver. This is probably due to the caching done by apache/mod_wsgi.

answered Jun 28 '12 at 14:12

Patrick

141
1
5

1

It is not django specific. I get this problem with cherrypy running behind apache on bluehost, and also with cherrypy behind nginx running on webfaction. It is a mysql-python problem that I can fix by closing the cursor each time after fetching. Errors were intermittent for me too. – Marc Maxmeister Nov 10 '12 at 02:53

score 3 · Answer 5 · answered Apr 04 '19 at 14:04

I had the same problem as for April of 2019 using python 3.7 and Mysql 2.7.

At intermittent intervals, the string (0, '') would be added at random to my SQL statements causing errors. I have solved the issue by commenting on the closing of the database connection and just leaving the closing of the cursors across my code.

def set_db():
    db = pymysql.connect(host='localhost',
                         user="root",
                         passwd="root",
                         db="DATABASE")
    return db


def execute_sql(cnx, sql_clause, fetch_all):

    if sql_clause and sql_clause is not None:
        try:
            cnx.execute(sql_clause)
        except Exception as e:
            print("Error in sql: " + sql_clause + str(e))
            return 0
            pass

        if fetch_all:
            result = cnx.fetchall()
        else:
            result = cnx.fetchone()

        return result
    else:
        print("Empty sql.")
        return 0

db = set_db()
cnx = db.cursor()
sql = "SELECT * FROM TABLE"
result = execute_sql(cnx, sql, 1)
cnx.close() #close the cursor
#db.close #do not close the db connection

...

score 3 · Answer 6 · answered Mar 09 '21 at 15:07

I had the same issue using threading with Python3 and Pymysql. I was getting deadlocks and then I would get hit with InterfaceError (0, '').

My issue was that I was trying to do a rollback on exception of the query- I believe this rollback was trying to use a connection that no longer existed and it was giving me the interface error. I took this rollback out (because I am OK with not doing rollback for this query) and I just let things go. This fixed my issue.

def delete_q_msg(self, assetid, queuemsgtypeid, msgid):
    """
    Given the paramerts below remove items from the msg queue equal to or older than this.
    If appropriate send them into a history table to be processed later

    :param assetid:
    :param queuemsgtypeid:
    :param msgid:
    :return:
    """
    params = (assetid, queuemsgtypeid, msgid,)
    db_connection = self._connect_to_db()
    sp_sql = "{db}.ps_delete_q_msg".format(db=self._db_settings["database"])
    return_value = []
    try:
        with db_connection.cursor() as cursor:
            cursor.callproc(sp_sql, params)
            return_value = cursor.fetchall()
        db_connection.commit()
    except Exception as ex:
        # i think we dont want rollback here
        # db_connection.rollback()
        raise Exception(ex)
    finally:
        db_connection.close()

    return return_value

score 0 · Answer 7 · answered May 31 '20 at 11:44

I had the same issue with Flask+pymysql, I was getting an empty tuple as a result in the except: block, something like this "(\"(0, '')\",)" to be specific.

It turned out that the connection was getting closed and later the code tried accessing it which resulted into this error. So I solved it by referring to above solutions and used a function for connection which assured me a conn every time I had to access the db.

You can recreate this issue by inserting conn.close() just before accessing the cursor.

For reference I used this site which helped me solve this issue.

https://hackersandslackers.com/python-mysql-pymysql/

score 0 · Answer 8 · answered Jun 24 '20 at 07:17

For me, removing the conn.close() from my function worked. I was trying to access the database again after closing. I am using flask with AWS. Also you can try to restart your flask application if it has been running for a long time & if you are also using AWS RDS with MYSQL workbench like in my case, then just check whether your session is expired or not and update the access key and id.

Hope this helps.

score 0 · Answer 9 · answered Jun 10 '21 at 20:12

I had this same problem and what worked for me in Django was what is described in this answer, which consists of:

Replacing

'ENGINE': 'django.db.backends.mysql'

with

'ENGINE': 'mysql_server_has_gone_away'

on

settings.DATABASES['ENGINE']

and installing with pip the package below:

mysql_server_has_gone_away==1.0.0

score -1 · Answer 10 · answered Feb 25 '21 at 09:46

-1

with connections.cursor() as cursor:
    res=cursor.execute(sql)

answered Feb 25 '21 at 09:46

xin.chen

964
2
8
24

6

This answer has been flagged as low-quality because it is just code without an explanation. It would be great if you could explain *why* using a context manager solves the problem. – Gino Mempin Feb 25 '21 at 10:35

InterfaceError (0, '')

10 Answers10

Linked