Problem Query server has gone away

2

An error occurred in the Query part

mysql_query(): MySQL server has gone away

What kind of error 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'
    
asked by anonymous 03.03.2014 / 18:35

1 answer

5

In general the error you are seeing is due to three problems:

  

mysql_query (): MySQL server has gone away


Operations performed in PHP

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

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

Alternatively, you can check whether it is active before attempting to use it by using mysql_ping () where we can read:

  

Ping a server connection or reconnect if there is no connection

What translated:

  

Ping the connection to the server or re-establish the connection if it is not found.

To use since 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...


Insertion of data (blob's and the like)

When you perform insert queries, particularly those containing BLOB's, you are bound to exceed the maximum limits imposed on the package that is sent to MySQL.

The limits are set at (English) where we can read in the documentation:

  

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

What translated:

  

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

This value can be changed in two ways:

  • Run a query to change the limit until the server is restarted:

    SET GLOBAL max_allowed_packet=1073741824;
    
  • Change the settings file to keep changing 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 querying the database 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 the connection time in PHP to ensure that the operations run as expected.

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

    ini_set('mysql.connect_timeout', 300);
    ini_set('default_socket_timeout', 300);
    
        
    03.03.2014 / 20:27