Mysql lost connection in While PHP

Question

I make a consultation via ajax, but, your return is being:

mysqli_query(): MySQL server has gone away in
mysqli_query(): Error reading result set's header in
mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given

I already added at the beginning of the file:

ini_set('mysql.connect_timeout', 9900);
ini_set('default_socket_timeout', 9900);

But it had no effect. In while SQL, the query is made on a basis of about 250,000 records. When trying to run sql in heidisql the return is

Lost connection to MySQL server during query

Example of SQL

SELECT
id,nome,origem,cidade,telefone_residencial
FROM
mailing
INNER JOIN
atendimento_cep 
ON
atendimento_cep_cep = cep
WHERE
nome IS NOT NULL AND origem = 1
GROUP BY
id
ORDER BY
data_cadastro DESC

Answer 1

The use of init_set will not always have an effect on the php.ini

What I think is your problem is that you have created a query that is bringing many results, because you are probably not using LIMIT.

Another possibility may be the amount of data returned in buffer, what must be surpassing the max_allowed_packet (or max_packet_size) configured in the my.cnf, however this may actually be also a problem in your code, assuming that your SELECT or in your table, then the problems may be:

• SELECT is bringing many columns and probably unnecessary columns
• You are using some BLOB column and in that column you upload files

I can not say for sure what the problem, could even say, edit the my.cnf and push the boundaries, still that would be bad for the server, if you can work within the limits, even if it is necessary to increase them a little, it will be ideal for your entire server to withstand when there is a lot of traffic, now if you increase the limits of timeout and the amount of packages may be that the server becomes quite slow for all users.

About BLOB (if you are using) I recommend reading this:

• It is wrong to write byte of images in the database?

About the SELECT see if it is really necessary to bring all the fields, I do not know if it is the buffer the problem, but if you need all fields same, then you can try using the result without buffer, thus $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT):

$result = $mysqli->query('SELECT * FROM tabela', MYSQLI_USE_RESULT);

if ($result) {
   while ($row = $uresult->fetch_assoc()) {
       ...
   }
}

$uresult->close();

For those who use PDO do this:

$pdo = new PDO('mysql:host=...;dbname=....', '...', '...');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$result = $pdo->query('SELECT * FROM tabela');

if ($result) {
   while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
       ...
   }
}