Skip to content

Multiplexing

Jump to bottom
Nick Vyzas edited this page Oct 18, 2018 · 12 revisions

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:

INSERT INTO mysql_query_rules (active,match_digest,multiplex) VALUES
('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:

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

... and maybe even cache it:

INSERT INTO mysql_query_rules (active,match_digest,multiplex,cache_ttl) VALUES
('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:

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

 

Clone this wiki locally