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",
> > 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.
> > 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.
