Override query hostgroup

Polyglot Projects ProxySQL
1

I have an application that runs perfectly well on ProxySQL except in a few specific circumstances. I route all SELECT queries to the read hostgroup and that works very well.

The specific circumstances is when the application performs some internal clean-up and then it sets the connection mode to: SET sql_mode=‘STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’,time_zone = ‘+00:00’,lc_messages = ‘en_US’

all queries from this connection should run in the write hostgroup, even the select queries, thus overriding my rule. Is there a way to do that ?

Right now this application mode errors out on a select query with: SQLSTATE[Y0000]: <>: 9006 ProxySQL Error: connection is locked to hostgroup 0 but trying to reach hostgroup 2 .

I’d rather not set mysql-set_query_lock_on_hostgroup to 0 since that introduces more issues.

This is on version 2.6.3

Any ideas ? Thank you

2

If you start an explicit transaction before executing this SET statement, then all queries inside the transaction will be routed to the writer, including SELECTs. ProxySQL does not break up transactions.

3

Understood. Thank you. Is there no session locking to a certain hostgroup once the session has been restricted this way ? It would be equivalent to having a transaction I guess

mysql> SET SESSION sql_mode = ‘STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.id, u.displayName, u.recoveryHash, u.recoverySendAt, u.theme, u.files_folder_id, u.last_password_change, u.id AS u.id, p.password, p.userId AS p.userId
→ FROM core_user u
→ LEFT JOIN core_auth_password p ON
→ u.id = p.userId
→ WHERE
→ u.id = ‘1’
→ LIMIT 0,1;
ERROR 9006 (Y0000): ProxySQL Error: connection is locked to hostgroup 0 but trying to reach hostgroup 2

4

Yes, there is session locking, which is what you are experiencing. SET statements tell ProxySQL to go to writer, but you have a rule that says ‘SELECTs go here’ and it won’t do that. You should explicitly START TRANSACTION; SET SESSION ...; SELECT ..; COMMIT and that should all be routed to the same connection on the writer.

5

sorry, what I meant was to make the entire connection go to the writer hostgroup once the set command has been issued. Or you think that would break a lot of the intended usage of proxysql ?

6

Yes, you can do that by forcing a transaction as I showed above.

If you simply do SET ..; SELECT.. you will get the error you are experiencing because ProxySQL disabled multiplexing on ‘SET’ commands.

See the section on ’ Tuning multiplexing’ and you can create a rule that forces multiplexing even when SET sql_mode is detected.

7

Thank you. It’s all good now. Much appreciated Matthew