Layer7 API Management

  • Private Community

  • 1.  Delete rows

    Posted Jan 11, 2019 08:24 AM

    HI There,

    In our dev in OTK ouath_token we have 4 million rows want to delete 2M from it in any order,what is the best way of doing it?pls advice



  • 2.  Re: Delete rows
    Best Answer

    Broadcom Employee
    Posted Jan 11, 2019 08:41 AM

    Hi Sharath,

     

    Are the tokens expired? Ideally, the maintenance jobs would be managing these, we don't typically recommend manually editing the database. If are you concerned with only expired tokens, the scheduled tasks is the best option. 

    Database Maintenance - CA API Management OAuth Toolkit - 4.3 - CA Technologies Documentation 

     

    If you have a backup of the database and need to edit it directly this page has the queries to purge expired tokens.

     

    Optimization - CA API Management OAuth Toolkit - 3.6 - CA Technologies Documentation 

     

    If you need to delete X number of entries indiscriminately, you can use the limit clause:

     

    DELETE FROM oauth_token WHERE rtoken is null limit 2000000;

     

    Regards,

    Joe



  • 3.  Re: Delete rows

    Broadcom Employee
    Posted Jan 13, 2019 08:12 PM

    If you don't care if token is expired, then it's a question for DBA.

    I would not suggest using DELETE FROM clause if you try to remove big data, as it costs long time.

     

    You can rename token table first and then insert into new table select from old table, and then drop the old table.

    (ie. change delete to insert, insert will be much faster than delete.)

    MySQL :: MySQL 5.7 Reference Manual :: 13.1.33 RENAME TABLE Syntax 

    MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.1 INSERT ... SELECT Syntax 

    This way you need the right sql to create the new token table, "show create table" command will show the create table sql.

     

     If don't want to touch the origin table, you can also insert into a temp table select the data wants to keep from token table -> truncate token table -> insert back data from temp table.

     

    Both ways will be much faster than "delete from" big table. 



  • 4.  RE: Re: Delete rows

    Posted Jun 17, 2020 11:07 AM
    Hello, how are you guys?

    The table OAUTH_TOKEN is huge (250GB). This is production environment.

    There is shell script to delete expired tokens (Doc CA API Management OAuth Toolkit - 3.6 - Page 272).

    But this is very slow and I can`t delete using the commands:

    delete from oauth_token where rtoken is not null and rexpiration < unix_timestamp();

    delete from oauth_token where expiration < unix_timestamp() and rtoken is null;

    Can I simply truncate this table and schedule a job to perform every day the purge of oauth_token and oauth_session?

    What is the effect to truncate oauth_token in production environment?

    Thank you very much


    Original Message


  • 5.  RE: Re: Delete rows

    Broadcom Employee
    Posted Jun 17, 2020 07:52 PM
    Dear Kevin,
    If you can truncate token tables, it will be much easier. The impact I can see is, the client currently has valid token will have to login again since you remove all tokens. (usually it's not a big problem)

    The otk 3.6 is quite old, the new versions have OOTB scheduled tasks for house keeping.

    Regards,
    Mark
    Original Message