MySQL Bugs: #31177: Server variables can't be set to their current values

Bug #31177	Server variables can't be set to their current values
Submitted:	24 Sep 2007 20:43	Modified:	14 Jan 21:36
Reporter:	Baron "Xaprb" Schwartz (Basic Quality Contributor)
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.38, 4.1, 5.0, 5.1, 5.2 BK	OS:	Linux (Ubuntu)
Assigned to:	Tatjana A. Nuernberg	Target Version:
Tags:	qc
Triage:	D2 (Serious)

Description:
While writing the script for bug #31138, I noticed that the script has some undesirable
side effects.  It checks for variable set-ability by doing

SET @@var_name := @@var_name

This should theoretically leave the setting unchanged, but it does not.  I enhanced the
script to detect the cases where the variables end up with a different value.

This does have some impact on the server, too.  For example, after running the script, I
can't load the Sakila database:

baron@tigger sakila-db $ mysql < sakila-schema.sql 
baron@tigger sakila-db $ mysql < sakila-data.sql 
ERROR 1197 (HY000) at line 34: Multi-statement transaction required more than
'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

How to repeat:
Apply the following patch to the test script attached to bug #31138:

--- test-option-tables.pl.old   2007-09-24 14:38:43.000000000 -0400
+++ test-option-tables.pl       2007-09-24 14:45:00.000000000 -0400
@@ -183,16 +183,26 @@
    my ($is_global_var, $is_session_var, $can_global_set, $can_session_set);
    if ( $var eq $option || !exists $options{$var} ) {
       eval {
-         $dbh->do('SELECT @@global.' . $var);
+         $dbh->do('SET @saved := @@global.' . $var);
          $is_global_var = 1;
          $dbh->do('SET @@global.' . $var . ' := @@global.' . $var);
          $can_global_set = 1;
+         my ( $pre, $post, $same ) = $dbh->selectrow_array(
+            'SELECT @saved, @@global.' . $var . ', @saved = @@global.' .  $var);
+         if ( !$same ) {
+            print "System variable $var can't be global set properly: was '$pre', now
'$post'\n";
+         }
       };
       eval {
-         $dbh->do('SELECT @@session.' . $var);
+         $dbh->do('SET @saved := @@session.' . $var);
          $is_session_var = 1;
          $dbh->do('SET @@session.' . $var . ' := @@session.' . $var);
          $can_session_set = 1;
+         my ( $pre, $post, $same ) = $dbh->selectrow_array(
+            'SELECT @saved, @@session.' . $var . ', @saved = @@session.' .  $var);
+         if ( !$same ) {
+            print "System variable $var can't be session set properly: was '$pre', now
'$post'\n";
+         }
       };
    }

Now when I run the script and grep the results for 'properly', I get the following:

System variable max_binlog_cache_size can't be global set properly: was
'18446744073709551615', now '4096'
System variable max_seeks_for_key can't be global set properly: was
'18446744073709551615', now '18446744073709551615'
System variable max_seeks_for_key can't be session set properly: was
'18446744073709551615', now '18446744073709551615'
System variable max_write_lock_count can't be global set properly: was
'18446744073709551615', now '1'
System variable myisam_max_sort_file_size can't be global set properly: was
'9223372036854775807', now '9223372036853727232'
System variable ndb_autoincrement_prefetch_sz can't be global set properly: was '32', now
'1'
System variable ndb_autoincrement_prefetch_sz can't be session set properly: was '32', now
'1'
System variable range_alloc_block_size can't be global set properly: was '2048', now
'4096'
System variable range_alloc_block_size can't be session set properly: was '2048', now
'4096'

Seems not to be reproducable on Windows and 5.0.45

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

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

mysql> select @@max_write_lock_count, @@max_binlog_cache_size, @@max_seeks_for_k
ey, @@myisam_max_sort_file_size;
+------------------------+-------------------------+---------------------+------
-----------------------+
| @@max_write_lock_count | @@max_binlog_cache_size | @@max_seeks_for_key | @@myi
sam_max_sort_file_size |
+------------------------+-------------------------+---------------------+------
-----------------------+
|             4294967295 |              4294967295 |          4294967295 |
          107374182400 |
+------------------------+-------------------------+---------------------+------
-----------------------+
1 row in set (0.00 sec)

mysql> set @@max_write_lock_count := @@max_write_lock_count;
ERROR 1229 (HY000): Variable 'max_write_lock_count' is a GLOBAL variable and sho
uld be set with SET GLOBAL
mysql> set @@max_binlog_cache_size := @@max_binlog_cache_size;
ERROR 1229 (HY000): Variable 'max_binlog_cache_size' is a GLOBAL variable and sh
ould be set with SET GLOBAL
mysql> set @@max_seeks_for_key := @@max_seeks_for_key;
Query OK, 0 rows affected (0.01 sec)

mysql> set @@myisam_max_sort_file_size := @@myisam_max_sort_file_size;
ERROR 1229 (HY000): Variable 'myisam_max_sort_file_size' is a GLOBAL variable an
d should be set with SET GLOBAL
mysql> select @@max_write_lock_count, @@max_binlog_cache_size, @@max_seeks_for_k
ey, @@myisam_max_sort_file_size;
+------------------------+-------------------------+---------------------+------
-----------------------+
| @@max_write_lock_count | @@max_binlog_cache_size | @@max_seeks_for_key | @@myi
sam_max_sort_file_size |
+------------------------+-------------------------+---------------------+------
-----------------------+
|             4294967295 |              4294967295 |          4294967295 |
          107374182400 |
