[sqlalchemy] Error: Packet sequence number wrong for read replica

[Ali Hopyar]

0


We have an environment with the following libraries and versions;
Python 3.8 
Flask 1.0.2 
Flask-SQLAlchemy 2.3.0 
SQLAlchemy 1.2.10 
PyMySQL 1.0.2

Normally, we run on a single RDS Mysql but because of the high load we 
needed to add a read replica and we have following configurations;
SQLALCHEMY_DATABASE_URI = //DB URL 
SQLALCHEMY_ECHO = False 
SQLALCHEMY_TRACK_MODIFICATIONS = False 
SQLALCHEMY_POOL_RECYCLE = 60 
SQLALCHEMY_POOL_SIZE = 10 SQLALCHEMY_MAX_OVERFLOW = 30 # Default to 10 
SQLALCHEMY_POOL_TIMEOUT = 60 # Default to 30 
SQLALCHEMY_BINDS = { 'read': Read Db URL }

Before creating the read replica we would use db and session like;
from flask_sqlalchemy import SQLAlchemy 
db = SQLAlchemy(session_options={"expire_on_commit": False}) 
db.session.query(MyEntity).filter_by(id=self.id).all()

After creating the read replica we have added a read_session like;
read_engine = db.get_engine(bind='read') 
session = db.create_scoped_session(options={'bind': read_engine, 
'expire_on_commit': False}) 
read_session = session() 


and started use it like;
read_session.query(MyEntity).filter_by(id=self.id).all()

but after this point, our application started to produce errors like Packet 
sequence number wrong - got x but expected y and Can't reconnect until 
invalid transaction is rolled back .

When I googled the error, I see that the reason of this error is 
multi-threading and it says every request should be handled in separate 
sessions but I wonder how it would work before creating the read replica. 
It seems SQLAlchemy uses the same session for all request because session 
is created with same way we did in SQLAlchemy instance. Related code in 
SQLAlchemy =>
def __init__(self, app=None, use_native_unicode=True, session_options=None, 
metadata=None, query_class=BaseQuery, model_class=Model): 
self.use_native_unicode = use_native_unicode 
self.Query = query_class 
self.session = self.create_scoped_session(session_options) # here is 
how session created in SQL Alchemy
self.Model = self.make_declarative_base(model_class, metadata) 
self._engine_lock = Lock() 
self.app = app _include_sqlalchemy(self, query_class) 

if app is not None: 
self.init_app(app) 

I wonder, what we did wrong and how we can create a session to use every 
where like SQLAlchemy did it?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/24b36433-5ff1-483b-a304-49bb451564dcn%40googlegroups.com.

[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.

[Ali]

Excellent, that (expire, refresh) works. Thank-you very much.

Is there a reason why it sometimes works?

On Feb 20, 11:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Feb 20, 2007, at 12:01 PM, Ali wrote:



  u = User(name='Ali')
  u.flush()
  r = Receipt(user_id=u.id)
  r.flush()
  r.user is u
  True
  r.cancelled_user_id = u.id
  r.flush()
  r.cancelled_user is u
  True

 oh sorry, i missed this one.  when you say r.cancelled_user_id =
 u.id, that will send the right SQL to the database but isnt going to
 change the r.cancelled_user property to anything unless you refresh
 ()/expire() the Receipt object.

 references:

 http://www.sqlalchemy.org/docs/
 unitofwork.myt#unitofwork_api_flush_whatishttp://www.sqlalchemy.org/trac/wiki/
 FAQ#Isetthefoo_idattributeonmyinstanceto7butthefooattributeisstillNonesh
 ouldntithaveloadedFoowithid7


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Multiple Foreign Keys to the same table, problems with second object.

[Ali]

Hi,

I am using the recipe for multiple foreign keys to the same table at
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/MultiFkRelations

I am also using SessionContext, and assign_mapper

It works well, except for one issue. The second object (ie that which
the second foreign key references) is only sometimes loaded, and other
times it is None.

Here is my code (with other fields removed):

stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)

class Receipt(object):
Stock Receipt

assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
)

