-
-
Save david-zw-liu/0bfc5520cce019c5b2d595236f141dd5 to your computer and use it in GitHub Desktop.
-- 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, | |
DENSE_RANK() OVER (PARTITION BY build_repo_id ORDER BY build_id DESC) AS rank | |
FROM builds | |
) AS t | |
WHERE t.rank <= 1000 | |
) | |
DELETE FROM | |
builds | |
WHERE | |
builds.build_id NOT IN (SELECT build_id FROM n_build_ids_per_repo); |
Hey I just tried your sql file by using sqlite3 database.sqlite < delete.sql
but the database does not get any smaller. Even when I set the amount t.rank <= 10 or 1. Do you have any idea why?
sqlite version:
3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2
Hey I just tried your sql file by using
sqlite3 database.sqlite < delete.sql
but the database does not get any smaller. Even when I set the amount t.rank <= 10 or 1. Do you have any idea why?sqlite version:
3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2
Hi ntimo,
This script prevents your database file from getting larger only.
To reduce your database file size, try to use vacuum
command of sqlite.
The VACUUM command rebuilds the database file, repacking it into a minimal amount of disk space.
Hi, thanks for this!
Just tried your sql file with a t.rank <= 100
and then made a vacuum
on DB, but the database keeps with the same size (18GB). See edit
Did the vacuum command work for you? Could you reduce your db size?
Edit:
As I have seen, it only deleted selected builds but it didn't expand to other tables due to missing (defaulted to false) PRAGMA foreign_keys = ON;
. Setting on the top of the SQL worked as expected and, with a t.rank
<= 100 and vacuum
reduced DB from 18GB
to 2,1GB
Worked as expected!
Thanks,
Regards
Hi, thanks for this!
Just tried your sql file with at.rank <= 100
and then made avacuum
on DB,but the database keeps with the same size (18GB). See edit
Did the vacuum command work for you? Could you reduce your db size?Edit:
As I have seen, it only deleted selected builds but it didn't expand to other tables due to missing (defaulted to false)PRAGMA foreign_keys = ON;
. Setting on the top of the SQL worked as expected and, with at.rank
<= 100 andvacuum
reduced DB from18GB
to2,1GB
Worked as expected!
Thanks,
Regards
Hi sbengo,
Sorry for replying to you now. I didn't notice that PRAGMA foreign_keys
is defaulted to false.
So i thought sqlite will perform cascading delete
as normal relational database, but I was wrong.
Thank for your help. I will add it to top of script
Thanks,
Regards
Hello @msglight4874, I tried connecting to the shell of my Drone docker instance to run your script but wasnt able to.
any advice on which to run this script and how?
Do i run it on drone server or the runner and how do i connect to run this script. thanks in advance
Hello @msglight4874, I tried connecting to the shell of my Drone docker instance to run your script but wasnt able to.
any advice on which to run this script and how?
Do i run it on drone server or the runner and how do i connect to run this script. thanks in advance
Hi @carthur-fm,
I save this SQL query to a file, and write another shell script to run sql command on drone database by cron-job.
#!/bin/bash
set -e
VOLUME_PATH=$(docker volume inspect --format '{{ .Mountpoint }}' drone_data)
DATABASE_PATH="${VOLUME_PATH}/database.sqlite"
/usr/local/bin/sqlite3 $DATABASE_PATH < remove_old_builds.sql
FYI.
thanks a lot! and where do i use the vacuum
command ? add it to the cron job script?
Also i got the error, Error: No such volume: drone_data
. could it be that drone has make some renaming ?
Hi @carthur-fm ,
thanks a lot! and where do i use the
vacuum
command ? add it to the cron job script?
change drone_data
to your drone CI docker volume name.
Also i got the error, Error: No such volume: drone_data. could it be that drone has make some renaming ?
You can use vacuum
in sqlite CLI.
sqlite3 ./database.sqlite # to get into CLI
was able to make it work. vaccum; didnt seem to recliam the space. maybe a reboot might. thanks a lot @msglight4874
Silly question, but can this be done while Drone server is running, or should we stop it before truncating the table (Sqlite3)? I am assuming we do not, but I am not real familiar with how Drone handles Sqlite3's DB.
Hi @nikatjef ,
It can be done while the server is running. That's fine.
Nice solution.