Bug #54215 Example SET scope defaults to GLOBAL, should specify SESSION
Submitted: 3 Jun 2010 18:23 Modified: 9 Jun 2010 1:33
Reporter: Ronald Bradford Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.39 OS:Mac OS X (N/A)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: document, set scope

[3 Jun 2010 18:23] Ronald Bradford
Description:
As described at 
* http://dev.mysql.com/doc/refman/5.1/en/commit.html
* http://dev.mysql.com/doc/refman/5.0/en/commit.html

START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

...The SET autocommit statement disables or enables the default autocommit mode for the current session.

The SET command, needs to state SET SESSION otherwise the autocommit state is changed for the entire system, not just the session.

How to repeat:
mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

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

mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Update documentation syntax
[4 Jun 2010 2:36] Paul DuBois
There is no GLOBAL autocommit prior to 5.5.3. Presumably what you are seeing is that SHOW GLOBAL VARIABLES shows the session value if there is no global value.

From 5.5.3 on, you will observe different behavior, I believe, because there is a GLOBAL autocommit.
[9 Jun 2010 1:33] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

With the GLOBAL modifier, SHOW VARIABLES displays the values that are
used for new connections to MySQL. As of MySQL 5.5.3, if a variable
has no global value, no value is displayed. Before 5.5.3, the session
value is displayed. With SESSION, SHOW VARIABLES displays the values
that are in effect for the current connection. If no modifier is
present, the default is SESSION. LOCAL is a synonym for SESSION.