Thank you very much for your quick response. I will try your suggestion.
I just wonder if you know there is any bug fix e.g. identical commit log
for this as, if possible, I want a kind of evidence that upgrading the
driver could solve my problem.
On Tuesday, April 19, 2016 at 10:56:06 PM UTC+9, Mike Bayer wrote:
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
<http://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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<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.