+------------------------+-------------------------+---------------------+------
-----------------------+
1 row in set (0.00 sec)

mysql>

but 

set @@max_seeks_for_key := @@max_seeks_for_key;

does not raise any error, neither does

set @@max_seeks_for_key := (2 * @@max_seeks_for_key);

.. but it has no effect!

mysql> select @@max_seeks_for_key;
+---------------------+
| @@max_seeks_for_key |
+---------------------+
|          4294967295 |
+---------------------+
1 row in set (0.00 sec)

mysql> set @@max_seeks_for_key := (2 * @@max_seeks_for_key);
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_seeks_for_key;
+---------------------+
| @@max_seeks_for_key |
+---------------------+
|          4294967295 |
+---------------------+
1 row in set (0.00 sec)

mysql>mysql> show global variables like '%max_seeks%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| max_seeks_for_key | 4294967295 |
+-------------------+------------+
1 row in set (0.00 sec)

mysql> show local variables like '%max_seeks%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| max_seeks_for_key | 4294967295 |
+-------------------+------------+
1 row in set (0.00 sec)

mysql>

There's a small difference in what you showed as a repro case.  The script will actually
be running

SET @@global.var_name := @@global.var_name;

and

SET @@session.var_name := @@session.var_name;

So the errors about needing to SET GLOBAL will go away.  (I'm not sure if this will allow
the bug to be reproduced anyway, though).

It wouldn't be hard to extend the script to check for variables that pretend to be
settable, but setting them has no effect.  Would you like me to do that?

I don't want anything particular .. I only became interested! (and FYI am not a MYSQL
person)

You are right about the error message.  No action is taken when an error is returned!

But in this

show global variables like 'max_write_lock_count';
show local variables like 'max_write_lock_count';
set @@global.max_write_lock_count := @@global.max_write_lock_count;
show global variables like 'max_write_lock_count';
show local variables like 'max_write_lock_count';

all returned results are '4294967295'.  I think this documents that for this variable
there is no such issue for this single variable on Win and 5.0.45!?

But the missing error message in my second post I think musst be a bug!

Thank you for the report.

Verified as described.

Problem is not setting variable in set @@globl.xxx=@@global.xxx; manner, but large integer
values;

Test case:

select @@global.max_binlog_cache_size;
set @@global.max_binlog_cache_size = @@global.max_binlog_cache_size;
select @@global.max_binlog_cache_size;
et @@global.max_binlog_cache_size = 4294967295;
select @@global.max_binlog_cache_size;

A couple of them are not large values, and are also not being set right.

@sveta

isn't what I found on Windows/MySQL 5.0.45 a bug too?
(my 2nd post here)

Peter,

thank you for the feedback. This seems to be buggy too, but seems to be same bug as this
one.

Bad data, changing to P2.

Object lesson for the day: if you get random test failures, but can't for the life of you
see anything wrong with your code, well ... it might be that the test program's broken! :
)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38449

ChangeSet@1.2557, 2007-11-25 19:05:07+01:00, tnurnberg@mysql.com +7 -0
  Bug#31177: Server variables can't be set to their current values
  
  Default values of variables were not subject to upper/lower bounds
  and step, while setting variables was. Bounds and step are also
  applied to defaults now; defaults are corrected quietly, values
  given by the user are corrected, and a correction-warning is printed
  to the log as needed. Lastly, very large values could wrap around,
  starting from 0 again. They are bounded at the maximum value for the
  respective data-type now if no lower maximum is specific in the
  variable's definition.

And now for my favourite, an alternative extended version that
actually throws out-of-bounds warnings client-wards.  (No
warning is thrown if value is simply not a int-multiple of
"step".)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38925

ChangeSet@1.2557, 2007-11-30 06:32:04+01:00, tnurnberg@mysql.com +21 -0
  Bug#31177: Server variables can't be set to their current values
  
  Default values of variables were not subject to upper/lower bounds
  and step, while setting variables was. Bounds and step are also
  applied to defaults now; defaults are corrected quietly, values
  given by the user are corrected, and a correction-warning is thrown
  as needed. Lastly, very large values could wrap around, starting
  from 0 again. They are bounded at the maximum value for the
  respective data-type now if no lower maximum is specified in the
  variable's definition.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39060

ChangeSet@1.2616, 2007-12-01 19:55:06+01:00, tnurnberg@mysql.com +8 -0
  Bug#31177: Server variables can't be set to their current values
  
  5.1+ specific fixes (plugins etc.)

See also Bug#6958

pushed to 5.0.52, 5.1.23, 6.2.8 in opt

What is 6.2.8? 

I think it should be 5.2.x (if this branch is still maintained) or 6.0.x (or both ... ) 

:-)

6.2.8 is the ndb version. : )  Shoulda been 6.0.4

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Documented bugfix in chanbelogs for 5.0.54, 5.1.23, and 6.0.5 as follows:

        Server variables could not be set to their current values on
        Linux platforms.