MySQL Bugs: #34876: Invalid Default value is assigned to a number of variables

Bug #34876	Invalid Default value is assigned to a number of variables
Submitted:	27 Feb 2008 10:39	Modified:	23 Apr 2008 20:38
Reporter:	Salman Rawala	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	default value

Description:
According to documentation the default value of the variable is 33554432, but when we compare it with DEFAULT, FALSE returns in result, which shows that the value mentioned is invalid. This occurs for the following variables:

tmp_table_size, key_buffer_size, max_relay_log_size, myisam_max_sort_file_size, transaction_prealloc_size, sql_mode, delay_key_write, log_output, flush, max_binlog_cache_size, max_binlog_size, max_connect_errors, max_connections, max_insert_delayed_threads, max_join_size, range_alloc_block_size, read_rnd_buffer_size, sort_buffer_size, sync_binlog, log_bin_trust_function_creators

How to repeat:
Sample Code:
SET @@global.tmp_table_size = DEFAULT;
SELECT @@global.tmp_table_size = 33554432;

Actual Output:
SET @@global.tmp_table_size = DEFAULT;
SELECT @@global.tmp_table_size = 33554432;
@@global.tmp_table_size = 33554432
0

Suggested fix:
Update documentation

Check default values from following location

http://dev.mysql.com/doc/refman/5.1/en/dynamic-system-variables.html

I believe default depends on OS used. What OS are you working in?

I am using Windows XP

Thank you for the bug report. Verified on Linux.

[miguel@mira dbs]$ 5.1/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "tmp_table_size";
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 | 
+----------------+----------+
1 row in set (0.01 sec)

mysql> SET @@global.tmp_table_size = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "tmp_table_size";
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 | 
+----------------+----------+
1 row in set (0.01 sec)

mysql>

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Corrections made:

tmp_table_size: system dependent, I have marked it that way
key_buffer_size: default is 8MB
max_relay_log_size: default is 0
log_output: default is FILE
max_insert_delayed_threads: default is 20

For the others, I cannot repeat the assertion in the bug report
that setting them to DEFAULT results in an incorrect value.