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 18:43	Modified:	18 Jun 2010 1:07
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.38, 4.1, 5.0, 5.1, 5.2 BK	OS:	Linux (Ubuntu)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	qc

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.

I can still reproduce this in 5.1 with this test case:

SELECT @@global.myisam_max_sort_file_size;
SET @@global.myisam_max_sort_file_size = @@global.myisam_max_sort_file_size;
SELECT @@global.myisam_max_sort_file_size;

It changes the value from 0x7fffffff to 0x7ff00000. Re-opening bug.

I can also reproduce it with:

SELECT @@global.max_binlog_cache_size;
SET @@global.max_binlog_cache_size = @@global.max_binlog_cache_size;
SELECT @@global.max_binlog_cache_size;

$ dc
18446744073709551615 4096 3 k / p
4503599627370495.999
18446744073709547520 4096 / p
4503599627370495.000
9223372036854775807 1024 1024 * / p
8796093022207.999
9223372036853727232 1024 1024 * / p
8796093022207.000
1 + p 
8796093022208.000
1024 * 1024 * p 9223372036854775807 - p
9223372036854775808.000
1.000
4503599627370495.000 1 + p 4096 * p 18446744073709551615 - p
4503599627370496.000
18446744073709551616.000
1.000

In real terms, suppose the data type is 16 bit unsigned int,
block-size is 1024, max is 65535 (2^16-1), and default value
is max. This means that the default has an "illegal" value,
that is, one that's not an integer multiple of 1024 (65535
rather than 65536). Trying to set to current value will correct
to nearest integer multiple of 1024 (65536 - 1024 = 63512).
That's the difference we see here. Now let's see why we can
set an illegal initial value, and whether that works on both
32 and 64 bit.

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/58358

2692 Tatiana A. Nurnberg	2008-11-10
      Bug#31177: Server variables can't be set to their current values
      
      Bounds-checks and blocksize corrections were applied to user-input,
      but constants in the server were trusted implicitly. If these values
      did not actually meet the requirements, the user could not set change
      a variable, then set it back to the (wonky) factory default or maximum
      by explicitly specifying it (SET <var>=<value> vs SET <var>=DEFAULT).
      
      Now checks also apply to the server's presets. Wonky values and maxima
      get corrected at startup. Consequently all non-offsetted values the user
      sees are valid, and users can set the variable to that exact value if
      they so desire.

queued to 6.0.9-bugteam (tested on lin86 64-bit, some add'l tests on 32-bit)

Pushed into 6.0.9-alpha  (revid:azundris@mysql.com-20081110173013-wehyxajole61nzsv) (version source revid:ingo.struewing@sun.com-20081128084401-fmehh9vu9y2ijjev) (pib:5)

Noted in 6.0.9 changelog.

You're not going to fix this in 5.0 or 5.1?  Seriously?

We are now, thanks!

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/62972

2732 Tatiana A. Nurnberg	2009-01-12
      Bug#31177: Server variables can't be set to their current values
      
      Bounds-checks and blocksize corrections were applied to user-input,
      but constants in the server were trusted implicitly. If these values
      did not actually meet the requirements, the user could not set change
      a variable, then set it back to the (wonky) factory default or maximum
      by explicitly specifying it (SET <var>=<value> vs SET <var>=DEFAULT).
      
      Now checks also apply to the server's presets. Wonky values and maxima
      get corrected at startup. Consequently all non-offsetted values the user
      sees are valid, and users can set the variable to that exact value if
      they so desire.

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/63197

2748 He Zhenxing	2009-01-14 [merge]
      Auto merge

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Setting status back to NDI pending merge to 6.0 tree.

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20090112055714-z8uf3u87fqr4y44e) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.10 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)