There is also a table user with an id column, which is reverenced
here.

So in an example

# Connect to the database, and create the tables etc

 u = User(name='Ali')
 u.flush()
 r = Receipt(user_id=u.id)
 r.flush()
 r.user is u
True
 r.cancelled_user_id = u.id
 r.flush()
 r.cancelled_user is u
True

So in this example it works perfectly as expected. However in my code
(a single threaded  PyGTK application, it doesn't work all the time,
and r.cancelled_user returns None more often than not. When the
application is restarted, the value is always correct.

The way I found to force it to work was to explicitly look up the
value like:

class Receipt(object):
Stock Receipt
# A hack to force the getting of the second object
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)

I realise this is ugly and breaks all sorts of things in what it
expects User to be able to do with sessions etc, but I could not find
another reliable way to do it. The fact that it occurs only sometimes
may imply some threading issues I guess, but I am not sure where to
look.

When echoing is turned on and r.cancelled_user is accessed after
flushing, a new query is not made (in the application), but it is made
in the example above.

Thank-you for any assistance,

Ali


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.

[Ali]

I left out the fields for brevity of example. They both have an id
column defined as primary keys.

On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:
 On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:





  Here is my code (with other fields removed):

  stockreceipt_table = Table('stockreceipt', meta,
  Column('user_id', Integer, ForeignKey('user.id')),
  # Cancellation
  Column('cancelled_user_id', Integer, ForeignKey('user.id')),
  )

  class Receipt(object):
  Stock Receipt

  assign_mapper(ctx, Receipt, stockreceipt_table,
  properties=dict(
  user=relation(User, backref='receipts',
  primaryjoin=stockreceipt_table.c.user_id ==
  users_table.c.id),
  cancelled_user=relation(User, backref='cancelled_receipts',
  lazy=False,
  primaryjoin=stockreceipt_table.c.cancelled_user_id ==
  users_table.c.id),
  )

 that cant be right, since theres no primary key expressed either in
 the stockreceipt_table or in your Receipt mapper.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.

[Ali]

By both, I mean user and receipt tables

On 20 Feb, 17:12, Ali [EMAIL PROTECTED] wrote:
 I left out the fields for brevity of example. They both have an id
 column defined as primary keys.

 On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:

  On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:

   Here is my code (with other fields removed):

   stockreceipt_table = Table('stockreceipt', meta,
   Column('user_id', Integer, ForeignKey('user.id')),
   # Cancellation
   Column('cancelled_user_id', Integer, ForeignKey('user.id')),
   )

   class Receipt(object):
   Stock Receipt

   assign_mapper(ctx, Receipt, stockreceipt_table,
   properties=dict(
   user=relation(User, backref='receipts',
   primaryjoin=stockreceipt_table.c.user_id ==
   users_table.c.id),
   cancelled_user=relation(User, backref='cancelled_receipts',
   lazy=False,
   primaryjoin=stockreceipt_table.c.cancelled_user_id ==
   users_table.c.id),
   )

  that cant be right, since theres no primary key expressed either in
  the stockreceipt_table or in your Receipt mapper.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.

[Ali]

Ok,

from sqlalchemy import *


from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext

meta = DynamicMetaData()

ctx = SessionContext(create_session)

def make_engine(uri=None):
if uri is None:
uri = 'sqlite:///:memory:'
engine = create_engine(uri, echo=True)
return engine

def connect(uri=None):
meta.connect(make_engine(uri=uri))


users_table = Table('user', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('password', String),
Column('registration', String),
)

class User(object):
User
def get_is_admin(self):
return self.id == 1
is_admin = property(get_is_admin)

def __str__(self):
s = self.name
if self.registration:
s = '%s (%s)' % (s, self.registration)
return s

assign_mapper(ctx, User, users_table)

stockreceipt_table = Table('stockreceipt', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('drug_id', Integer, ForeignKey('drug.id')),
Column('date', Date),
Column('quantity', Integer),
Column('supplier_id', Integer, ForeignKey('supplier.id')),
Column('user_id', Integer, ForeignKey('user.id')),
Column('cancelled', Boolean),
Column('cancelled_reason', String),
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
Column('internal_transfer', Boolean),
Column('invoice_number', String),
Column('internal_from', String),
)

class Receipt(object):
Stock Receipt
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)

assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
supplier=relation(Supplier, backref='receipts'),
drug=relation(Drug, backref='receipts'),
)
)

