Open Stack

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:
>>
>> https://bugs.launchpad.net/1188378
>>
>> 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:
>>
>> https://review.openstack.org/#/c/32044/
>>
>> 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.id).filter(
             TokenModel.expires < cutoff)).limit(500)
while True:
     results = q.all()
     if len(results):
         ids_to_delete = [r[0] for r in results]
         session.query(TokenModel).filter(
             TokenModel.id.in_(ids_to_delete)).delete()
     else:
         break

Code not tested, use with caution, YMMV, etc etc...

Best,
-jay