MySQL Bugs: #5035: init_connect='SET AUTOCOMMIT=0' doesn't work

Bug #5035	init_connect='SET AUTOCOMMIT=0' doesn't work
Submitted:	13 Aug 2004 19:03	Modified:	17 Aug 2004 11:27
Reporter:	Georg Richter	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4 and 5.0.2	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
according to the manual it is possible to set autocommit mode in my.cnf: 
 
This variable can also be set on the command line or in an option file. To set the variable as just 
shown using an option file, include these lines:  
[mysqld] 
init_connect='SET AUTOCOMMIT=0' 
 
 This variable was added in MySQL 4.1.2.  
 
but looks like it doesn't work. 

How to repeat:
beethoven:/home/georg/work/php/php5/ext/mysqli # grep -C1 "AUTOCOMMIT" /etc/my.cnf 
[mysqld] 
init_connect='SET AUTOCOMMIT=0' 
datadir=/data/mysql-4.1 
beethoven:/home/georg/work/php/php5/ext/mysqli # /usr/local/mysql-4.1/bin/mysqld_safe & 
[1] 8739 
beethoven:/home/georg/work/php/php5/ext/mysqli # Starting mysqld daemon with databases 
from /data/mysql-4.1 
 
beethoven:/home/georg/work/php/php5/ext/mysqli # exit 
georg@beethoven:~/work/php/php5/ext/mysqli> /usr/local/mysql-5.0/bin/mysql -uroot test 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 3 to server version: 4.1.4-beta-debug-log 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
4.1.4-beta-debug-log 
[19:00] root@test> select @@autocommit; 
+--------------+ 
| @@autocommit | 
+--------------+ 
|            1 | 
+--------------+ 
1 row in set (0.00 sec) 
 
4.1.4-beta-debug-log 
[19:01] root@test>

I find that on Mac OS X, setting init_connect dynamically
causes clients to fail.  The server does not crash; what
happens is that the connection is lost as soon as the client
issues the first statement. (The \s command works, but
not statements sent to the server.)

Looks like it's not a bug. If a user has SUPER privilege, init_connect will not execute
(otherwise if init_connect will a wrong query no one can connect to server). 

Note, if init_connect is a wrong query, the connection is closing without any errors
and next command will clause 'lost connection' error.

Paul, could you document this?

I am trying to turn off the autocommit option.  But it is not working for me.
I am working on Linux RH9, with the latest version of MySQL running over it.

[root@cgi12 ~/mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.4-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mean
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_mean |
+----------------+
| learn          |
+----------------+
1 row in set (0.00 sec)

mysql> select * from learn;
Empty set (0.00 sec)

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

mysql> insert into learn values('sujay','2000B3A7479',151);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from learn;
+-------+------------+------+
| name  | id         | room |
+-------+------------+------+
| sujay | 2000B3A747 |  151 |
+-------+------------+------+
1 row in set (0.00 sec)

mysql>

I also added the following in my my.cnf
init_connect='SET AUTOCOMMIT=0'

Is there any hack for this or is this a bug now ??