Query server problem has Gone away

Question

An error occurred to me in the Query part

mysql_query(): MySQL server has gone away

What kind of mistake is this?

Query:

$sqlinsert = "INSERT INTO tb_trabalhador  VALUES(0,'".$Nome."','".$Morada."','".$Tipo."','".$Email."','".$AlvaraNumero."','".$AlvaraValidade."','".$AlvaraAnexo."', '".$AcidenteNumero."', 
'".$AcidenteValidade."','".$AcidenteAnexo."','".$SeguroNumero."','".$SeguroValidade."','".$SeguroAnexo."','".$FinancasValidade."','".$FinancasAnexo."','".$SocialValidade."',
'".$SocialAnexo."','".$RemuneracaoValidade."','".$RemuneracaoAnexo."','".$InstaladorNumero."','".$InstaladorValidade."','".$InstaladorAnexo."','".$MontadorNumero."','".$MontadorValidade."','".$MontadorAnexo."')";   

Structure

'tb_trabalhador', 'CREATE TABLE `tb_trabalhador` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `Nome` varchar(200) DEFAULT NULL,\n  `Morada` text,\n  `Tipo` varchar(45)
 DEFAULT NULL,\n  `Email` text,\n  `AlvaraNumero` int(11) DEFAULT NULL,\n  `AlvaraValidade` date DEFAULT NULL,\n  `AlvaraAnexo` longblob,\n  `AcidenteNumero` int(11)
 DEFAULT NULL,\n  `AcidenteValidade` date DEFAULT NULL,\n  `AcidenteAnexo` longblob,
\n  `SeguroNumero` int(11) DEFAULT NULL,\n  `SeguroValidade` date DEFAULT NULL,\n  `SeguroAnexo` longblob,\n  `FinancasValidade` date DEFAULT NULL,\n  `FinancasAnexo` longblob,\n  `SocialValidade` date DEFAULT NULL,\n  `SocialAnexo` longblob,\n  `RemuneracaoValidade` date DEFAULT NULL,\n  `RemuneracaoAnexo` longblob,\n  `InstaladorNumero` int(11) DEFAULT NULL,\n  `InstaladorValidade` date DEFAULT NULL,\n  `InstaladorAnexo` longblob,\n  `MontadorNumero` int(11) DEFAULT NULL,\n  `MontadorValidade` date DEFAULT NULL,\n  `MontadorAnexo` longblob,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8'

Answer 1

As a general rule the mistake you’re seeing is due to three problems:

mysql_query(): Mysql server has Gone away

Operations performed in PHP

If between the time you open the database connection and the time you actually interact with it you are performing operations that take time to complete, the database connection may be lost.

The recommendation here is that you do the operations you have to do and open the database connection when you really need it.

Alternatively, you can check if it is active before you try using mysql_ping() ^(English) where we can read:

Ping a server Connection or Reconnect if there is no Connection

That translated:

Ping the connection to the server or restore it if it is not found.

To use given that the mysql_ping() does not automatically reconnect:

$conn = mysql_connect('localhost', 'utilizador', 'password');
mysql_select_db('db',$conn);

// blá blá blá 

// antes de correres a consulta de inserção
if (!mysql_ping($conn)) {

  // fecha ligação anterior (mesmo que já não exista, tem que ser fechada)
  mysql_close($conn);

  // volta a ligar
  $conn = mysql_connect('localhost', 'utilizador', 'password');

  // escolhe base de dados
  mysql_select_db('db',$conn);
}

// a tua consulta para inserir aqui...

Data insertion (blob’s and related)

By performing insertion queries, particularly those containing BLOB’s, you are subject to exceeding the maximum limits imposed on the package that is sent to Mysql.

The limits are defined in max_allowed_packet ^(English) where we can read in the documentation:

You must Increase this value if you are using large BLOB Columns or long strings.

That translated:

You should increase this value if you are using large BLOB columns or long strings.

This value can be changed in two ways:

1. Run a query to change the limit until the server restarts:

   SET GLOBAL max_allowed_packet=1073741824;
   

2. Change the settings file to keep the change forever:

   In the file my.ini, include a line after [mysqld] with:

   max_allowed_packet=500M
   

Data collection

If this error occurs when we are performing database queries to collect data, the problem may be the amount of information and/or the time it takes to arrive.

For these scenarios, you need to increase connection time in PHP to ensure that operations run as expected.

At the beginning of our PHP file, we can set two values higher than the normal 60 seconds:

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