MySQL connector python 35 Resource temporarily unavailable with large queries?

Question

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.

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)

I keep getting the same error for my select statements:

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.

Any ideas how I can get around this?

Answer 1

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

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

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,

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;