On 04/18/2016 08:54 PM, Shingo Toda wrote:
Hi all
I am now seeing the SQLAlchemy raise 'NoSuchColumnError' occasionally
when concurrent accesses happen with 'select for update'.
My application uses
* SQLAlchemy 0.8.4
* MySQL-python 1.2.3
* CentOS6.7
* python 2.6
* MariaDB 10.0.20 (three-node Galera Cluster with haproxy 1.5.2)
the column missing from the result set is not typically a result of
concurrent updates or anything like that, if it is happening only
sometimes then the leading suspect is the driver, in this case
MySQL-Python 1.2.3. I would recommend at the very least upgrading to
1.2.5, and preferably swapping it out with mysqlclient which is the
now-maintained fork of MySQL-Python.
On the SQLA side, running logging with echo='debug' will show the column
names being reported for result sets; if these sporadically change to no
longer correspond to the query just emitted, that problem is on the
driver side.
I know Galera does not support explicit lock so I configure haproxy to
redirect incoming requests only to one active node.
The reason of this is to make explicit locking work for 'select for
update', like the way we use single database server.
So the other Galera nodes are setup just for backup.
To break down what that application does, firstly I create engine and
sessioinmaker with isolation_level="READ COMMITTED"
```
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
# 192.168.0.2 is virtual IP
engine = sqlalchemy.create_engine(
"mysql://test:testtest@192.168.0.2/test",
poolclass=NullPool,
isolation_level="READ COMMITTED")
maker = sessionmaker(bind=engine, autocommit=True)
```
Secondary the model is defined below, which has a composite key
(`data_id1`, `data_id2`).
```
Base = declarative_base()
class MyData(Base):
__tablename__ = 'mydata'
__table_args__ = (
UniqueConstraint('data_id1', 'data_id2'),
)
data_id1 = Column(String(64), primary_key=True)
data_id2 = Column(String(64), primary_key=True)
status = Column(String(40), nullable=True)
```
There could be multiple application processes running(not daemon) which
could access to the same record.
Each process firstly tries to lock a row, then check status of MyData
and update status attribute.
```
.....
session.begin()
try:
mydata = get_mydata(session, data_id1, data_id2, lockmode="update")
// do something here to check status
update_status(session, data_id1, data_id2, "some status")
session.commit()
except Exception as e:
session.rollback()
.....
def get_mydata(session, data_id1, data_id2, lockmode=None):
query =
session.query(MyData).filter_by(data_id1=data_id1).filter_by(data_id2=data_id2)
if lockmode:
query = query.with_lockmode(lockmode)
return query.first()
def update_status(session, data_id1, data_id2, status):
data = get_mydata(session, data_id1, data_id2)
data.status = status
session.flush()
```
When concurrent access happens and one process locks a row, subsequent
processes are blocked from processing the same record at the same time.
The subsequent processes will wait until the previous one releases the lock.
This usually works fine as I expect but sometimes I see
`NoSuchColumnError` cast when the subsequent process call
`query.first()` within `update_status()`.
```
Traceback (most recent call last):
File "/root/myapp.py", line 301, in some_func()
update_status(session, data_id1, data_id2, "some status")
File "/root/myapp.py", line 231, in update_status
data = get_mydata(session, data_id1, data_id2)
File "/root/myapp.py", line 256, in get_mydata
return query.first()
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py",
line 2282, in first
ret = list(self[0:1])
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/query.py",
line 2149, in __getitem__
return list(res)
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/loading.py",
line 72, in instances
rows = [process[0](row, None) for row in fetch]
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/loading.py",
line 356, in _instance
tuple([row[column] for column in pk_cols])
File
"/usr/lib64/python2.6/site-packages/sqlalchemy/engine/result.py", line
314, in _key_fallback
expression._string_or_unprintable(key))
NoSuchColumnError: "Could not locate column in row for column
'mydata.data_id1'"
```
I wish someone knows which point causes this error.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
