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()

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? — Haakon
– Haakon, Commented Jan 22, 2013 at 10:16

Burhan Khalid · Accepted Answer · 2012-10-24 09:28:23Z

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()

answered Oct 24, 2012 at 9:28

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1597652 Over a year ago

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...

Burhan Khalid Over a year ago

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

user1597652 Over a year ago

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...

Daniël van Eeden · Accepted Answer · 2013-06-07 06:48:03Z

1

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.

answered Jun 7, 2013 at 6:48

Daniël van Eeden

6535 silver badges7 bronze badges

Comments

Anthony Fammartino · Accepted Answer · 2015-02-19 22:06:03Z

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.

Collectives™ on Stack Overflow

MySql connector dies in Python

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related