Is there a way to get the default value of autocommit in MySQL?

Question

I'm just wondering as per the title if this can be done? For example, if I was to do something like this:

$this->db->autocommit(false);

But then afterwards I wanted to set it back to it's default value; I can't just assume it's defaults to true and do this:

$this->db->autocommit(true);

I would like to reset it back to it's default value if possible; or what do other people do when wanting a single query to auto commit? Do you always run:

$this->db->autocommit(true);

...before the query to make sure that autocommit is on?

Forgo

Answer 1

http://php.net/manual/en/mysqli.autocommit.php

To determine the current state of autocommit use the SQL command SELECT @@autocommit.

It seems silly to determine if it's on and if not turn it on though.

Another way would be using:

SHOW VARIABLES WHERE Variable_name='autocommit';

Also autocommit among other variables are on a per-connection basis, you setting autocommit to true will not affect any other connections. The only way to find what the default value is, is by checking right when the connection is made (before changing the state).

Suspensory

I don't want to get the current state, I want to get the default value. It seems to me that it would be good practice to return something to it's normal state after changing it, otherwise you will have to always turn it on/off every time you run a query to make sure it's how you want it. – Forgo

Updated with more info. – Suspensory

Thanks. Yeah, I know it's a per-connection basis, but I guess if you change it to false in one-place and then in another place afterwards where the code assumed the default then you could run into problems..... perhaps the best method is to just always set it before all relevant queries!? – Forgo

Yeah that'd be the easiest way, although if you want to be extremely safe you could keep track of what it is before changing, then afterwards change it back. This way whatever the surrounding code was expecting it to be, it will be. – Suspensory

"SHOW VARIABLES" won't work on recent mysql versions (>5.6.x), so "SELECT @@autocommit" is the only way. For backword compatibility I use "SELECT @@autocommit as Value" so I can read always the field 'Value' whatever version is mysql. – Confederate

IDK much, but executing SHOW GLOBAL VARIABLES WHERE Variable_name='autocommit'; it was showing a different value. 🤷 – Printable

Answer 2

With the MySQL query below, you can check if autocommit is on("1") or off("0"):

SELECT @@autocommit; -- "1" or "0"

Latimer