mysqli multi_query send of X bytes failed with errno=32 Broken pipe

Solution for the E_NOTICE: mysqli::multi_query(): send of X bytes failed with errno=32 Broken pipe error.

You may get the following error when executing a large multi_query with PHP mysqli:

E_NOTICE: mysqli::multi_query(): send of 1000000 bytes failed with errno=32 Broken pipe

If so, you probably need to change the max_allowed_packet variable in MySQL.

To do this, log in as root and execute the following commands:

SHOW VARIABLES LIKE 'max_allowed_packet';
SET @@global.max_allowed_packet = 16777215;
SHOW VARIABLES LIKE 'max_allowed_packet';

This will set the value globally for all connections.

The default value is typically 1048576, which is 1 Megabyte.

You can also configure this value via the my.cnf MySQL configuration file.

MySQLi multi_query Performance Benefits

PHP MySQLi multi_query is an excellent solution when you are executing a large number of queries over a high-latency network connection.

In my test, 3200 insert statements of 2 MB of data took 208 seconds (almost 4 minutes) executing the statements individually, but only 12 seconds using multi_query. In this example, the latency was approximately 0.06 seconds per query, with the connection between two servers on each coast of the USA.

---