Python 35 mysql connector Resource temporarily unavailable with large requests?

Question

I am trying to use the MySQL connector as an alternative to pymysql as it supports several statements in one query for some updates that I need to do ( here is my other question related to this ) however this is not suitable for my other case of sending very large select statements .

I have a dynamically generated Select statement that retrieves all rows matching any of the specified values; e.g. 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 of whether I have compression = True or False. I also don't think this is a server-side issue, as I mentioned that the same Select statements seem to work with pymysql working with the same code and machine.

Any ideas how I can get around this?

Answer 1

Do not create this horrific IN(...) , instead insert values ​​into the table, one per row.

Then make a JOIN to the real table to get the rows you need. (Make sure col_a indexed in a real table; don't make it index in an extra table.)

If there can be duplicates in a huge list, you must first delete the list. See if Python can do this easily enough. If not, you can have one PRIMARY KEY column and make 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;