美文网首页
MySQL插入数据提示OperationalError: (20

MySQL插入数据提示OperationalError: (20

作者: 星辰大海的碎片 | 来源:发表于2018-12-27 17:09 被阅读0次
    Exception in thread Thread-1:
    Traceback (most recent call last):
      File "C:\Python27\lib\threading.py", line 801, in __bootstrap_inner
        self.run()
      File "C:\Python27\lib\threading.py", line 754, in run
        self.__target(*self.__args, **self.__kwargs)
      File "-----.py", line 45, in insert_data
        insert_sql = self.generate_insert_sql(table_name,col_name_list,arr_list)
      File "-----.py", line 113, in generate_insert_sql
        self.cursor2.execute(select_sql)
      File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 205, in execute
        self.errorhandler(self, exc, value)
      File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
        raise errorclass, errorvalue
    OperationalError: (2013, 'Lost connection to MySQL server during query')
    

    原因未知
    解决办法就是不要一次插入大量数据
    产生环境:
    我在更新数据库,从旧的数据库更新到新的数据库,第一次出现这个消息是在我插入一千多条数据的时候,然后我减小了数据量,这个错误就消失了。

    MySQL文档对此的解释

    划重点

    • You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.5.2.10, “Packet too large”.
    • You can get more information about the lost connections by starting mysqld with the --log-warnings=2 option. This logs some of the disconnected errors in the hostname.err file
    • 如果向服务器发送不正确或太大的查询,也可能会出现这些错误。如果mysqld收到的数据包太大或无序,则会假定客户端出现问题并关闭连接。如果需要大查询(例如,如果使用大型BLOB列),则可以通过设置服务器的max_allowed_pa​​cket变量来增加查询限制,该变量的默认值为1MB。您可能还需要增加客户端的最大数据包大小。有关设置数据包大小的更多信息,请参见第B.5.2.10节“数据包太大”。
    • 您可以通过使用--log-warnings = 2选项启动mysqld来获取有关丢失连接的更多信息。这会记录hostname.err文件中的一些断开连接的错误

    不排除是MySQL崩了

    tips


    扩展mysql服务器的max_allowed_pa​​cket有三种方法:
    更改mysql服务器计算机上的max_allowed_packet=64M文件/etc/mysql/my.cnf并重新启动服务器
    在mysql服务器上执行sql: set global max_allowed_packet=67108864;
    Python连接到mysql后执行sql:
    connection.execute('set max_allowed_pa​​cket = 67108864')

    • 在命令行执行show variables like 'max_allowed_packet';查看 max_allowed_pa​​cket

    之前没出现这个错误是因为定时任务是每天更新的,然后我也没读代码,但是前几天写新的更新的时候发现他是以三十分钟的update_time在更新,导致我有很多信息没更新过去。

    未完待续。。。

    相关文章

      网友评论

          本文标题:MySQL插入数据提示OperationalError: (20

          本文链接:https://www.haomeiwen.com/subject/milklqtx.html