MySQL disable autocommit, as well as the problems encountered

MySQL autocommit (auto-commit) is on by default, it has a certain impact on MySQL performance, for example, if you insert 1000 data, MySQL will commit1000 times, if we put autocommit off, through the program to control , only one commit is allowed.

1, we can set the autocommit

View copy print?

2. Mysql> set global init_connect="set autocommit=0"; //Prompt you to set up with a higher-privileged financial account
4. ERROR 1227 (42000): Access denied; Need (at least one's) the SUPER privilege (s) for this operation
6. Mysql> set autocommit=0;
8. Query OK, 0 rows Affected (0.00 sec)
12. Mysql> SELECT @ @autocommit; //Check the settings of the autocommit
14. +--------------+
16. | @ @autocommit |
18. +--------------+
20. | 0 |
22. +--------------+
24. 1 row in Set (0.00 sec)

2, we can modify the MySQL configuration file my.cnf to close autocommit

View copy print?

1. [Mysqld]
2. init_connect=' SET autocommit=0 ' //Add these to mysqld

With the second method off, one thing to note, the access to the MySQL user can not be greater than the rights to start the MySQL user, otherwise init_connect= ' SET autocommit=0 ' will not function, will not report any errors , sweat a first. See the following example

View copy print?

2. [Email protected]:~$ mysql-umysql
4. Welcome to the MySQL Monitor. Commands End With;  or \g.
6. Your MySQL Connection ID is 1
8. Server Version:5.5.2-m2-log Source Distribution
12. Type ' help; ' or ' \h ' for help .  Type ' \c ' to clear the current input statement.
16. Mysql> SELECT @ @autocommit; //mysql is the start user, shutdown autocommit successful
18. +--------------+
20. | @ @autocommit |
22. +--------------+
24. | 0 |
26. +--------------+
28. 1 row in Set (0.00 sec)
32. Mysql> ctrl-c-- exit!
34. Aborted
36. [Email protected]:~$ mysql-uroot
38. Welcome to the MySQL Monitor. Commands End With;  or \g.
40. Your MySQL Connection ID is 2
42. Server Version:5.5.2-m2-log Source Distribution
46. Type ' help; ' or ' \h ' for help .  Type ' \c ' to clear the current input statement.
50. Mysql> SELECT @ @autocommit;  //Start with root account, unsuccessful.
52. +--------------+
54. | @ @autocommit |
56. +--------------+
58. | 1 |
60. +--------------+
62. 1 row in Set (0.00 sec)

Will this be a MySQL bug? I looked for this question on the Internet, and there really was. Some of the contents are as follows:
If a user has SUPER privilege, Init_connect would not execute
(otherwise if Init_connect would a wrong query no one can connect to server).

Note, if Init_connect is a wrong query, the connection was closing without any errors
And next command would clause ' Lost connection ' error.

It's a little clear in there. If a user has SUPER privilege, Init_connect would not execute, Init_connect does not execute if the user has more advanced permissions.

http://blog.csdn.net/ying_593254979/article/details/12095169

MySQL disable autocommit, as well as the problems encountered (GO)