mysql - 接口(interface)错误: 2013: Lost connection to MySQL server during query

Question

当我尝试在 Google Cloud Platform 上使用 Python 连接到 MySql 数据库服务器时，出现错误。以下是实际代码。

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)

错误如下:

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

谁能解释一下这段代码有什么问题以及为什么我会收到此错误。请解释一下我该如何解决这个问题？

Answer 1

来自Mysql documentation ，这是该问题的原因。

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

他们所说的解决方法是:

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

如何在 python 中做到这一点:

如果您使用 SQLAlchemy，请在连接引擎函数中添加超时参数，这样就可以了。

这是一个例子

from sqlalchemy import create_engine

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

pool_pre_ping 是从 this answer 获取的。 .