Bug #71477 transaction_allow_batching is available in non-Cluster MySQL Server 5.6

• Submitted: 25 Jan 2014 12:10
• Modified: 27 Jan 2014 15:11
• Reporter: Valeriy Kravchuk
• Status: Verified
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.6.15
• OS: Any
• CPU Architecture: Any
• Tags: regression, transaction_allow_batching

[25 Jan 2014 12:10] Valeriy Kravchuk

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-system-variables.html#sysvar_transact...) says:

"This variable is currently supported for MySQL Cluster only."

But I see it and can change values with non-Cluster MySQL server 5.6.15 on Windows, for example:

mysql> show variables like 'transaction%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| transaction_alloc_block_size | 8192  |
| transaction_allow_batching   | OFF   |
| transaction_prealloc_size    | 4096  |
+------------------------------+-------+
3 rows in set (0.00 sec)

mysql> set session transaction_allow_batching='ON';
Query OK, 0 rows affected (0.08 sec)

mysql> show variables like 'transaction%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| transaction_alloc_block_size | 8192  |
| transaction_allow_batching   | ON    |
| transaction_prealloc_size    | 4096  |
+------------------------------+-------+
3 rows in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.15                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.15-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

It is just silently changed, no warnings or anything about improper use or no effect.

Manual for 5.5 has the same text, but in MySQL 5.5 this session variable does not exist in non-Cluster binaries:

mysql> show variables like 'transaction%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| transaction_alloc_block_size | 8192  |
| transaction_prealloc_size    | 4096  |
+------------------------------+-------+
2 rows in set (0.06 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.35                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.35                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.06 sec)

So, this can be considered a kind of "regression" and waste of memory for a variable that should not have any effect.

How to repeat:
With normal, non-Cluster binaries, try the following:

show variables like 'transaction_allow%';
set session transaction_allow_batching='ON';
show variables like 'transaction_allow%';
show variables like '%version%';

Suggested fix:
Do not add this session variable for builds without NDB Cluster support. 

Alternatively, give some reasons in the manual on why this is changed in 5.6 and what this change is intended for.

[27 Jan 2014 10:39] MySQL Verification Team

Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[27 Jan 2014 15:11] Valeriy Kravchuk

While we are on this topic note that there is yet another server variables, server_id_bits, that is visible in 5.6.15 non-Cluster binaries:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.06 sec)

mysql> show variables like 'server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

while manual (http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-system-variables.html#sysvar_server_i...) says:

"This system variable is supported only by MySQL Cluster. server_id_bits is not supported by the standard MySQL Server."

I assume the reason for it to be there is the same as for transaction_allow_batching, so they should be fixed or explained together. Correct me if I am wrong and this should be reported separately.