cloudbau/nova — pull request #1 by mouadino (2012-12-21 07:09:38+00:00)
Title: Add fail over inside nova SQLAlchemy session handler

Hi,

The patch attached enable us to configure nova with fail over redundant db backends.

The patch start by defining a new configuration option ``extra_sql_connections`` that should be set to a list of redundant SQL connection that nova will fail over one of them when the *current* ``sql_connection`` go down.

###### Note:
My first intuition was to change ``sql_connection`` to be a list (``sql_connections``) of all db backends that are part of the redundant cluster (in our case it's Galera cluster), but i wasn't sure if this will not break anything that rely already on ``sql_connection`` being a simple string option, that's why i opted for two config option but IMHO it's better to have only one option.

###### Note:
The failover logic is implemented inside the database pool (SQLAlchemy or Eventlet), so one thing to note is that the engine is not aware of which connection it get (to which backend), one consequence of this is that the engine.url attribute will not reflect the currently used backend, moreover right now there is no way to know the current used engine db bakend.

#### Testing:

The pull request doesn't contain any new test cases b/c it wasn't trivial for me, how to do so, most test cases inside the nova project use SQLite as a backend (except for ``test_migration.py``, i believe), but i did wrote some tests in my local machine, but they are hardly automatic (need to stop the MySQL server manually), which i am including here:

```Python
self.flags(sql_dbpool_enable=False) # Both True or False should be supported.
self.flags(
extra_sql_connections=["mysql://root:password@/nova"]
)
engine = session.create_engine("mysql://root:password@/nova")
engine.execute("INSERT INTO example values (1, 'test')")
# While the code is in sleep, i stop the mysql server in machine1 to let machine2 take over.
import time; time.sleep(10)
engine.execute("INSERT INTO example values (2, 'test')")
```

#### Problem:

The current patch doesn't handle disconnect failure inside a transaction i.e. when autocommit set to False or when an explicit transaction is used, example where things can go wrong:

```Python
s = session.get_session()
with s.begin():
s.execute("INSERT INTO example values (16, 'test2')")
import time; time.sleep(10)
# Stoping machine 1 MySQL between this 2 queries,
# will raise OperationalError (2006, 'MySQL server has gone away')
s.execute("INSERT INTO example values (17, 'test3')")
```

The reason for this is that the connection is checked only at the beginning of the transaction (using checkout event), and AFAIK SQLAlchemy [don't try or known how to re-connect inside a transaction](http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg09181.html), so when this later lose connection inside a transaction, we can't easily switch to a new backend in the middle of a transaction, that why in this case a disconnect error will be raised.
One thing to note here is that after SQLA raise the disconnect error it will also recycle the entire connection pool which mean that transactions that come after will have fresh/new connection.

#### Possible solution:

There is a number of solution that i was thinking of to fix this problem, if it's needed, one will be to create a sub-class of sqlalchemy's Session and override the execute method to rollback and retry when there is a disconnection failure inside a transaction.

But i am kind of still new to OpenStack, so i don't want to jump in and start creating code w/o having a PoC that this can happen in reality.

What do you think about the general approach that i took here ? Did i miss something ? And what about the problem of disconnection in the middle of a transaction ?

Cheers,

--
Mouad