Friday, August 28, 2009

SHOW GLOBAL VARIABLES ... autocommit?? What?

The autocommit variable is a session only variable, right? Easy to confirm by:
mysql> select @@global.autocommit;
ERROR 1238 (HY000): Variable 'autocommit' is a SESSION variable


But if I do this another way, I get a different result:
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)


To me, this is a bug, right? But before I report it, I wanted to see if you agree, or? Might there be stuff out there that relies on this strange behaviour. And it gets worse, I'm afraid. In a new MySQL session:
mysql> show session variables where variable_name = 'tmp_table_size' or variable
_name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | ON |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.00 sec)

mysql> show global variables where variable_name = 'tmp_table_size' or variable_
name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | ON |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.01 sec)

Looks fair, right. So now lets set these two variables in this session:
mysql> set tmp_table_size = 102400;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

OK, now let's look at the result, in global and session scope again:
mysql> show session variables where variable_name = 'tmp_table_size' or variable
_name = 'autocommit';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| autocommit | OFF |
| tmp_table_size | 102400 |
+----------------+--------+
2 rows in set (0.00 sec)

mysql> show global variables where variable_name = 'tmp_table_size' or variable_
name = 'autocommit';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| autocommit | OFF |
| tmp_table_size | 13631488 |
+----------------+----------+
2 rows in set (0.00 sec)


Whoa, this doesn't look right, does it? I change one variable and it affects GLOBAL scope, but not for the other variable. And there is a difference between these two variables, which is that autocommit is really a session only variable! So why in heavens name does it pop up when I do a SHOW GLOBAL VARIABLES? And why is the global value changed when I set the session value? This seems to be how it works for all session only variables?
There might be some sense in this, that I do not understand, so please enlighten me! But if this is the case, then the docs are wrong! Like MySQL manual section 5.1.5 that says this: Several system variables exist only as session variables and then goes on to list these, which are all show as above as both local and global (but always with the same value). The same section also states that Most of them are not displayed by SHOW VARIABLES which isn't true at all, actually, they are ALL displayed by SHOW VARIABLES!

And MySQL manual section 12.5.5.41 says that With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL which actually isn't true either (just because I set AUTOCOMMIT to 0, and that is the vale displayed by SHOW GLOBAL VARIABLES, new connection will still have AUTOCOMMIT on).

So, what do you say? Am I just confused? Is the docs wrong, or even right in some awkward way? Or is this a bug? Or? And if you are wondering, the effect on the corresponding INFORMATION_SCHEMA tables is the same...

/Karlsson

2 comments:

Nuno Tavares said...

Hi Karlsson,

I think you were misconcluding some points, and you would understand better if you have a look at:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

You'll see that there are some variables that have "Var scope" as either Session, Global, or Both.

So, imagine that these two scopes are transparent layers, Session piled over Global, and you're looking at it from the top. If you were looking to tmp_table_size, you'd see:
* the GLOBAL value, if there was no SESSION overriden;
* the SESSION value, otherwise.

And that's because tmp_table_size can be seen on both scopes. However, if you were looking to autocommit, you'd only see the SESSION value, since there was no GLOBAL scope. If the SESSION was not touched in the session, then you'd see the (still SESSION) default value.

The only solution to avoid such confusion would be to rip the SESSION variables off the list of SHOW GLOBAL VARIABLES. However you'd loose a "global snapshot of default values" in other scenarios (where you don't change SESSION values). You'd have to issue both SHOW GLOBAL and SHOW SESSION and merge the results to have that snapshot. I think the current is the best way, you don't have to know wether a variable is in one or another scope to check for its value.

However, I agree that the docs could be more precise, stating that the cited note about GLOBAL modifier applies only to Global (scope) variables.

Hope that helps,

Karlsson said...

Well, I understand how they work. And if it worked consistently that way, I would be OK. I don't have a problem with, say, tmp_table_size, that works according to the docs alright.

However, when you say that I would miss a global snapshot of default values if SHOW GLOBAL VARIABLES didn't show the value of, say, autocommit, this is where it goes wrong! As if I set this to 0 on a session level, SHOW GLOBALVARIABLES will show it to be 0, which is NOT the default at login. Whereas for tmp_table_size, it would work just like that.

I think there are two other ways this could work, both better than today:
- SHOW GLOBAL VARIABLES would excluded the session variables completely. This would be reasonable and not cause any confusion.

- SHOW GLOBAL VARIABLES would included the session only variables, just like today, but would show the real "default at login" values of them, i.e. the real default value! Having the session value pop up there is just wrong.

I know what you are saying about how GLOBAL and SESSION variables interact, and now they relate, and I fully understand that. But if a session only variables is shown by SHOW GLOBAL, why can't I do a SELECT @@global.autocommit? I see a different set of values, depending on if I do a SHOW GLOBAL VARIABLES, or if a do a SELECT @@global.<variable name> This just can't be right, in my mind.

So in essence, I fully know how it is supposed to work, but it doesn't consistently do that, in my mind, which is the issue at hand here!

Cheers and thanx for reading!
/Karlsson