Conditions where multiplexing is disabled

  • active transaction
    When a transaction is active in a connection, multiplexing is disabled until the transaction commits or rollbacks.

  • table(s) locked
    If LOCK TABLE, LOCK TABLES or FLUSH TABLES WITH READ LOCK is executed, multiplexing is disabled until UNLOCK TABLES is executed.

  • use of GET_LOCK()
    If GET_LOCK() is executed, multiplexing is disabled and is never enabled again.

  • use of specific session/user variables
    All queries that have @ in their query_digest will disable multiplexing, and it will never be enabled again.

NOTE: If you are selecting a variable (e.g. select @test_var) and you are not getting results as you expected, its most probably due to a query rule which is routing your statement to a different hostgroup (review your query rules for this).

Similarly, the same happens if these commands are executed:

  • SET SQL_SAFE_UPDATES=?,SQL_SELECT_LIMIT=?,MAX_JOIN_SIZE=? (mysql —safe-updates)
  • SET FOREIGN_KEY_CHECKS
  • SET UNIQUE_CHECKS
  • SET AUTO_INCREMENT_INCREMENT (v 1.4.4+)
  • SET AUTO_INCREMENT_OFFSET (v 1.4.4+)

  • SET GROUP_CONCAT_MAX_LEN (v 1.4.4+)
    There are 2 exceptions hardcoded that do not disable multiplexing:

  • SELECT @@tx_isolation

  • SELECT @@version
    These exceptions are hardcooded because many applications run these in every connection.

  • use of SQL_CALC_FOUND_ROWS
    If a query contains SQL_CALC_FOUND_ROWS, multiplexing is disabled and is never enabled again on the connection.

  • Temporary tables
    If CREATE TEMPORARY TABLE is executed, multiplexing is disabled and is never enabled again on the connection.

  • use of PREPARE
    If PREPARE is executed (creation of prepared statements using the TEXT protocol and not the BINARY protocol), multiplexing is disabled and is never enabled again.

  • SQL_LOG_BIN sets to 0
    If SQL_LOG_BIN is set to 0, multiplexing is disabled until SQL_LOG_BIN is set back to 1.

Not handled session variables

Most of the session variables are not handled automatically.For example, if a client issues SET TX_ISOLATION=? , multiplexing is NOT disabled.This is a bug if you have clients that use different tx_isolation, while it is a feature if all the clients specify the same tx_isolation that is also the default (this is the common case, in my experience).

Ad-hoc enable/disable of multiplexing

mysql_query_rules.multiplexing allows to enable or disable multiplexing based on matching criteria.The field currently accepts these values:

  • 0 : disable multiplex
  • 1 : enable multiplex
  • 2 : do not disable multiplex for this specific query containing @
    For example, in your application is using SET SQL_SELECT_LIMIT , you can create these 2 rules:
  1. INSERT INTO mysql_query_rules (active,match_digest,multiplex) VALUES
  2. ('1','^SET SQL_SELECT_LIMIT=?',0), (1,'^SET SQL_SELECT_LIMIT=DEFAULT,1);

If your application is sending something like SELECT @@max_allowed_packet and you want to prevent multiplexing to be disabled because of this, you can create the following rule:

  1. INSERT INTO mysql_query_rules (active,match_digest,multiplex) VALUES
  2. ('1','^SELECT @@max_allowed_packet',2);

… and maybe even cache it:

  1. INSERT INTO mysql_query_rules (active,match_digest,multiplex,cache_ttl) VALUES
  2. ('1','^SELECT @@max_allowed_packet',2,60000);

If your application is trying to set a variable that will lead to disable multiplexing, and you think that it can be filtered, you can create a filter that returns OK without executing the query:

  1. INSERT INTO mysql_query_rules (active,match_digest,OK_msg) VALUES
  2. ('1','^SET @@wait_timeout = ?','');

原文: https://github.com/sysown/proxysql/wiki/Multiplexing