Server has gone away error when set multiple mysql servers and max_replication_lag
TokuiNico opened this issue · 14 comments
Docker Image
- ProxySQL version 2.6.0
- OS version Debian GNU/Linux 11 (bullseye)
I've set up a hostgroup with two servers, db_primary and db_replica, to manage read-only connections. The db_replica is configured with a max_replication_lag of 1800 seconds. Ideally, most read-only traffic should route to db_replica unless its replication lag exceeds 1800 seconds, at which point connections should switch to db_primary.
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | db_primary | 3306 | 0 | ONLINE | 1 | 0 | 5000 | 0 | 0 | 0 | primady db for readonly user |
| 1 | db_replica | 3306 | 0 | ONLINE | 100000 | 0 | 5000 | 1800 | 0 | 0 | replica db for readonly user |
However, while the expectation is that new read-only queries will redirect to db_primary without impacting active connections when db_replica's lag surpasses 1800 seconds, the actual switch disrupts existing queries, resulting in a "Server has gone away" error and preventing a smooth transition to db_primary.
Hi @TokuiNico,
could you please share the full error log? As the issue template mentions, it's a requisite for potential bug reports. Since the error is "Server has gone away", that might be an indication that MySQL itself terminated the connections. If you could share also MySQL error log during the time of this errors it will also help to troubleshoot the issue.
Thank you, regards. Javier.
Hi, @JavierJF
I've checked the MySQL error log and didn't find anything relevant. Could this issue be related to using Peewee's connection pool and opening transactions during queries?
Also, another question: with ProxySQL in use, is there still a need for a client-side connection pool?
Hi @TokuiNico,
I've checked the MySQL error log and didn't find anything relevant. Could this issue be related to using Peewee's connection pool and opening transactions during queries?
Without the requested information, for now, the full ProxySQL error log, and at least the sections of the MySQL error log at the time of the incidents (would be convenient to have increased MySQL verbosity first, via SET GLOBAL log_error_verbosity=3;), it's early for making hypothesis.
Also, another question: with ProxySQL in use, is there still a need for a client-side connection pool?
That depends on your use case, ProxySQL helps with fast connections creation in the frontend, since those connections are much more lightweight that MySQL ones. Maybe this combined with an already warmed up connection pool is sufficient for your use case, determining if it's is something that requires measurements.
Thanks, Javier.
I'll set log_error_verbosity and collect error log if similar issue happened.
Thanks.
I can confirm that this happens for us too. proxysql 2.5.5.
mysql> show mysql servers;
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 10.37.1.40 | 3306 | 0 | ONLINE | 1000 | 0 | 300 | 0 | 0 | 0 | |
| 1 | 10.37.1.40 | 3306 | 0 | ONLINE | 1 | 0 | 300 | 0 | 0 | 0 | |
| 1 | 10.37.1.41 | 3306 | 0 | ONLINE | 1000 | 0 | 300 | 1 | 0 | 0 | |
| 2 | 10.37.1.41 | 3306 | 0 | ONLINE | 1000 | 0 | 300 | 0 | 0 | 0 | |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.01 sec)
We already have verbosity set to 3 on percona server and this happened two days in a row recently:
2024-05-12T05:03:24.618050Z 31972 [Note] Aborted connection 31972 to db: 'p2m_lcl' user: 'web_P2M' host: '10.37.1.50' (Got an error writing communication packets)
2024-05-13T05:04:25.611231Z 62680 [Note] Aborted connection 62680 to db: 'p2m_lcl' user: 'web_P2M' host: '10.37.1.50' (Got an error writing communication packets)
Proxysql log entries:
2024-05-12 01:03:24 MySQL_HostGroups_Manager.cpp:3490:replication_lag_action_inner(): [WARNING] Shunning server 10.37.1.41:3306 from HG 1 with replication lag of 3 second, count number: '1'
2024-05-12 01:03:24 MySQL_Session.cpp:4225:handler_ProcessingQueryError_CheckBackendConnectionStatus(): [ERROR] Detected a lagging server during query: 10.37.1.41, 3306
.....
2024-05-13 01:04:25 MySQL_HostGroups_Manager.cpp:3490:replication_lag_action_inner(): [WARNING] Shunning server 10.37.1.41:3306 from HG 1 with replication lag of 3 second, count number: '1'
2024-05-13 01:04:25 MySQL_Session.cpp:4225:handler_ProcessingQueryError_CheckBackendConnectionStatus(): [ERROR] Detected a lagging server during query: 10.37.1.41, 3306
app log entries:
[2024-05-12 01:03:24] [SUBPROGRAM-4] [symfony] [crit] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
[2024-05-12 01:03:24] [SUBPROGRAM-4] [symfony] [err] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
[2024-05-12 01:03:24] [SUBPROGRAM-4] [symfony] [crit] #0 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/Statement.php(253): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Statement))
#1 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php(1036): Doctrine_Connection_Statement->execute(Array)
#2 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php(992): Doctrine_Connection->execute('SELECT p.id AS ...', Array)
#3 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php(1036): Doctrine_Query_Abstract->_execute(Array)
#4 /var/www/imsapps/p2m/lib/model/doctrine/OrgnodeTable.class.php(940): Doctrine_Query_Abstract->execute(Array, 3)
#5 /var/www/imsapps/p2m/lib/model/doctrine/TosTable.class.php(44): OrgnodeTable->getAncestorIds(NULL)
#6 /var/www/imsapps/p2m/lib/model/doctrine/SystemMail.class.php(454): TosTable->findByOrgnodeOrDefault(Object(Member))
#7 /var/www/imsapps/p2m/lib/model/doctrine/SystemMail.class.php(495): SystemMail::disableCommunicationByTos(Array)
#8 /var/www/imsapps/p2m/plugins/cgMailerPlugin/lib/cgMailer.class.php(65): SystemMail::disableCommunication(Array, 'processingError')
#9 /var/www/imsapps/p2m/plugins/cgImporterPlugin/lib/cgImporter.class.php(488): cgMailer::addToQueueByApp('admin', 'ai_email', 'processingError', Array)
#10 /var/www/imsapps/p2m/lib/model/doctrine/HierarchyImportQueue.class.php(109): cgImporter->sendErrorEmail('/var/ims/data/c...', 'Importer Crashe...', '/var/ims/data/c...')
#11 /var/www/imsapps/p2m/lib/model/doctrine/HierarchyImportQueue.class.php(35): HierarchyImportQueue->import(Object(SubProgram))
#12 /var/www/imsapps/p2m/batch/rabbit/file/processFileProcessingQueue.php(89): HierarchyImportQueue->execute()
#13 /var/www/imsapps/p2m/batch/rabbit/file/processFileProcessingQueue.php(128): executeFileProcessingQueue(Array)
#14 {main}
[2024-05-12 01:03:24] [SUBPROGRAM-4] [symfony] [crit] [HIERARCHY IMPORT ID:18756 4] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
.....
[2024-05-13 01:04:30] [SUBPROGRAM-4] [symfony] [crit] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
[2024-05-13 01:04:30] [SUBPROGRAM-4] [symfony] [err] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
[2024-05-13 01:04:30] [SUBPROGRAM-4] [symfony] [crit] #0 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/Statement.php(253): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Statement))
#1 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php(1036): Doctrine_Connection_Statement->execute(Array)
#2 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php(992): Doctrine_Connection->execute('SELECT p.id AS ...', Array)
#3 /var/www/imsapps/p2m/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php(1036): Doctrine_Query_Abstract->_execute(Array)
#4 /var/www/imsapps/p2m/lib/model/doctrine/OrgnodeTable.class.php(940): Doctrine_Query_Abstract->execute(Array, 3)
#5 /var/www/imsapps/p2m/lib/model/doctrine/TosTable.class.php(44): OrgnodeTable->getAncestorIds(NULL)
#6 /var/www/imsapps/p2m/lib/model/doctrine/SystemMail.class.php(454): TosTable->findByOrgnodeOrDefault(Object(Member))
#7 /var/www/imsapps/p2m/lib/model/doctrine/SystemMail.class.php(495): SystemMail::disableCommunicationByTos(Array)
#8 /var/www/imsapps/p2m/plugins/cgMailerPlugin/lib/cgMailer.class.php(65): SystemMail::disableCommunication(Array, 'processingError')
#9 /var/www/imsapps/p2m/plugins/cgImporterPlugin/lib/cgImporter.class.php(488): cgMailer::addToQueueByApp('admin', 'ai_email', 'processingError', Array)
#10 /var/www/imsapps/p2m/lib/model/doctrine/HierarchyImportQueue.class.php(109): cgImporter->sendErrorEmail('/var/ims/data/c...', 'Importer Crashe...', '/var/ims/data/c...')
#11 /var/www/imsapps/p2m/lib/model/doctrine/HierarchyImportQueue.class.php(35): HierarchyImportQueue->import(Object(SubProgram))
#12 /var/www/imsapps/p2m/batch/rabbit/file/processFileProcessingQueue.php(89): HierarchyImportQueue->execute()
#13 /var/www/imsapps/p2m/batch/rabbit/file/processFileProcessingQueue.php(128): executeFileProcessingQueue(Array)
#14 {main}
[2024-05-13 01:04:30] [SUBPROGRAM-4] [symfony] [crit] [HIERARCHY IMPORT ID:18764 4] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away