MySQL Bugs: #54215: Example SET scope defaults to GLOBAL, should specify SESSION

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:	MacOS (N/A)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	document, set scope

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

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.

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.