6

Open up two mysql command-line clients and connect to your database. In client #1, enter

START TRANSACTION;

In client #2, use SHOW PROCESSLIST, then

KILL [n];

where n is the id for client #1's connection. Bam—transaction rolled back. But client #1 doesn't know that. Then from client #1, send some command—say,

UPDATE clients SET important_field = NULL;

You'll get the response:

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

Query OK, 10000 rows affected (0.05 sec)
Rows matched: 10000  Changed: 10000  Warnings: 0

Oops, you say, better hit ROLLBACK! Then you realize, to your horror, that you're not in a transaction anymore.

Here's my question: In case I want to KILL a connection at some point, is there any way I can ensure that this "now you're in a transaction, now you're not" scenario doesn't happen, short of setting autocommit to 0 at the system level?

Note that the above is tested under mysql 5.1—if later versions provide a fix, I'd love to hear about it. I'd also love to see tests done with JDBC, ADO.NET, etc. to see whether they're susceptible to this same issue.

(On a meta note, this question arose from two questions over at Server Fault. I'm really hoping that the DBA community will prove more helpful...)

Update: See my answers below. This issue appears to be unique to the mysql command-line utility, with its bizarre auto-reconnect "feature." Most likely, any tool or library not built on top of the mysql utility will not exhibit this behavior. However, you may want to test whatever you're using to be sure, or take drachenstern's suggestion and wrap your transactions in stored procedures.

|improve this question
  • 1
    The MySQL protocol allows for safe transactions. An infinite number of programs, like the command-line client, can be written that won't allow an end user to safely perform one. :) – Dan Grossman Mar 4 '11 at 3:03
  • This is astonishing - I can assure you Oracle doesn't behave like this. Kill a session, its work will be rolled back, and when it reconnects, it will implicitly start a brand new transaction. More evidence that MySQL is only suited for trivial applications. – Gaius Mar 4 '11 at 23:32
7

What was wrong with the answers on those two questions? They were 100% accurate, so I'm not sure what more we can do to help you here.

I would suggest that you confirm that you never assume that you're in a transaction. Always check to make sure you are. In TSQL it would be as simple as checking @@TRANCOUNT to be greater than 0. That's rather the same as any threaded situation where you want to check mutexes. What happens if you do it with a stored procedure? It kills the stored procedure, yes? Because SPs are intended to be atomic. What you're demonstrating has nothing to do with atomicity.

To be clear, this behavior is by design! Don't run atomic transactions by hand in the console, put them in a program so if the connection goes away it's gone. This is not something that you can "just hope works right".

If you have something that must be ACID, you must put it in a container that can be made ACID. This means a stored procedure or the like.

|improve this answer
  • 1
    I want to frame your first paragraph. :D – hobodave Mar 4 '11 at 8:39
  • I'd like to frame the question.. :-) – Marian Mar 4 '11 at 11:26
  • Thanks, this is a good answer. Even on the command line, starting your operation with CREATE PROCEDURE, and putting a transaction within that procedure block, ensures safety. It does have the unfortunate side effect of hampering interactivity, though. Is there any way to say "Run this query if I'm within a transaction" in MySQL? – Trevor Burnham Mar 4 '11 at 17:46
  • @Trevor Yes, by creating a procedure. Outside of that, no. This is a matter of atomicity. In theory it could be done, but in practice, you're too slow. You operate at what to the computer appear to be glacial speeds. The amount of buffering it would have to maintain on the server would effectively lock whatever data source you were trying to manipulate from everyone else's use. Are you looking to prevent malicious coworkers from screwing you over or are you trying to prevent yourself from doing dumb things? What's the issue with creating procedures? Besides "hampering interactivity"? – jcolebrand♦ Mar 4 '11 at 18:07
  • It's just that it feels nice to start a transaction, make some changes, then explore a bit to see what the ramifications of those changes were before deciding to commit the transaction. I've accepted your answer, but it would be cool if there were a way to safely do this. – Trevor Burnham Mar 4 '11 at 18:22
1

It turns out that there's a simpler answer than I'd previously suggested: When starting the mysql client, use the --disable-reconnect flag.

The original problem stemmed from the mysql command-line client's bizarre default behavior of automatically reconnecting and sending the given command, no matter how potentially harmful, if the connection has been lost. With --disable-reconnect, here's what happens after it gets killed and I send the query:

UPDATE clients SET important_field = NULL;

The response is

ERROR 2006 (HY000): MySQL server has gone away

and the query doesn't go through. Further queries also yield the same error, until I manually reconnect.

So, if you're working from the mysql command-line client on a non-trivial database, I'd recommend using --disable-reconnect by default.

This also helps to allay my concerns about bindings in Java, Ruby, etc.; while I'd certainly recommend testing any particular binding you use, it seems unlikely that any popular MySQL library would use the "automatically reconnect and resend" behavior. However, coders must be careful that their programs don't have any functions that might inadvertently do this (say, retrying an individual query after an exception is thrown rather than restarting the entire transaction).

|improve this answer
0

Use Ruby. Using the canonical mysql gem:

require 'rubygems'
require 'mysql'

my = Mysql.new '127.0.0.1', 'root', 'root', 'test_db'
my.autocommit 0
sleep 10
st = my.prepare 'insert into foo (name) values (?)'
st.execute 'bar'
my.commit

If a kill is issued during those 10 seconds of sleep, a Mysql::Error is emitted from the next line with the message "MySQL server has gone away," and the insertion doesn't occur. I put ActiveRecord through a similar test; not surprisingly, its behavior was identical, since it's built on top of the mysql gem.

So if you use Ruby's mysql library, you can work without fear of KILL-related catastrophe. This also means you could use an irb session to perform a safe transaction interactively.

I suspect that this is equally true of all popular MySQL language bindings, but this is the only one I've tested personally.

|improve this answer
  • 1
    You ... don't read so well do you? I said to put it in an application or a stored procedure. put them in a program so if the connection goes away it's gone So the trick is: once a connection is made from a program and a transaction is started, and the connection evaporates (KILL) then the transaction gets rolled back. If a connection is made, the program hasn't used it yet, and the connection evaporates, an error is thrown. This now becomes an SO concern (insuring that an operation occurs with an unsteady connection). – jcolebrand♦ Mar 7 '11 at 3:29
  • But I'm very glad that you worked on it and sorted it out and found how to recreate it in ruby or another language. In my case I would suggest using SSMS but you're not on SQL so obviously that doesn't work in your case ;) ~ Otherwise, I don't know what else you can do. What were you trying to prevent in the first place? Coworkers killing your executing code? – jcolebrand♦ Mar 7 '11 at 3:31
  • Yes, I realize that you mentioned putting the transaction in a program. However, you also suggested that to safely run a transaction interactively, I'd have to "dupe it out to a testing database." I wanted to point out that interactive transactions can be done safely from irb, Ruby's command-line utility. – Trevor Burnham Mar 7 '11 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.