InterfaceError: 2013: Lost connection to MySQL server during query

Question

When I'm trying to connect to the MySql Database server in Python on Google Cloud Platform, I'm getting the error. The following is the actual code.

import mysql.connector
import pandas as pd

cnx = mysql.connector.connect(user = 'xxxxxx', password='xxxxxx',
                              host='xxx.xxx.xxx.xx',
                              database='xxxxxxxxx')
cursor = cnx.cursor()
cnx.close()
if cur and con:                        
    cur.close() 
    con.close()
sql1 = "SELECT * FROM ms_trackevaluation_15_16.ms_skill"
cursor.execute(sql1)
rows = cursor.fetchall()
df1 = pd.read_sql(sql1, cnx)

The following is the error :

InterfaceError                            Traceback (most recent call last)
<ipython-input-2-30094be976fb> in <module>()
      4 cnx = mysql.connector.connect(user = 'xxxxx', password='xxxxx',
      5                               host='173.194.104.33',
----> 6                               database='xxxxx')
      7 cursor = cnx.cursor()
      8 cnx.close()

    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/__init__.py in connect(*args, **kwargs)
        177         return CMySQLConnection(*args, **kwargs)
        178     else:
    --> 179         return MySQLConnection(*args, **kwargs)
        180 Connect = connect  # pylint: disable=C0103
        181 
    
    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/connection.py in __init__(self, *args, **kwargs)
         93 
         94         if len(kwargs) > 0:
    ---> 95             self.connect(**kwargs)
         96 
         97     def _do_handshake(self):
    
    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/abstracts.py in connect(self, **kwargs)
        714 
        715         self.disconnect()
    --> 716         self._open_connection()
        717         self._post_connection()
        718 
    
    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/connection.py in _open_connection(self)
        205         self._socket = self._get_connection()
        206         self._socket.open_connection()
    --> 207         self._do_handshake()
        208         self._do_auth(self._user, self._password,
        209                       self._database, self._client_flags, self._charset_id,
    
    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/connection.py in _do_handshake(self)
         97     def _do_handshake(self):
         98         """Get the handshake from the MySQL server"""
    ---> 99         packet = self._socket.recv()
        100         if packet[4] == 255:
        101             raise errors.get_exception(packet)
    
    /home/scrollstech_shravankumar/anaconda3/lib/python3.5/site-packages/mysql/connector/network.py in recv_plain(self)
        241                 chunk = self.sock.recv(4 - packet_len)
        242                 if not chunk:
    --> 243                     raise errors.InterfaceError(errno=2013)
        244                 packet += chunk
        245                 packet_len = len(packet)
    
    InterfaceError: 2013: Lost connection to MySQL server during query

Can anyone explain what's wrong with this code and why I'm getting this error. And please explain how can I resolve this issue ?

Answer 1

From Mysql documentation, Here is the reason for that issue.

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing.

The workaround to this as they stated is to :

increase net_read_timeout from its default of 30 seconds to 60 seconds or longer

How to do that in python:

If you are using SQLAlchemy use add the timeout parameter to the connection engine function and you will be good to go.

Here is an example

from sqlalchemy import create_engine

engine = create_engine(self.writer_url, 
                       connect_args={'connect_timeout': 120},
                       pool_pre_ping=True)

The pool_pre_ping was grabed from this answer .

Answer 2

Refer following form : https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

B.5.2.3 Lost connection to MySQL server

There are three likely causes for this error message.

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing.

Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries. If you know that this is happening, you should try increasing net_read_timeout from its default of 30 seconds to 60 seconds or longer, sufficient for the data transfer to complete.

More rarely, it can happen when the client is attempting the initial connection to the server. In this case, if your connect_timeout value is set to only a few seconds, you may be able to resolve the problem by increasing it to ten seconds, perhaps more if you have a very long distance or slow connection. You can determine whether you are experiencing this more uncommon cause by using SHOW GLOBAL STATUS LIKE 'Aborted_connects'. It will increase by one for each initial connection attempt that the server aborts. You may see “reading authorization packet” as part of the error message; if so, that also suggests that this is the solution that you need.

If the cause is none of those just described, you may be experiencing a problem with BLOB values that are larger than max_allowed_packet, which can cause this error with some clients. Sometime you may see an ER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase max_allowed_packet.

Please try increasing connection timeout using:

`cnx = mysql.connector.connect(user = 'shravan2018', password='password',
                              host='173.194.104.33',
                              database='ms_trackevaluation_15_16',
                              connect_timeout=1000 ,
                              wait_timeout=28800 ,
                              interactive_timeout=28800 )`

Hope this helps.

Answer 3

If you're facing issues with your MySQL database after performing transaction control operations (commit and rollback) on a specific table, and you're unable to perform any further operations or drop the table, there are a few steps you can take to resolve the problem:

1. Restart the MySQL service: Try restarting the MySQL service or server. This can help resolve any temporary issues or locks that may be preventing further operations. After restarting, attempt to perform the necessary operations again.

2. Check for active transactions: Verify if there are any active transactions that might be causing the issue. You can use the SHOW PROCESSLIST command in MySQL to view the active processes and transactions. If you find any long-running or stuck transactions, you may need to terminate them using the KILL command.

3. Increase the timeout settings: If the issue persists and you're encountering timeout errors, you can try increasing the timeout settings in MySQL. For example, you can modify the wait_timeout and interactive_timeout variables in the MySQL configuration file (usually my.cnf or my.ini) or by running SQL statements like SET GLOBAL wait_timeout = 3600;.

4. Optimize the table: If the table you're having trouble with is large or has performance issues, you can try optimizing it. Use the OPTIMIZE TABLE command to rebuild the table's indexes and optimize its storage. This can help resolve any corruption or fragmentation issues.

5. Use the FORCE option to drop the table: If you're unable to drop the table using the regular DROP TABLE statement, you can try using the DROP TABLE statement with the FORCE option. This option forces the table to be dropped even if there are foreign key constraints or other dependencies. Use this option with caution, as it can result in data loss if not used correctly.

DROP TABLE IF EXISTS your_table_name FORCE; If none of the above steps resolve the issue, it might be necessary to consult with a database administrator or MySQL expert to investigate and resolve the specific problem you're facing.