MySQL Bugs: #51175: Can't specify default value for autocommit

Bug #51175	Can't specify default value for autocommit
Submitted:	14 Feb 2010 22:24	Modified:	10 Jan 2011 16:04
Reporter:	Tim Behrendsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.1.42	OS:	Any
Assigned to:	Valeriy Kravchuk	CPU Architecture:	Any
Tags:	autocommit

Description:
Summary: I would like to be able to run commands from a file upon start-up of the 'mysql' command line client. This is to fix the (arguable) problem that autocommit cannot be turned off reliably by default.

How to repeat:
Feature request.

Suggested fix:
My feature request is something that I believe would not only be very simple to implement, but only incredibly useful. I would like to see the 'mysql' command line client look for a .mysqlrc file in the user directory that would execute commands upon start-up, and before it reads commands from stdin.

The biggest reason I want it is so I can reliably turn off autocommit when running the mysql command, which is (seemingly) not currently possible (it's somewhat possible to do it with init_connect, but the latter doesn't run in all cases).

It would also be useful for a wide variety of other commands, such as setting the default date output.

Other details that would be useful to consider:

1) Be able to specify an alternate file using a command line option.

2) Have a system-wide mysqlrc in /etc.

Implementing this feature would make make a huge number of people happy who (like me) search in vain for a way to turn off autocommit. :)

Thanks for all your hard work and consideration of this.

Tim Behrendsen

What about init_connect server variable? Check http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_init_connect.

Thank you for the response. Yes, I've tried to use that, but unfortunately that method has this wrinkle:

> Note that the content of init_connect is not executed for users that have the
> SUPER privilege.

I'm limited in what I can tweak for the privileges of my connecting users, and I haven't been able to turn off autocommit because of the above restriction. It doesn't seem to be currently possible to turn off autocommit in all cases.

Having many users with SUPER privilege is the problem by itself, but OK, for some cases your feature request makes sense it seems.

I've changed the synopsis and categorization of this bug. It has very little to do with needing a .mysqlrc (which already exists in the form of my.cnf, etc) and everything to do with allowing the default value for autocommit to be set on the server in a way that doesn't depend on configuring all clients correctly.

It is true that the core issue is disabling autocommit in a comprehensive way, but another issue is that I'm a little afraid to disable autocommit globally. I've seen references that importing and exporting can be affected by this.

To try and combat setting it globally, I've experimented with putting:

---------
[mysqld]
init_connect="set autocommit=0"
---------

...in the .my.cnf for my local user, and it doesn't seem to work like putting in the global file. So that's been a problem (if I'm doing it wrong, please let me know). And even if I got this to work, I still have the user permissions problem.

While it's true that .my.cnf serves somewhat the same purpose as a .mysqlrc file, the truth is that it's not very flexible. It would be much more flexible to be able to put *any* command into a start-up script. (Another idea that just occurred to me is to have a [startup] section in .my.cnf that would read commands, which would eliminate having another file.)

I suppose what would totally solve my immediate problem is having a "--autocommit=0" on the mysql command line client. Then I could alias the mysql command to do that all the time, and wouldn't affect the rest of the system. That's probably the most "it ought to be there anyway" solution.

But having the start-up script would be really cool. :) You could have it dump out database stats whenever you ran the mysql command. If you found yourself commonly creating a temporary table, you could have it do that. The possibilities of a start-up script are endless.

Thanks for the responses!

Actually, I'd say that fix for Bug#57316 (that allows to set autocommit globally, even for SUPER users, using init_connect) solves this problem. 

Now (since 5.5.8) there is a way to set default value of autocommit for all users.