PHP MySQLi Insert Query Error - Server Has Gone Away

Question

On trying to insert array of records, I am getting errors : 1. MySQL server has gone away 2. Error reading result set's header

Following are the details:

// DB2
$host1 = 'localhost';
$user1 = 'root';
$pass1 = '';
$db1   = 'my_db';
$conn1 = mysqli_connect($host1, $user1, $pass1, $db1);

....
...
...
...

//echo '<pre>'; print_r($countryArr); die;

$countryArr result :

$query = "INSERT INTO `cities` (`country_id`, `city`, `soft_delete`, `date_added`) VALUES " . implode(',', $countryArr);
mysqli_query($conn1, $query);

Error :

How many records are to be inserted? `MySQL server has gone away` is because php looses connection with mysql — Fakhruddin Ujjainwala, Mar 01 '16 at 09:45
@FakhruddinUjjainwala Around 50000 records are to be inserted. — Kunwarbir S., Mar 01 '16 at 09:47
change setting in php.ini max_execution_time = 0 (or 3600000) — Dhaval Patel, Mar 01 '16 at 09:49

score 2 · Answer 1 · answered Jan 07 '19 at 21:50

2

MySQL server has gone away may be due to exceeding limit on mysql packet size. Check MySQL variable max_allowed_packet and try to increase it.

answered Jan 07 '19 at 21:50

Vasiliy Zverev

622
5
10

Fakhruddin Ujjainwala · Accepted Answer · 2016-03-01T10:02:12.987

1

Step 1:

set_time_limit(0); // this will remove the time limit if any.

Step 2:

Also check ping to keep the connection alive, http://php.net/manual/en/mysqli.ping.php

Step 3

Try executing the inserts in batches. i.e Batch of 500 statements.

edited Mar 01 '16 at 10:02

answered Mar 01 '16 at 09:54

Fakhruddin Ujjainwala

2,493
17
26

@KunwarbirS. glad to help you. :) – Fakhruddin Ujjainwala Mar 10 '16 at 07:06

Kareem · Answer 3 · 2017-09-09T15:59:19.553

Your query takes 13+ seconds to execute while your mysql server is set to kill a connection after a number of seconds smaller than 13.

Change this row in your mysql configuration file (usually located in /etc/mysql)

wait_timeout=15 //any reasonable amount of time more than 13 seconds for your example

PS: you might also need to change the maximum number of connections allowed to reflect the change in the wait_time. to do that, change these 2 rows in mysql configuration file.

max_connections=90
max_user_connections=60

Changing the max connections could be necessary, depending on the activity of your server. For example, if your timeout is 5 and your max connections is 30, and assuming all queries are long quires taking exactly 5 seconds to process, then your server can only serve 6 connections at a time.

Now assuming you change the timeout to 15 and the quires take 15, your server will only be able to serve 2 connections.

Hope that helps!

PHP MySQLi Insert Query Error - Server Has Gone Away

3 Answers3