meta.create_all()
connect()

 u = User(name='Ali')
 u.flush()
 r = Receipt(user_id=u.id)
 r.flush()
 r.user is u
True
 r.cancelled_user_id = u.id
 r.flush()
 r.cancelled_user is u
True

Now, as I mentioned, r.cancelled_user is set correctly in this
example, but not (always) in my application, although it is used
similarly. (I am happy to paste 2000 lines of PyGTK if you like). When
the attribute is accessed, there is no SQL output at all, although in
this example there is.

Thanks for any assistance,

Ali


On Feb 20, 6:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 why dont you attach a script that runs completely ?  use a sqlite://
 database.

 On Feb 20, 12:14 pm, Ali [EMAIL PROTECTED] wrote:

  By both, I mean user and receipt tables

  On 20 Feb, 17:12, Ali [EMAIL PROTECTED] wrote:

   I left out the fields for brevity of example. They both have an id
   column defined as primary keys.

   On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:

On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:

 Here is my code (with other fields removed):

 stockreceipt_table = Table('stockreceipt', meta,
 Column('user_id', Integer, ForeignKey('user.id')),
 # Cancellation
 Column('cancelled_user_id', Integer, ForeignKey('user.id')),
 )

 class Receipt(object):
 Stock Receipt

 assign_mapper(ctx, Receipt, stockreceipt_table,
 properties=dict(
 user=relation(User, backref='receipts',
 primaryjoin=stockreceipt_table.c.user_id ==
 users_table.c.id),
 cancelled_user=relation(User, backref='cancelled_receipts',
 lazy=False,
 primaryjoin=stockreceipt_table.c.cancelled_user_id ==
 users_table.c.id),
 )

that cant be right, since theres no primary key expressed either in
the stockreceipt_table or in your Receipt mapper.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.

[Ali]

Thanks for the advice, and sorry I didn't provide the correct
information, but ctx.current.flush() makes no difference.
r.cancelled_user is (nearly: 19/20) always None immediately after the
assign and flush with no database access being made. Interestingly,
the user_id/user relationship is always correct

I will just have to stick to using the hack on the Receipt object to
get the cancelled_user until I am more familiar with SA to work out
what is going on.

Ali

Missing Tables:

suppliers_table = Table('supplier', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('address', String),
Column('postcode', String),
Column('telephone', String),
)

class Supplier(NamedObject):
Supplier
def get_long_string(self):
return '%s (%s %s %s)' % (self.name, self.address,
self.postcode, self.telephone)

assign_mapper(ctx, Supplier, suppliers_table)

forms_table = Table('form', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
)

class Form(NamedObject):
Drug Form

assign_mapper(ctx, Form, forms_table)

drugs_table = Table('drug', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('strength', String),
Column('stock', Integer),
Column('form_id', Integer, ForeignKey('form.id'))
)

class Drug(object):
Drug Definition
def __str__(self):
return '%s (%s %s)' % (self.name, self.form, self.strength)
str = property(__str__)

assign_mapper(ctx, Drug, drugs_table,
properties = dict(
form = relation(Form, backref='drugs')
)
)


On Feb 20, 9:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 well I cant run the program since its not complete (no Drug, Supplier
 class/tables defined, etc), but if id hazard a guess id say you
 shouldnt be calling flush() on your class instances, since it will
 not flush any changes on any dependencies.  call ctx.current.flush()
 instead.

 On Feb 20, 2007, at 1:40 PM, Ali wrote:



  Ok,

  from sqlalchemy import *

  from sqlalchemy.ext.assignmapper import assign_mapper
  from sqlalchemy.ext.sessioncontext import SessionContext

  meta = DynamicMetaData()

  ctx = SessionContext(create_session)

  def make_engine(uri=None):
  if uri is None:
  uri = 'sqlite:///:memory:'
  engine = create_engine(uri, echo=True)
  return engine

  def connect(uri=None):
  meta.connect(make_engine(uri=uri))

  users_table = Table('user', meta,
  Column('id', Integer, autoincrement=True, primary_key=True),
  Column('name', String),
  Column('password', String),
  Column('registration', String),
  )

  class User(object):
  User
  def get_is_admin(self):
  return self.id == 1
  is_admin = property(get_is_admin)

  def __str__(self):
  s = self.name
  if self.registration:
  s = '%s (%s)' % (s, self.registration)
  return s

  assign_mapper(ctx, User, users_table)

  stockreceipt_table = Table('stockreceipt', meta,
  Column('id', Integer, autoincrement=True, primary_key=True),
  Column('drug_id', Integer, ForeignKey('drug.id')),
  Column('date', Date),
  Column('quantity', Integer),
  Column('supplier_id', Integer, ForeignKey('supplier.id')),
  Column('user_id', Integer, ForeignKey('user.id')),
  Column('cancelled', Boolean),
  Column('cancelled_reason', String),
  Column('cancelled_user_id', Integer, ForeignKey('user.id')),
  Column('internal_transfer', Boolean),
  Column('invoice_number', String),
  Column('internal_from', String),
  )

  class Receipt(object):
  Stock Receipt
  def get_cancelled_user_hack(self):
  return User.get_by(id=self.cancelled_user_id)
  cancelled_user_hack = property(get_cancelled_user_hack)

  assign_mapper(ctx, Receipt, stockreceipt_table,
  properties=dict(
  user=relation(User, backref='receipts',
  primaryjoin=stockreceipt_table.c.user_id ==
  users_table.c.id),
  cancelled_user=relation(User, backref='cancelled_receipts',
  lazy=False,
  primaryjoin=stockreceipt_table.c.cancelled_user_id ==
  users_table.c.id),
  supplier=relation(Supplier, backref='receipts'),
  drug=relation(Drug, backref='receipts'),
  )
  )

  meta.create_all()
  connect()

  u = User(name='Ali')
  u.flush()
  r = Receipt(user_id=u.id)
  r.flush()
  r.user is u
  True
  r.cancelled_user_id = u.id
  r.flush()
  r.cancelled_user is u
  True

  Now, as I mentioned, r.cancelled_user is set correctly in this
  example, but not (always) in my application, although it is used
  similarly. (I am happy to paste 2000 lines of PyGTK if you like). When
  the attribute is accessed, there is no SQL output at all, although in
  this example there is.

  Thanks for any assistance,

  Ali

  On Feb 20, 6:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
  why dont you attach

[sqlalchemy] Re: py2exe sqlalchemy

[Ali]

Yes, I imagined something like that. I only posted to avert users from
norcebo effect that they might encounter reading this thread.

On Feb 17, 5:16 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 if you didnt have to change SA's logging module, its possibly
 because py2.5 has better support for absolute module imports (or
 maybe py2exe does).

 On Feb 17, 2007, at 11:58 AM, Ali wrote:



  Hi,

  I might be going insane, but I am successfully using Sqlalchemy
  (0.3.4, Python2.5, py2exe 0.6.6) with py2exe without any problems.

  I did (as suggested) an easy_install --always-unzip to prevent
  having an egg, and then included 'packages': 'sqlalchemy' in the
  options dict for py2exe.

  e.g.

  options = {
  'py2exe' : {
  'packages': 'encodings, sqlalchemy', etc.

  I am waiting for it to explode...

  Ali

  On Feb 9, 4:12 pm, Michael Bayer [EMAIL PROTECTED] wrote:
  add an entry to the FAQ page on the wiki.  possibly with a new FAQ
  category integrating with external tools.

  Oleg Deribas wrote:
  Hello,

  Something Special said the following on 08.02.2007 13:22:

  they should put this help somewhere on main page, or fix it:)

  Michael, would you accept such patch?

  --
  Oleg


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---