MySQL connection lost in While PHP [closed]

Question

MySQL connection lost in While PHP [closed]

Navigation

#1 by (1 votes)

0

I make a query via ajax, but your return is:

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've added it at the beginning of the file:

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

But it did not work. In the while SQL, the query is made on a basis of about 250,000 records. When trying to execute sql in heidisql the return is

Lost connection to MySQL server during query

SQL example

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

php while

asked by anonymous 04.12.2017 / 15:08

1 answer

ListBox - Multiply values and move to another Listbox Error trying to minify js files in grunt

score 1 · Accepted Answer

Using init_set will not always have an effect on php.ini

What I believe to be your problem is that you have created a query that has many results, because you are probably not using LIMIT .

Another possibility may be the amount of data returned in buffer , which should be exceeding max_allowed_packet (or max_packet_size ) set to my.cnf , however this may actually be also a problem in your code, assuming your SELECT or in your table, then the problems can 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, I could even say, edit my.cnf and increase the limits, but this would be bad for the server, if you can work within limits, even if it is necessary to increase only a little bit of them, 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 it can be that the server becomes very slow for all users.

On the BLOB (if using) I recommend you read this:

Is it wrong to write image byte in the database?

On SELECT see if it really is necessary to bring all the fields, I do not know if it is the buffer problem, but if you need all the same fields then you can try using unbuffered result, 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 using 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)) {
       ...
   }
}