How to handle Broken piper error when writing to a table using mysql?

Question

The data I'm trying to insert into my table has about 95k rows. So in essence I'm trying to insert 95k values using the executemany(sql, data) function.

My initial suspicion is that the number of sets of values being inserted is too much for the executemany() function to handle. Any ideas if that is indeed the cause or if there is anything else that could be causing the following error message?

Traceback (most recent call last): File "/Users/ssrikanthan/anaconda/envs/ilc-load/lib/python3.5/site-packages/mysql/connector/network.py", line 130, in send_plain self.sock.sendall(packet) BrokenPipeError: [Errno 32] Broken pipe

Abhishek Ginani · Accepted Answer · 2016-02-03T09:03:35.467

1

Try increasing the max_allowed_packet variable:

set global max_allowed_packet=262144000;

We can update the same from MySQL Config also as:

[mysqld]
max_allowed_packet=250M

Once updating the config you need to restart MySQL services to take effect and this is the best way to update variables. setting variables from queries is not good because values updated to defaults when MySQL services restarts.

Default value of this variable is 1MB which is not enough in some case like yours which includes large data insertion. so ,while performing large data operations like insertion,updation and dumping data from MySQL then this variable should be updated. Max limit to this variable 1GB but setting it to to 250 MB is enough in all the cases generally.

edited Feb 03 '16 at 09:03

answered Feb 03 '16 at 08:46

Abhishek Ginani

4,511
4
23
35

getting back `mysql.connector.errors.ProgrammingError: 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation ` – user4659009 Feb 03 '16 at 08:52
Yes you need to have SUPER privilege to set this variable. – Abhishek Ginani Feb 03 '16 at 08:53
@user4659009: You can also update it from MySQL config file,if you have access otherwise your DB user should have SUPER privilege to update this. Check the updated answer. – Abhishek Ginani Feb 03 '16 at 08:57
I'm using mysql workbench, I can't find where I'm meant to update the max_allowed_packet, any ideas where I might be able to find it? – user4659009 Feb 03 '16 at 09:18
You are running MySQL on windows OS? – Abhishek Ginani Feb 03 '16 at 09:19
On Mac OS unfortunately – user4659009 Feb 03 '16 at 09:20
Check this : http://stackoverflow.com/questions/10757169/mysql-my-cnf-location . It contains information related to MySQL config location in MAC OS. Try to find the config and update the variable as per the answer. – Abhishek Ginani Feb 03 '16 at 09:26

How to handle Broken piper error when writing to a table using mysql?

1 Answers1