Used_serial database goes from 69MB to 0.3MB after rebuilding. What can i do?

I decided to double check all of my databases for corruption. All of them verified “ok” yesterday, but I am not getting any downloads so I checked again today. All checked out OK except used_serials gives the following:

PS C:\Users\Administrator> docker run --rm -it --mount type=bind,source=v:\storagenode\storage\used_serial.db,destination=/used_serial.db sstc/sqlite3 sqlite3 /used_serial.db “PRAGMA integrity_check;”
*** in database main ***
Page 16352: btreeInitPage() returns error code 11
On tree page 7343 cell 43: Rowid 324254 out of order
On tree page 7331 cell 43: Rowid 323720 out of order
Page 15989: btreeInitPage() returns error code 11
On tree page 7425 cell 21: Child page depth differs
On tree page 7425 cell 20: Child page depth differs
Page 16255: btreeInitPage() returns error code 11
Page 16688: btreeInitPage() returns error code 11
Page 17113: btreeInitPage() returns error code 11
On tree page 7395 cell 170: Child page depth differs
On tree page 7395 cell 169: Rowid 327792 out of order
On tree page 7395 cell 169: Child page depth differs
On tree page 7395 cell 97: 2nd reference to page 7351
On tree page 7395 cell 96: 2nd reference to page 7350
On tree page 7395 cell 95: 2nd reference to page 7349
On tree page 7395 cell 94: 2nd reference to page 7348
On tree page 7395 cell 93: 2nd reference to page 7347
On tree page 7395 cell 92: 2nd reference to page 7346
On tree page 7395 cell 91: 2nd reference to page 7345
On tree page 7395 cell 90: 2nd reference to page 7344
On tree page 7395 cell 89: 2nd reference to page 7343
On tree page 7395 cell 88: 2nd reference to page 7342
On tree page 7395 cell 87: 2nd reference to page 7341
On tree page 7395 cell 86: 2nd reference to page 7340
On tree page 7395 cell 85: 2nd reference to page 7339
On tree page 7395 cell 84: 2nd reference to page 7338
On tree page 7395 cell 83: 2nd reference to page 7337
On tree page 7395 cell 82: 2nd reference to page 7336
On tree page 7395 cell 81: 2nd reference to page 7335
On tree page 7395 cell 80: 2nd reference to page 7334
On tree page 7395 cell 79: 2nd reference to page 7333
On tree page 7395 cell 77: 2nd reference to page 7331
On tree page 7395 cell 76: 2nd reference to page 7330
On tree page 7395 cell 75: 2nd reference to page 7329
On tree page 7395 cell 74: 2nd reference to page 7328
On tree page 7395 cell 73: 2nd reference to page 7327
On tree page 7395 cell 72: 2nd reference to page 7326
On tree page 7395 cell 71: 2nd reference to page 7325
On tree page 7395 cell 70: 2nd reference to page 7324
On tree page 7395 cell 69: 2nd reference to page 7323
On tree page 7395 cell 68: 2nd reference to page 7322
On tree page 7395 cell 67: 2nd reference to page 7321
On tree page 7395 cell 66: 2nd reference to page 7320
On tree page 7395 cell 65: 2nd reference to page 7319
On tree page 7395 cell 64: 2nd reference to page 7318
On tree page 7395 cell 63: 2nd reference to page 7317
On tree page 7395 cell 62: 2nd reference to page 7316
On tree page 7395 cell 61: 2nd reference to page 7315
On tree page 7067 cell 44: Rowid 312032 out of order
Page 16776: btreeInitPage() returns error code 11
Page 16505: btreeInitPage() returns error code 11
On tree page 7059 cell 43: Rowid 311678 out of order
Page 16358: btreeInitPage() returns error code 11
On tree page 7054 cell 43: Rowid 311457 out of order
Page 16313: btreeInitPage() returns error code 11
On tree page 6996 cell 312: Child page depth differs
On tree page 6996 cell 311: Rowid 315845 out of order
On tree page 6996 cell 311: Child page depth differs
On tree page 6996 cell 233: 2nd reference to page 7075
On tree page 6996 cell 232: 2nd reference to page 7074
On tree page 6996 cell 231: 2nd reference to page 7073
On tree page 6996 cell 230: 2nd reference to page 7072
On tree page 6996 cell 229: 2nd reference to page 7071
On tree page 6996 cell 228: 2nd reference to page 7070
On tree page 6996 cell 227: 2nd reference to page 7069
On tree page 6996 cell 225: 2nd reference to page 7067
On tree page 6996 cell 224: 2nd reference to page 7066
On tree page 6996 cell 223: 2nd reference to page 7065
On tree page 6996 cell 222: 2nd reference to page 7064
On tree page 6996 cell 221: 2nd reference to page 7063
On tree page 6996 cell 220: 2nd reference to page 7062
On tree page 6996 cell 219: 2nd reference to page 7061
On tree page 6996 cell 218: 2nd reference to page 7060
On tree page 6996 cell 217: 2nd reference to page 7059
On tree page 6996 cell 216: 2nd reference to page 7058
On tree page 6996 cell 215: 2nd reference to page 7057
On tree page 6996 cell 214: 2nd reference to page 7056
On tree page 6996 cell 213: 2nd reference to page 7055
On tree page 6996 cell 212: 2nd reference to page 7054
On tree page 6996 cell 211: 2nd reference to page 7053
On tree page 6996 cell 210: 2nd reference to page 7052
On tree page 6996 cell 209: 2nd reference to page 7051
On tree page 6996 cell 208: 2nd reference to page 7050
On tree page 6996 cell 207: 2nd reference to page 7049
On tree page 6996 cell 206: 2nd reference to page 7048
On tree page 6996 cell 205: 2nd reference to page 7047
On tree page 6996 cell 204: 2nd reference to page 7046
On tree page 6996 cell 203: 2nd reference to page 7045
On tree page 6996 cell 202: 2nd reference to page 7044
Page 16492: btreeInitPage() returns error code 11
Page 16138: btreeInitPage() returns error code 11
Page 16624: btreeInitPage() returns error code 11
Page 16402: btreeInitPage() returns error code 11
On tree page 6996 cell 177: Child page depth differs
On tree page 6996 cell 176: Rowid 309866 out of order
On tree page 6996 cell 176: Child page depth differs
On tree page 6996 cell 137: 2nd reference to page 6978
On tree page 6996 cell 136: 2nd reference to page 6977
On tree page 6996 cell 135: 2nd reference to page 6976
On tree page 6996 cell 134: 2nd reference to page 6975

