Bug #66884 SERVER_STATUS is always inited with SERVER_STATUS_AUTOCOMMIT=1
Submitted: 20 Sep 2012 3:16 Modified: 29 Jan 2013 20:03
Reporter: dennis GAO Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.5, 5.0.97, 5.1.69, 5.5.31, 5.7.1 OS:Linux (I only test it on ubuntu 11.10)
Assigned to: CPU Architecture:Any
Tags: autocommit

[20 Sep 2012 3:16] dennis GAO
Description:
I am trying to parse the OK packet of mysql server to get the value of
flag  SERVER_STATUS_AUTOCOMMIT.

But I meet an odd phenomenon:
     If I start mysqld with option autocommit=0, and I send a query
"create table XXX",
the return OK packet will be with SERVER_STATUS_AUTOCOMMIT=1.

Code:
short a =(server_status & SERVER_STATUS_AUTOCOMMIT)?1:0;
a is equal to 1.

I read the mysql code of mysql 5.5, I found that the thd->server_status
will always be inited with SERVER_STATUS_AUTOCOMMIT=1, and this flag only
be reset when the user change the global/session variable autocommit with a
different value.

So If I start mysqld with option autocommit=0, and do not execute a query
"set autocommit=1", the thd->server_status will be 1 all the time, with a
different value with the global/session variable.

Is it correct?

Looking forward to any responses, and thanks in advance

How to repeat:
Start mysqld with option autocommit=0, and do not execute a query
"set autocommit=1".

Then login the mysql execute "select @@autocommit;" or "show variables like '%autocommit%'". You will see the return value 0.

Fetch the return OK packet of query "select @@autocommit;", you can see the server_status & SERVER_STATUS_AUTOCOMMIT=1.

Suggested fix:
Init the thd->server_status according to the global variables.
[20 Sep 2012 7:56] Peter Laursen
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_autocommit

"Before MySQL 5.5.8, the global autocommit value cannot be set at startup. As a workaround, set the init_connect system variable: 
SET GLOBAL init_connect='SET autocommit=0';"

.. wo what is the exact version you are using?

Peter
(not a MySQL/Oracle person)
[20 Sep 2012 9:30] dennis GAO
My mysql version:
Server version: 5.5.13-debug-log Source distribution.

And I think I set the global and session variable successfully by adding "autocommit=0" into my.cnf before starting the mysqld server.

Now the problem is that the return OK packet contains a SERVER_STATUS with a wrong value for SERVER_STATUS_AUTOCOMMIT.

If I start mysqld with autocommit=0, the OK packet should contains SERVER_STATUS with SERVER_STATUS_AUTOCOMMIT=0, rather than 1.
[20 Sep 2012 12:36] Davi Arnaut
Yeah, the flag is not properly adjusted when the session is initialized (or reinitialized). See THD::init, compare to how the NO_BACKSLASH_ESCAPES value is properly reflected in the server_status flag.
[29 Jan 2013 20:03] Sveta Smirnova
Thank you for the report.

Verified as described.
[9 Jan 2017 18:01] Rene' Cannao'
tcpdump output

Attachment: 66884.tcp.txt (text/plain), 9.53 KiB.

[9 Jan 2017 18:04] Rene' Cannao'
It seems that not only SERVER_STATUS is always inited with SERVER_STATUS_AUTOCOMMIT=1 , but also running "set autocommit=0" doesn't change SERVER_STATUS with SERVER_STATUS_AUTOCOMMIT=0 .
See attached file