Under what circumstances would you want Rails to be set NOT to reconnect to MYSQL

As you pointed out in the question, one possible side-effect of automatically reconnecting (if done at a per-statement level), is that it is not transaction-safe.

The MySQL documentation in fact explicitly states that the auto-reconnect feature affects transactions:

Any active transactions are rolled back and autocommit mode is reset.

Applications that are not written to deal with this could easily break. The documentation also lists a number of other side effects caused by the auto-reconnect feature, all of which could cause applications not written to anticipate the behavior to function incorrectly or fail.

Also, if the connection to the database is suddenly lost, the server might not properly release locks that were being held by the connection, so it sounds like an application could deadlock in some cases:

If the connection drops, it is possible that the session associated with the connection on the server side will still be running if the server has not yet detected that the client is no longer connected. In this case, any locks held by the original connection still belong to that session, so you may want to kill it by calling mysql_kill().

Edit: The MySQL documentation link in the answer doesn't seem to exist now. Find the updated documentation here

From the Rails 2.3 release notes (emphasis mine):

4.8 Reconnecting MySQL Connections

MySQL supports a reconnect flag in its connections – if set to true, then the client will try reconnecting to the server before giving up in case of a lost connection. You can now set reconnect = true for your MySQL connections in database.yml to get this behavior from a Rails application. The default is false, so the behavior of existing applications doesn’t change.