Re: [sqlalchemy] DB sync on application level

[Cornelinux K]

Hi Michael,

thanks for your response.
At least it shows me, that obviously this seems to be no common idea - 
mostly known as a bad idea.

I do not want to sync anything on a database level, but on a logical level. 
It is not important, that entries have the same index. 

In theory it would not even be important, that two nodes would ran the same 
database type.

Let's boil it down to a simple example of two nodes. Each node being an 
authentication system increasing the counter in the database entry on 
successful or failed authentication (for what it's worth - HOTP).
The authentication object exists on both nodes. The authentication request 
hits the first node.
The first node would have to check, if this object is locked.
If not, the first node would have to lock the object on the other nodes.
Then it performes the authentication an modifies the counters in the 
database object.
Then it transmits the changes to the other nodes and
...tells them to release the lock.

Sounds straight to me. (Maybe I am a bit naive.

The locking mechanism would also happen on the application level. But I was 
wondering if there are existing mechanism, tools or concepts to get started 
with.

Kind regards
Cornelius

Am Sonntag, 27. September 2015 04:14:25 UTC+2 schrieb Michael Bayer:
>
>
>
> On 9/26/15 6:35 AM, Cornelinux K wrote:
>
> Hello, 
>
> I have an application that uses an SQL database with also many write 
> access.
> Now I am thinking of high availability. One solution was to set up a mysql 
> master master replication.
>
> that's a good solution.
>
>
>
> But I was thinking, there might be some good aspects when doing the 
> synchronization on the application level. 
>
>
> I'm not familiar with any.   You can't do real HA synchronization at the 
> application level because you are unable to control the transaction log 
> ordering at that level.  You commit your transaction to database A, great, 
> now your homegrown synchronization thing tries to send the same thing to 
> database B, and deadlock!  write conflict!  other entirely garden-variety 
> concurrency issues!  now your HA is Un-A.  
>
> You could use more than two partners, the setup *should* be simpler (a 
> design goal ;-)
>
>
> totally simple setup, sure.   But the effort attempting to get any kind of 
> result near something ordinary like Galera, vast, and even then it won't 
> even work that well.
>
>
>
> Are there any best practices or examples how this can be "easier" achieved 
> with SQLAlchemy.
>
> not really, you'd do something like intercept the execution events, record 
> the statements being received, and then when the transaction is committed, 
> sync all those operations out to the other system.   E.g. you'd track 
> http://docs.sqlalchemy.org/en/rel_1_0/core/events.html#sqlalchemy.events.ConnectionEvents.after_execute
>  
> and 
> http://docs.sqlalchemy.org/en/rel_1_0/core/events.html#sqlalchemy.events.ConnectionEvents.commit,
>  
> and probably a bunch of other events.
>
>
>
> I.e. checking a lock on an entry, locking the entry on all sync parners, 
> before updating this entry, unlocking on sync partners.
>
> I'd install Galera.
>
> Recovery after a failure of a node...
>
>
> I'd put HAProxy in front of Galera.
>
>
>
> Thanks a lot and kind regards
> Cornelius
>
> -- 
> 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 .
> To post to this group, send email to sqlal...@googlegroups.com 
> .
> Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] DB sync on application level

[Cornelinux K]

Hello,

I have an application that uses an SQL database with also many write access.
Now I am thinking of high availability. One solution was to set up a mysql 
master master replication.

But I was thinking, there might be some good aspects when doing the 
synchronization on the application level. You could use more than two 
partners, the setup *should* be simpler (a design goal ;-)

Are there any best practices or examples how this can be "easier" achieved 
with SQLAlchemy.
I.e. checking a lock on an entry, locking the entry on all sync parners, 
before updating this entry, unlocking on sync partners.
Recovery after a failure of a node...

Thanks a lot and kind regards
Cornelius

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] No attribute of child relation contained in parent object

[cornelinux]

Hi,

I am just starting to use the relation between two tables.
The problem started with the child data not being written to the child
tables.
I played around, and now I am totally confused.
Maybe someone can shed some light on this.

I got these tables:

{{{
user_table = sa.Table('User', meta.metadata,
sa.Column('UserId',  sa.types.Integer(),
primary_key=True),
sa.Column('UserDesc',sa.types.UnicodeText(),
default=u''),
)

userrealm_table = sa.Table('UserRealm', meta.metadata,
sa.Column('id', sa.types.Integer(), primary_key=True),
sa.Column('user_id',
sa.types.Integer(),ForeignKey('User.UserId')),
sa.Column('realm_id', sa.types.Integer(),
nullable=False )
)
}}}

I got the following classes:

{{{
class User(object):
def __init__(self,  desc):
log.debug(' __init__(%s)' % desc)
self.UserDesc = serial

classe UserRealm(object):
def __init__(self, realm):
log.debug(setting realm_id to %i % realm)
self.realm_id = realm
}}}

A user may belong to several realms. All the relation stuff should be
done in the mappers:

{{{
orm.mapper(UserRealm, userrealm_table)
orm.mapper(User, user_table, properties={
'children':relationship(UserRealm,backref='user', cascade=save-
update)
})
}}}

Now I am at the point, that the User object contains no attribute
identifying the realm...
I thought this attribute should be generated by the relation
definition?

Any ideas on this?

Kind regards
Cornelius

-- 
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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.