PDO Connection Timeout After a big process... (cakephp 3.0)


#1

Hey guys, how is it going?!

I’ve done a lot of research and found nothing, so I thought the best way was to post here…
I have a Shell controller that is very simple but takes a lot of time…

First it does a find()->toArray(), and loop in each row.
For each row, it makes a backup (using phardata) and takes a lot of time… like 10mins to complete.
The problem is that, after it completes the phar, i send it to a google folder (all works, and till here, so far, so good), and after all of this, i need to update the row with the last backup date… very simples, like $this->Backup->updateAll([‘dt_last_update’=>Date(‘Y-m-d H:i:s’)], [‘id’=>$item->id]); // $item from foreach

(Ive tried $this->Backup->save() and had the same issue too)

And i get the mysql server has gone away error… with the following stack trace…

/home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php(39): PDOStatement->execute(NULL)
#1 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Database/Connection.php(314): Cake\Database\Statement\MysqlStatement->execute()
#2 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Database/Query.php(213): Cake\Database\Connection->run(Object(Cake\ORM\Query))
#3 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/ORM/Query.php(1037): Cake\Database\Query->execute()
#4 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(287): Cake\ORM\Query->_execute()
#5 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/ORM/Query.php(986): Cake\ORM\Query->_all()
#6 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(415): Cake\ORM\Query->all()
#7 /home/vamola/web/vamola.net/public_html/src/Shell/BackupShell.php(55): Cake\ORM\Query->first()
#8 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Console/Shell.php(488): App\Shell\BackupShell->CriarBackup()
#9 /home/vamola/web/vamola.net/public_html/vendor/cakephp/cakephp/src/Console/CommandRunner.php(140): Cake\Console\Shell->runCommand(Array, true)
#10 /home/vamola/web/vamola.net/public_html/bin/cake.php(12): Cake\Console\CommandRunner->run(Array)
#11 {main}

Do you guys have any idea how can I get it work? It doesn’t make sense mysql goes away, because the connection should be closed… (my param in app.config for persistent is false…)

My code is very simple, just like this:

** GetAll() is like find()
** UpdateAll() is like updateAll()

$busGoogle = new \App\Business\Google();
$busBackup = \App\Util\Comum::Business(‘Basicos’, ‘Backup’);
$lstSites = $busBackup->GetAll([‘fl_ativo’=>‘S’])->toArray();

	foreach ($lstSites as $item) {
		try {
			$bkpPath    = sys_get_temp_dir() . DS;
			$timestamp  = Date("YmdHis");
			$bkpFolder  = $item->ds_site ."-Content-". $timestamp;
			$bkpSQL     = $item->ds_site ."-SQL-". $timestamp;
			
			/* $busBackup->UpdateAll([
				'fl_processando'=>'S', 
				'dt_ini_backup'=>Date("Y-m-d H:i:s"), 
				'dt_fim_backup'=>null
			], [
				'ds_site'=>$item->ds_site
			]); */
			
			$senhaBanco = "";
			if (!empty($item->ds_senha_banco)) {
				$senhaBanco = \App\Util\Comum::Decrypt($item->ds_senha_banco);
			}
			
			// executando backups
			exec("mysqldump -h " .$item->ds_servidor_banco. " -P " .$item->ds_porta_banco. " -u " .$item->ds_usuario_banco .(!empty($senhaBanco) ? (" -p" .$senhaBanco) : ''). " --default-character-set=utf8 -N " .$item->ds_nome_banco. " >" . $bkpPath . $bkpSQL. ".sql");
			$files = [
				\App\Util\Comum::CreateTarGz($bkpPath . $bkpFolder, $item->ds_path),
				\App\Util\Comum::CreateTarGz($bkpPath . $bkpSQL, $bkpPath . $bkpSQL. ".sql")
			];
			
			// apagando backups deste site na Cloud
			array_map(function ($file) use ($busGoogle) {
				$busGoogle->DeleteFile($file->getId());
			}, $busGoogle->FindCustom("name contains '" .$item->ds_site. "-'"));

			// enviando para a Cloud
			$busGoogle->WriteFiles($files, true);

			sleep(1);
                           // the error goes when I run this one...
			/*$busBackup->UpdateAll([
				'fl_processando'=>'N', 
				'dt_fim_backup'=>Date("Y-m-d H:i:s")
			], [
				'ds_site'=>$item->ds_site
			]); */

			// removendo sql.
			@unlink($bkpPath . $bkpSQL. ".sql");
		} catch (\Exception $e) {
			@mail( "sac@vamola.net", "Fim de Processo - NOK - {$item->ds_site}", "Processo NOK ".$e->getTraceAsString());
		}