How to delete old build log?

msglight4874 · August 26, 2019, 3:21pm

Hi,

How can i delete old build log?
My drone sqlite database is bigger and bigger day by day and machine disk became almost full…

Thank for your helping.

bradrydzewski · August 26, 2019, 3:43pm

Have you considered using S3 or an S3 compatible storage system to store your logs? https://docs.drone.io/administration/server/database/#s3-storage

Alternatively you can create a shell script / SQL statement to delete old logs and you can schedule this script using your host machine’s cron scheduer. You would be responsible for creating the shell script / SQL statement.

msglight4874 · August 27, 2019, 12:54am

Ok, I will try deleting old log by writing my shell script.

Thank you.

I wrote this SQL. It works pretty well.

gist.github.com

https://gist.github.com/msglight4874/0bfc5520cce019c5b2d595236f141dd5

remove_old_builds.sql

WITH n_build_ids_per_repo as (
  SELECT build_id
  FROM (
    SELECT
      build_id,
      build_repo_id,
      DENSE_RANK() OVER (PARTITION BY build_repo_id ORDER BY build_id DESC) AS rank
    FROM builds
  ) AS t
  WHERE t.rank <= 1000

This file has been truncated. show original

ask · November 8, 2021, 6:37pm

@msglight4874 Any chance you’d put your GitHub gist back?

msglight4874 · November 9, 2021, 1:30am

Sure.

gist.github.com

https://gist.github.com/david-liu-950627/0bfc5520cce019c5b2d595236f141dd5

remove_old_builds.sql

-- Thank @sbengo to figure out foreign_keys constraints is defaults to false in sqlite
-- Enable to delete logs by cascading delete
PRAGMA foreign_keys = ON;

WITH n_build_ids_per_repo as (
  SELECT build_id
  FROM (
    SELECT
      build_id,
      build_repo_id,

This file has been truncated. show original