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

9 comments:

ntavares 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

obat herbal said...

treatment khusus penyakit wasir cara mengobati wasir mengetahui ciri ciri penyakit wasir obat ambeien herbal ambeclear

herbal medicine said...

obat wasir racikan herbal dan pengobatan penyakit wasir dan cara mengobati penyakit wasir dan obat herbal penyakit wasir dan obat wasir ambeien dan cara menyembuhkan ambeien atau wasir dan obat wasir ampuh dan cara menyembuhkan wasir dan obat herbal wasir dan cara mengobati ambeien dan obat herbal wasir dan obat ambeien ampuh merupakan solusi pengobatan herbal dari denature indonesia

wasirdenature said...

nanah keluar dari kemaluan cairan nanah keluar dari kemaluan obat cairan nanah keluar dari kemaluan obat untuk cairan nanah yang keluar dari kemaluan obat ujung kemaluan keluar cairan nanah obat ujung kemaluan keluar cairan nanah cara mengobati ujung kemaluan keluar nanah ujung kemaluan keluar nanah di sertai perih saat kencing penyebab penis keluar nanah penyebab penis keluar nanah cara mengobati penis keluar nanah kelamin pria keluar nanah obat kelamin pria keluar nanah obat kelamin pria keluar cairan nanah mengapa alat kelamin keluar nanah kemaluan laki laki keluar nanah kemaluan keluar nanah kemaluan keluar cairan nanah mengapa kemaluan keluar nanahcairan nanah keluar dari kemaluan kencing perih dan keluar cairan nanah cairan nanah keluar dari kemaluan pria mengapa cairan nanah keluar dari kemaluan kemaluan pria keluar nanah

wasirdenature said...

penyebab kemaluan keluar cairan nanah cara mengobati kemaluan keluar nanah kemaluan keluar nanah di sertai perih saat kencing mengobati kemaluan keluar nanah pada pria mengobati kemaluan keluar cairan nanah pada pria mengobati kelamin keluar nanah mengobati kemaluan pria yang keluar nanah nanah keluar dari kemaluan cairan nanah keluar dari kemaluan obat cairan nanah keluar dari kemaluan obat untuk cairan nanah yang keluar dari kemaluan obat ujung kemaluan keluar nanah obat ujung kemaluan keluar cairan nanah cara mengobati ujung kemaluan keluar nanah ujung kemaluan keluar nanah di sertai perih saat kencing penis keluar cairan nanah dan kencing perih penyebab penis keluar nanah cara mengobati penis keluar nanah obat alat kelamin keluar nanah obat cairan nanah keluar dari kemaluan obat cairan nanah keluar dari kemaluan mengapa kemaluan keluar nanah cara mengobati kemaluan keluar nanah obat kemaluan keluar nanah

lionelmessi10 said...

nanah keluar dari kemaluan cairan nanah keluar dari kemaluan obat cairan nanah keluar dari kemaluan obat untuk cairan nanah yang keluar dari kemaluan obat ujung kemaluan keluar cairan nanah obat ujung kemaluan keluar cairan nanah cara mengobati ujung kemaluan keluar nanah ujung kemaluan keluar nanah di sertai perih saat kencing penyebab penis keluar nanah penyebab penis keluar nanah cara mengobati penis keluar nanah kelamin pria keluar nanah obat kelamin pria keluar nanah obat kelamin pria keluar cairan nanah mengapa alat kelamin keluar nanah kemaluan laki laki keluar nanah kemaluan keluar nanah kemaluan keluar cairan nanah mengapa kemaluan keluar nanahcairan nanah keluar dari kemaluan kencing perih dan keluar cairan nanah cairan nanah keluar dari kemaluan pria mengapa cairan nanah keluar dari kemaluan kemaluan pria keluar nanah

marko said...

Who doesn't care to be the lord of streets? Most likely everybody does! There are shrouded dreams in every body. Presently the time now unravel your fantasy and make it a reality! In the event that you can't bear the cost of purchasing naturally it is fine, there are a lot of organizations offering credits. cash advance

marko said...

In spite of the fact that it may help assuage somebody of some of their monetary commitments, it can frequent them for a considerable length of time to come. When record of your liquidation hits your credit report, it can be hard to get financing from a budgetary establishment. On the off chance that you are hunting down auto credits after insolvency, your objective is to get qualified at the best rate and terms. Payday Loans Costa-mesa