autocommit setting ignored

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What would cause the set autocommit=0 to be ignored by clients when I
started my instance with the cnf file below?

[mysqld]
init_connect='SET AUTOCOMMIT=0'
innodb_buffer_pool_size = 512M
transaction_isolation = READ-COMMITTED
log-bin
sync_binlog=1


The clients have no autocommit setting in their cnf files.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with PCLinuxOS - http://enigmail.mozdev.org

iEYEARECAAYFAkeRNJgACgkQzIf+rZpn0oTi9wCfWVBLTD9K+c VhqOsVbguSMrJL
xM8AoIxcjOT4bPBO0YlzC0xCY3ZAePAu
=v0V9
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No responses to what's below the first time around. Are there any takers
yet?

Chuck wrote:
| What would cause the set autocommit=0 to be ignored by clients when I
| started my instance with the cnf file below?
|
| [mysqld]
| init_connect='SET AUTOCOMMIT=0'
| innodb_buffer_pool_size = 512M
| transaction_isolation = READ-COMMITTED
| log-bin
| sync_binlog=1
|
|
| The clients have no autocommit setting in their cnf files.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with PCLinuxOS - http://enigmail.mozdev.org

iEYEARECAAYFAkeWJqoACgkQzIf+rZpn0oQFygCfZh2BhKxIN+ ToafNlNmpdoCEa
FewAniEk1rlF6nWBvGe+AUngF+f4gw9U
=zqCV
-----END PGP SIGNATURE-----

On Tue, 22 Jan 2008 18:23:54 +0100, Chuck <skilover_nospam@bluebottle.com>
wrote:
> Chuck wrote:
> | What would cause the set autocommit=0 to be ignored by clients when I
> | started my instance with the cnf file below?
> |
> | [mysqld]
> | init_connect='SET AUTOCOMMIT=0'
> | innodb_buffer_pool_size = 512M
> | transaction_isolation = READ-COMMITTED
> | log-bin
> | sync_binlog=1
> No responses to what's below the first time around. Are there any takers
> yet?

Well, don't toppost :P

1. What does 'select @@autocommit;' tell you?
2. Do the users perhaps have SUPER privilige? (In which case init_connect
is ignored)
3. Keep in mind not all engines support transactions.
--
Rik Wasmus

On Tue, 22 Jan 2008 17:23:54 GMT, Chuck wrote:
>
> No responses to what's below the first time around. Are there any takers
> yet?
>
> Chuck wrote:
>| What would cause the set autocommit=0 to be ignored by clients when I
>| started my instance with the cnf file below?
>|
>| [mysqld]
>| init_connect='SET AUTOCOMMIT=0'
>| innodb_buffer_pool_size = 512M
>| transaction_isolation = READ-COMMITTED
>| log-bin
>| sync_binlog=1
>|
>|
>| The clients have no autocommit setting in their cnf files.

Not so far apparently. But you haven't given us much to work with in
terms of what the client is doing. What's it written in/what connector
is it using, for example? How long does this connection sit between
doing things? Does it instantly start committing? What makes you suspect
that this is happening?

--
CANNIBAL, n.
A gastronome of the old school who preserves the simple tastes and adheres
to the natural diet of the pre-pork period.

Rik Wasmus wrote:
> On Tue, 22 Jan 2008 18:23:54 +0100, Chuck
> <skilover_nospam@bluebottle.com> wrote:
>> Chuck wrote:
>> | What would cause the set autocommit=0 to be ignored by clients when I
>> | started my instance with the cnf file below?
>> |
>> | [mysqld]
>> | init_connect='SET AUTOCOMMIT=0'
>> | innodb_buffer_pool_size = 512M
>> | transaction_isolation = READ-COMMITTED
>> | log-bin
>> | sync_binlog=1
>> No responses to what's below the first time around. Are there any takers
>> yet?
>
> Well, don't toppost :P
>
> 1. What does 'select @@autocommit;' tell you?
> 2. Do the users perhaps have SUPER privilige? (In which case
> init_connect is ignored)
> 3. Keep in mind not all engines support transactions.

Ok won't top post. I don't usually. Must have been temporarily possessed
by a Microsoft demon. :)

select @@autocommit returns 1.

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

