PHP Warning: "MySQL Server is gone" after a MySQL dump in macOS terminal

Question

I use the PHP script from the CLI to load remote MySQL databases (gzip) and extract them directly to the MacBook MySQL 5.7 server (not MAMP). It works fine, but as a side effect, my PHP applications (MAMP Pro) lose the MySQL connection in the middle of the CLI script with a warning

PHP Warning: mysqli::__construct(): MySQL server has gone away in ... 

This happens after a crossroads around dozens of databases (cannot reproduce the exact amount). While the CLI script is being executed, the MySQL panel in System Preferences goes from green / works to red / stopped to work green / start again after each CLI dump that does not conflict with PHP applications in the browser at first. But at some point, PHP applications obviously lose their connection.

I played with my.cnf and installed

 [mysqld] max_allowed_packet=128M max_connections=1024 

or other amounts, but that doesn’t change anything.

When I manually stop and start MySQL in System Preferences after completing the CLI script, PHP continues to work fine again.

Any ideas?

EDIT:

Thanks, but this has not yet been fixed. So the main idea of ​​the script:

 $tmpPath = '/tmp/' For each of 30 databases with different size: $dbName = database name exec('ssh [e-mail] "mysqldump --login-path=local --lock-tables=false '.$dbName.' | gzip" > '.$tmpPath.$dbName.'.sql.gz'); exec('gzip -q -dc '.$tmpPath.$dbName.'.sql.gz | mysql -u root -proot '.$dbName) 

As I said, the PHP CLI script in the terminal does not complain at all! Its my PHP applications (backend for third-party Angular applications), which somewhere in the middle of 30 dumps stop working due to the mentioned error.

Answer 1

I have a similar problem and fixed it simply by adding max_connections = 1024 in mysql.conf

Answer 2

We had a similar problem and we used this that reconnects if the connection goes away:

 <?php use Exception; use PDO; use Our\Package\Common\Config\DbCredentials; class DbConnectionManager { private $credentials; /** @var PDO $connection */ private $connection; /** * DbConnectionManager constructor. * @param DbCredentials $credentials */ public function __construct(DbCredentials $credentials) { $this->credentials = $credentials; $this->reconnect(); } /** * @return PDO */ public function getConnection() { if (!$this->isConnected()) { $this->reconnect(); } return $this->connection; } /** * @return bool */ private function isConnected() { try { return $this->connection->query('SELECT 1 + 1;'); } catch (Exception $e) { return false; } } /** * Reinitialise the connection to MySQL */ private function reconnect() { $dsn = $this->credentials->getDriver() .':host='.$this->credentials->getHost() .';port='.$this->credentials->getPort() .';dbname='.$this->credentials->getDbName() ; $connection = new PDO($dsn, $this->credentials->getUser(), $this->credentials->getPassword(), array( PDO::ATTR_TIMEOUT => 28800, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, )); $this->connection = $connection; } } 

You can customize this. The DBCredentials class is just a simple PHP class with getters and setters for the driver, host name, user name, password, db name, you can change it to just accept string arguments if you want.

Answer 3

You can also get these errors if you send a request to a server that is incorrect or too large. If mysqld gets the package too big . or out of order, he assumes that something went wrong with the client and closes the connection.

To avoid this problem, you must ensure that max_allowed_packet is installed more on the mysqld server than on the client, and that all clients use the same value for max_allowed_packet. And do not forget to restart your server after that !!!!

Answer 4

When you create mysqldump, you lock the tables. I think this is the main problem and therefore your applications get this error. Use the following options:

 --lock-tables, -l Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all. 

Original answer here:

Run MySQLDump without locking tables

And try the user "sleeping" between dump commands.