We have some code running against MySQL running in Amazon RDS. During an
outage or the maintenance window - Amazon issues an automatic failover of
our RDS instance. However our code doesn't failover with it.
We are having issues with 2 of our applications (2 different issues):
First one: Unable to rollback when we get the error "2006, 'MySQL server
has gone away'"
The issue we are having is that a DB operation will fail when the failover
occurs. So in the event of an error we issue a rollback on the SqlSoup
object (which delegates to the underlying session).
sqlsoup_engine.rollback()
However this rollback fails with the error:
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/ext/sqlsoup.py",
line 602, in rollback
self.session.rollback()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py",
line 114, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 625, in rollback
self.transaction.rollback()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 339, in rollback
transaction._rollback_impl()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 367, in _rollback_impl
t[1].rollback()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2035, in rollback
self._do_rollback()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2072, in _do_rollback
self.connection._rollback_impl()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1265, in _rollback_impl
self._handle_dbapi_exception(e, None, None, None, None)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 1262, in _rollback_impl
self.engine.dialect.do_rollback(self.connection)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py",
line 1903, in do_rollback
connection.rollback()
OperationalError: (OperationalError) (2006, 'MySQL server has gone away')
None None
Subsequently our application continues on an then starts failing with:
StatementError("Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back)",)
The second error is: 2003, "Can't connect to MySQL server on
'mydbname.us-east-1.rds.amazonaws.com' (111)
File "/home/ec2-user/myapp/python/myapp.py", line 188, in _write
sqlsoup_engine.commit()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/ext/sqlsoup.py",
line 610, in commit
self.session.commit()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py",
line 114, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 656, in commit
self.transaction.commit()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 314, in commit
self._prepare_impl()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 298, in _prepare_impl
self.session.flush()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 1583, in flush
self._flush(objects)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 1654, in _flush
flush_context.execute()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
line 331, in execute
rec.execute(self)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py",
line 475, in execute
uow
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
line 45, in save_obj
uowtransaction)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
line 138, in _organize_states_for_save
states):
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py",
line 759, in _connections_for_states
base_mapper)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 194, in connection
return self._connection_for_bind(engine)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 256, in _connection_for_bind
conn = self._parent._connection_for_bind(bind)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 267, in _connection_for_bind
conn = bind.contextual_connect()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/base.py",
line 2490, in contextual_connect
self.pool.connect(),
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 224, in connect
return _ConnectionFairy(self).checkout()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 387, in __init__
rec = self._connection_record = pool._do_get()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 741, in _do_get
con = self._create_connection()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 188, in _create_connection
return _ConnectionRecord(self)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 270, in __init__
self.connection = self.__connect()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/pool.py",
line 330, in __connect
connection = self.__pool._creator()
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py",
line 80, in connect
return dialect.connect(*cargs, **cparams)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/sqlalchemy/engine/default.py",
line 281, in connect
return self.dbapi.connect(*cargs, **cparams)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/MySQLdb/__init__.py",
line 81, in Connect
return Connection(*args, **kwargs)
File
"/home/ec2-user/myapp/pyenv/lib/python2.7/site-packages/MySQLdb/connections.py",
line 187, in __init__
super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (OperationalError) (2003, "Can't connect to MySQL
server on 'mydbname.us-east-1.rds.amazonaws.com' (111)") None None
It appears that the DNS lookup is cached (speculation?). RDS does a DNS
swap on the url so it points to the slave instance. So if a DNS lookup was
already done and the IP was cached if this would be the cause. This might
not be anything to do with SqlAlchemy (maybe not even MySQLdb) - but wanted
to check if anyone has seen similar behaviour before.
These applications can handle the rollback as long as it is able to
re-connect and continue on. It will just re-process the records it was
trying to operate on. But it gets in this stuck state and requires us to
manually restart them for it to recover. So my questions are:
1) How should we be recovering when the server disappears? If a rollback
is not going to succeed when the underlying DB has gone away - what is the
best action to take?
2) Is there a good way to be handling the connection (with SqlSoup) so that
it will auto-reconnect AND re-resolve DNS lookups?
Thanks,
Jonathan
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/GSWRS_jNNmsJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