I am not getting any errors in my logs. Here is my attempt to rebuild/fix the database…which brings the database down to 300kb.

Any advice?

Thank you!

P.S. I have a copy of the used_serial.db from 24 hours ago that passed the integrity check. I don’t know if that can be used or may help.

You won’t get any downloads (ingress) if your node is full.

Thank you, but my node still has 750GB free space.

Bump for help… perhaps admins did not see my previous edit.

“P.S. I have a copy of the used_serial.db from 24 hours ago that passed the integrity check. I don’t know if that can be used or may help.”

What does the used_serial.db do/how does it work? Can I use a database from over 24hrs ago?

If I remember correctly it keeps track of serials used for bandwidth contracts. Your node should be able to run even if it’s empty. The only reason it is there is to prevent uplinks from reusing the same one.

Missing the data is not a big deal. However, it’s likely you’re not missing anything. The sqlite databases are never vacuumed to remove unused space. As a result deleted data basically sticks around in the database. After the procedure you did it would effectively clean up all deleted data. In all likelihood all data that should be there is still there. If you don’t have any errors, you’re fine.

What does that mean in the long run? I am wondering because I had a database issue and needed to rebuild orders.db. It took awfully long and after that it was only half of the size.
So I am wondering how big can all those databases become if they don’t get maintained with all those frequent database operations?

Just found this here: How SQLite VACUUM Optimizes Your Database

Which is basically saying it keeps growing and growing.

1 Like

Yep, that’s pretty much it. I brought this up a while back, but vacuuming can take some time depending on the system and so far it’s not implemented. I guess it could be done during database migration for a new version. There is no need to do it very often as databases don’t get all that large.

Update: I replaced my used_serials.db with the one from 2 days ago (that was not corrupt) and I am now seeing “downloaded” in the logs and not just “download starting”.

So it looks like the database was causing an issue.