Multiplexing

Nick Vyzas edited this page Oct 18, 2018 · 4 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
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.