MySql connector dies in Python

Question

EDIT: This problem is unbelievable. I have now managed to replace an annoying print function with the time.sleep(0.01), but why on earth I should benefit from a SLOWER execution time is beyond me.

I have a problem in iterating over my cursor in MySQL 1.0.7 connector for Python 3.23.

Unless print() the result of each iteration (which is both silly and time consuming) I get the following error raised:

raise errors.InterfaceError(errno=2013) mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

Any thoughts?

the code is trivial thusfar:

self.config = {'user': user,'password': password,'host': host,'database':     
database,'raise_on_warnings': True}

self.data = []
self.clickcnx = mysql.connector.connect(**self.config)
self.clickcursor = self.clickcnx.cursor()

query = "SELECT table1, table2, table3 FROM `db`-tables;"
self.clickcursor.execute(query)
for item in self.clickcursor:
  print(item)     #this is the strange line that I need!
  self.data.append(item)
self.clickcnx.close()

Pironi

Please post the code that is triggering this. – Dogfight

In my case, the "solution" was time.sleep(0.000001) (this was the smallest sleep time I could find that would not trigger the error). Really bizarre, but thanks for posting that. Did you ever find a proper solution? – Braque

Answer 1

You are missing the part where you actually fetch the results, you are just stepping over the cursor.

Try this version:

query = "SELECT table1, table2, table3 FROM `db`-tables;"
self.clickcursor.execute(query)
results = self.clickcursor.fetchall()
for item in results:
  print(item)     #this is the strange line that I need!
  self.data.append(item)
self.clickcnx.close()

Dogfight

The exact same error... very weird. It actually gets worse with the changes you recommended, because now the bizarre print() statement no longer helps. I must have offended the SQL-gods... – Pironi

What mysql driver are you using? mysql.connector.connect is not from MySQLdb – Dogfight

Great, you're still online. It's from their website, version 1.0.7. Where can I download the one you mention? ah ok, I see the link now. I'm in China, so links can be a little broken... – Pironi

Answer 2

I had the same issue with 1.0.7. Adding a time.sleep() fixed it. Then I upgraded to 1.0.10 and removed the sleep. That also works fine.

So the solution seems to be:

pip install --upgrade mysql-connector-python

You might need to use sudo.

Malindamalinde

Answer 3

I had the issue with MySQL connector. I tried Burhan Khalid's answer, but it did not work. I was able to solve my problem by adding a GROUP BY statement.

Original query:

SELECT
    a.ID,
    a.UID,
    g.GroupId,
FROM Accounts a
    LEFT JOIN Profile p ON p.ID = a.ID
    LEFT JOIN Group g ON g.GroupId = p.GroupId
WHERE
    a.UID IS NOT NULL
    AND a.Active = 1
    AND a.Type = 1;

Query that returns the same results as the original:

SELECT
    a.ID,
    a.UID,
    g.GroupId,
FROM Accounts a
    LEFT JOIN Profile p ON p.ID = a.ID
    LEFT JOIN Group g ON g.GroupId = p.GroupId
WHERE
    a.UID IS NOT NULL
    AND a.Active = 1
    AND a.Type = 1
GROUP BY
    a.UID;

The only difference is the second query does not throw an InterfaceError(2013). My query returns around 250,000 rows. I ran an explain on each of these queries and to my suprise, the second query takes longer (Duration) than the first because it stores the results in a temporary table, but the fetch time went from 6.00s to 0.50s!

Try to add a GROUP BY agregator and see if that solves your problem. Of course you need to ensure your results are the same. I suggest doing a GROUP BY on a table value that you know will be unique.

Mainis