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
Tags: qc
Triage: Triaged: D2 (Serious)

[24 Sep 2007 18:43] Baron Schwartz
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'
[24 Sep 2007 19:21] Peter Laursen
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>
[24 Sep 2007 19:30] Peter Laursen
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>
[24 Sep 2007 19:37] Baron Schwartz
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?
[24 Sep 2007 19:58] Peter Laursen
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!
[25 Sep 2007 11:04] Sveta Smirnova
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;
[25 Sep 2007 11:14] Baron Schwartz
A couple of them are not large values, and are also not being set right.
[25 Sep 2007 12:23] Peter Laursen
@sveta

isn't what I found on Windows/MySQL 5.0.45 a bug too?
(my 2nd post here)
[25 Sep 2007 12:41] Sveta Smirnova
Peter,

thank you for the feedback. This seems to be buggy too, but seems to be same bug as this one.
[29 Oct 2007 13:27] Timour Katchaounov
Bad data, changing to P2.
[25 Nov 2007 18:05] Tatiana Azundris Nuernberg
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! : )
[25 Nov 2007 18:05] Bugs System
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.
[26 Nov 2007 5:21] Tatiana Azundris Nuernberg
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".)
[30 Nov 2007 5:32] Bugs System
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.
[1 Dec 2007 18:55] Bugs System
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.)
[3 Dec 2007 8:18] Tatiana Azundris Nuernberg
See also Bug#6958
[6 Dec 2007 9:37] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23, 6.2.8 in opt
[6 Dec 2007 9:53] Peter Laursen
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 ... ) 

:-)
[10 Dec 2007 2:48] Tatiana Azundris Nuernberg
6.2.8 is the ndb version. : )  Shoulda been 6.0.4
[14 Dec 2007 8:14] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:17] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:20] Bugs System
Pushed into 6.0.5-alpha
[14 Jan 2008 20:36] Jon Stephens
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.
[27 Oct 2008 15:18] Sven Sandberg
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.
[27 Oct 2008 15:23] Sven Sandberg
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;
[6 Nov 2008 7:15] Tatiana Azundris Nuernberg
$ 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.
[10 Nov 2008 17:35] Bugs System
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.
[28 Nov 2008 0:57] Tatiana Azundris Nuernberg
queued to 6.0.9-bugteam (tested on lin86 64-bit, some add'l tests on 32-bit)
[8 Dec 2008 11:34] Bugs System
Pushed into 6.0.9-alpha  (revid:azundris@mysql.com-20081110173013-wehyxajole61nzsv) (version source revid:ingo.struewing@sun.com-20081128084401-fmehh9vu9y2ijjev) (pib:5)
[8 Dec 2008 16:45] Paul Dubois
Noted in 6.0.9 changelog.
[9 Dec 2008 20:29] Baron Schwartz
You're not going to fix this in 5.0 or 5.1?  Seriously?
[11 Dec 2008 21:58] Sergei Golubchik
We are now, thanks!
[12 Jan 2009 11:15] Bugs System
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.
[14 Jan 2009 9:32] Bugs System
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
[15 Jan 2009 6:40] Bugs System
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)
[15 Jan 2009 16:16] Paul Dubois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:26] Bugs System
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)
[19 Jan 2009 13:04] Bugs System
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)
[19 Jan 2009 14:41] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:10] Bugs System
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)
[20 Jan 2009 18:56] Bugs System
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)
[28 Jan 2009 21:53] Paul Dubois
Noted in 6.0.10 changelog.
[5 May 2010 15:14] Bugs System
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)
[6 May 2010 3:01] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:49] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:18] Bugs System
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)
[28 May 2010 6:46] Bugs System
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)
[28 May 2010 21:45] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:14] Bugs System
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)
[15 Jun 2010 8:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:49] Bugs System
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)
[17 Jun 2010 12:27] Bugs System
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)
[17 Jun 2010 13:14] Bugs System
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)