[openstack-dev] [Keystone] Best way to do something MySQL-specific?
jaypipes at gmail.com
Tue Jul 9 23:33:45 UTC 2013
On 07/08/2013 05:18 PM, Sean Dague wrote:
> On 07/01/2013 01:35 PM, Clint Byrum wrote:
>> The way the new keystone-manage command "token_flush" works right now
>> is quite broken by MySQL and InnoDB's gap locking behavior:
>> Presumably other SQL databases like PostgreSQL will have similar problems
>> with doing massive deletes, but I am less familiar with them.
>> I am trying to solve this in keystone, and my first attempt is here:
>> However, MySQL does not support using "LIMIT" in a sub-query that
>> is feeding an IN() clause, so that approach will not work. Likewise,
>> sqlalchemy does not support the MySQL specific extension to DELETE which
>> allows it to have a LIMIT clause.
>> Now, I can do some hacky things, like just deleting all of the expired
>> tokens from the oldest single second, but that could also potentially
>> be millions of tokens, and thus, millions of gaps to lock.
>> So, there is just not one way to work for all databases, and we have to
>> have a special mode for MySQL.
>> I was wondering if anybody has suggestions and/or examples of how to do
>> that with sqlalchemy.
> Honestly, my answer is typically to ask Jay, he understands a lot of the
> ways to get SQLA to do the right thing in mysql.
LOL, /me blushes.
In this case, I'd propose something like this, which should work fine
for any database:
cutoff = timeutils.utcnow() - 60 # one minute ago...
# DELETE in 500 record chunks
q = session.query(
TokenModel.expires < cutoff)).limit(500)
results = q.all()
ids_to_delete = [r for r in results]
Code not tested, use with caution, YMMV, etc etc...
More information about the OpenStack-dev