I think you hit the nail on the head though with question #2. I recently
gave myself super privilege. Is there a way to force autocommit=0 for
super users? I already had it in my client's .my.cnf file, but that was
also being ignored.

[client]
host = <removed>
password = <removed>
[innodb]
autocommit=0

Is there a reason that autocommit=1 is forced for super
userS?

Thanks.

Peter H. Coffin wrote:
> On Tue, 22 Jan 2008 17:23:54 GMT, Chuck wrote:
>> No responses to what's below the first time around. Are there any takers
>> yet?
>>
>> Chuck wrote:
>> | What would cause the set autocommit=0 to be ignored by clients when I
>> | started my instance with the cnf file below?
>> |
>> | [mysqld]
>> | init_connect='SET AUTOCOMMIT=0'
>> | innodb_buffer_pool_size = 512M
>> | transaction_isolation = READ-COMMITTED
>> | log-bin
>> | sync_binlog=1
>> |
>> |
>> | The clients have no autocommit setting in their cnf files.
>
> Not so far apparently. But you haven't given us much to work with in
> terms of what the client is doing. What's it written in/what connector
> is it using, for example? How long does this connection sit between
> doing things? Does it instantly start committing? What makes you suspect
> that this is happening?
>

The client is the mysql program using whatever the default mysql
connector is.

On Tue, 22 Jan 2008 19:15:47 GMT, Chuck wrote:
> I think you hit the nail on the head though with question #2. I recently
> gave myself super privilege. Is there a way to force autocommit=0 for
> super users? I already had it in my client's .my.cnf file, but that was
> also being ignored.
>
> [client]
> host = <removed>
> password = <removed>
> [innodb]
> autocommit=0
>
> Is there a reason that autocommit=1 is forced for super
> userS?

SUPER seems designed to ensure that SUPER users can always log into a sane
and predictable environment, regardless of how screwed up the setting in
various .cnf files may be, so that the screwed up bits can be fixed. I
wouldn't consider it a suitable flag for general use on a functional
user ID.

--
38. If an enemy I have just killed has a younger sibling or offspring
anywhere, I will find them and have them killed immediately, instead of
waiting for them to grow up harboring feelings of vengeance towards me in
my old age. --Peter Anspach's list of things to do as an Evil Overlord

On Tue, 22 Jan 2008 20:15:47 +0100, Chuck <skilover_nospam@bluebottle.com>
wrote:

> Rik Wasmus wrote:
>> On Tue, 22 Jan 2008 18:23:54 +0100, Chuck
>> <skilover_nospam@bluebottle.com> wrote:
>>> Chuck wrote:
>>> | What would cause the set autocommit=0 to be ignored by clients when I
>>> | started my instance with the cnf file below?
>>> |
>>> | [mysqld]
>>> | init_connect='SET AUTOCOMMIT=0'
>>> | innodb_buffer_pool_size = 512M
>>> | transaction_isolation = READ-COMMITTED
>>> | log-bin
>>> | sync_binlog=1
>>> No responses to what's below the first time around. Are there any
>>> takers
>>> yet?
>> Well, don't toppost :P
>> 1. What does 'select @@autocommit;' tell you?
>> 2. Do the users perhaps have SUPER privilige? (In which case
>> init_connect is ignored)
>> 3. Keep in mind not all engines support transactions.
>
> Ok won't top post. I don't usually. Must have been temporarily possessed
> by a Microsoft demon. :)
>
> select @@autocommit returns 1.
>
> mysql> select @@autocommit;
> +--------------+
> | @@autocommit |
> +--------------+
> | 1 |
> +--------------+
>
> I think you hit the nail on the head though with question #2. I recently
> gave myself super privilege. Is there a way to force autocommit=0 for
> super users? I already had it in my client's .my.cnf file, but that was
> also being ignored.
>
> [client]
> host = <removed>
> password = <removed>
> [innodb]
> autocommit=0
>
> Is there a reason that autocommit=1 is forced for super
> userS?

Not necessarily the autocommit is ignored, but init_connect is. Super
users would have a hell of a time connecting if it contained an error, see
also http://bugs.mysql.com/bug.php?id=5035

autocommit=1 is sadly the default, I personally do not know about any
method how to get it to work in a my.cnf for super users, save for just
doing a SET just after connecting.
--
Rik Wasmus