MySQL连接器python 35资源暂时不可用于大型查询？

I'm trying to use MySQL connector as an alternative to pymysql as it supports multiple statements in one query for some updates I have to make (Here is my other question related to that) however it fails for my other use case of sending over very large select statements.

我正在尝试使用MySQL连接器作为pymysql的替代方案,因为它支持一个查询中的多个语句,我必须做一些更新(这是我的另一个问题)然而它失败了我的其他用例发送非常大型选择陈述。

I have a dynamically generated Select statement which retrieves all rows that match any of the specified values passed in; for example Select * from table where col_a in (val_1, val_2.... val_350,000)

我有一个动态生成的Select语句,它检索与传入的任何指定值匹配的所有行;例如,从表中选择*,其中col_a位于(val_1,val_2 .... val_350,000)

I keep getting the same error for my select statements:

我的select语句一直出现同样的错误:

Exception in thread Thread-1:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/mysql/connector/network.py", line 212, in send_compressed
    self.sock.sendall(zip_packet)
BlockingIOError: [Errno 35] Resource temporarily unavailable

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/threading.py", line 921, in _bootstrap_inner
    self.run()
  File "/Users/maldeiri/raw_data_processing/sql_retriever.py", line 22, in run
    self.mysql_cursor.execute(self.sql_statement)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/mysql/connector/cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/mysql/connector/connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/mysql/connector/connection.py", line 261, in _send_cmd
    packet_number)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/mysql/connector/network.py", line 215, in send_compressed
    errno=2055, values=(self.get_address(), _strioerror(err)))
mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at 'database_end_point:3306', system error: 35 Resource temporarily unavailable

This happens regardless if I have compress=True or False. I also don't think this is a server side issue as I mentioned the exact same Select statements seem to be working with pymysql running of the same code and machine.

如果我有compress = True或False,则会发生这种情况。我也不认为这是服务器端问题,因为我提到完全相同的Select语句似乎与pymysql运行相同的代码和机器。

Any ideas how I can get around this?

有什么想法我可以解决这个问题吗?

1 个解决方案

#1

Don't build that horrendous IN(...), instead throw the values into a table, one per row.

不要构建那个可怕的IN(...),而是将值抛出到表中,每行一个。

Then do a JOIN to the real table to get the rows you need. (Be sure that col_a is indexed in the real table; don't bother indexing it in the extra table.)

然后对真实表执行JOIN以获取所需的行。 (确保col_a在真实表中编入索引;不要在额外的表中对其进行索引。)

If the huge list can have dups, you should probably de-dup the list first. See if Python can do it easily enough. If not, you could have that one-column be the PRIMARY KEY and do INSERT IGNORE as you insert them. Or,

如果巨大的列表可以有重复,您应该首先删除列表。看看Python是否可以轻松完成。如果没有,您可以将该一列作为PRIMARY KEY,并在插入时插入IGNORE。要么,

CREATE TABLE t (val) ENGINE=MyISAM;
INSERT or LOAD DATA ... (no dedupping)
SELECT rt.* FROM real_table
    JOIN ( SELECT DISTINCT val FROM t )  ON rt.val = t.val;

#1