Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

ProxySQL possibly breaks backup solutions like JetBackup

17 views
Skip to first unread message

GT

unread,
Mar 5, 2025, 5:59:11 AMMar 5
to proxysql
Not sure if this is a ProxySQL issue, a JetBackup issue, a mysqldump or a mix of all of them.
When trying to do a DB backup with JetBackup through ProxySQL the job fails.
This is always reproducible when backing up with JetBackup connecting to ProxySQL and has no problems when running directly on the DB server IPs.
The problem is that when using such solutions with a control panel (ie. Plesk, WHM, etc) you can't set the direct IP of the DB server, because it's taken from the user accounts DBs which points to the ProxySQL binding.

In JetBackup logs I have the following errors, repeated for each DB to backup:
```
mysqldump: Couldn't execute 'SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'XXX'': Server has gone away (2006)
mysqldump: Couldn't execute 'SET SQL_QUOTE_SHOW_CREATE=1': Server has gone away (2006)
mysqldump: Couldn't execute 'SELECT `COLUMN_NAME` AS `Field`, `COLUMN_TYPE` AS `Type`, `IS_NULLABLE` AS `Null`, `COLUMN_KEY` AS `Key`, `COLUMN_DEFAULT` AS `Default`, `EXTRA` AS `Extra`, `COLUMN_COMMENT` AS `Comment` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = 'YYY' AND TABLE_NAME = 'XXX' ORDER BY ORDINAL_POSITION': Server has gone away (2006)
```

In proxysql.log lots of (again for each DB):
```
MySQL_Session.cpp:8392:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client localhost:0. Setting lock_hostgroup. Please report a bug for future enhancements:SET session_track_schema=1,session_track_system_variables='autocommit,tx_isolation'
MySQL_Session.cpp:8392:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client localhost:0. Setting lock_hostgroup. Please report a bug for future enhancements:SET @i = -1
MySQL_Session.cpp:8392:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client localhost:0. Setting lock_hostgroup. Please report a bug for future enhancements:/*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */
mysql_data_stream.cpp:530:check_data_flow(): [ERROR] Session=0x7f5bdc379980, DataStream=0x7f5be90f3100 -- Data at both ends of a MySQL data stream: IN <1 bytes 0 packets> , OUT <442 bytes 32768 packets>
```

Both ProxySQL and MariaDB servers have the same max_allowed_packet, which is enough to run the backup job directly on the MariaDB server.
About the MAX_STATEMENT_TIME I already read that ProxySQL doesn't support commented statements, but that shouldn't be a blocking issue.
I also tried to set different mysql_versions in ProxySQL without luck.

- ProxySQL version: Tested on different versions 2.6.x up to 2.7.2
- OS: Debian 12
- DB Server: MariaDB 10.11.6

Reply all
Reply to author
Forward
0 new messages