Database cleanup

[boschkundendienst]

Hi,

I had problems uploading images but could fix it as described in Server crashes on upload to filesystem but what obviously happened was a server crash. I lost a document (only had a heading in it) and so it doesn't matter. But when I now Export user data I have some strange files in the archive like:

.md
-1.md
-2.md
-3.md
-4.md
-5.md
...and the rest of my files which look ok

The above files have 0 bytes.

I assume this happened due to the crash. My question is, how can I clean them out? - Any ideas.

Thanks in advance

Peter

[ccoenen]

These somehow made it into your database. I would guess that free url mode is configured, and that the missing images were requested by a browser, creating an empty document in the process. That document has no title or headline, so we have nothing to create a filename from.

So it's probably not directly caused by a crash.

[boschkundendienst]

These somehow made it into your database. I would guess that free url mode is configured, and that the missing images were requested by a browser, creating an empty document in the process. That document has no title or headline, so we have nothing to create a filename from.

So it's probably not directly caused by a crash.

Ok. Is there a simple way for me to cleanup these strange documents. I think I would be able to connect into the postgres container and fire up some sql commands there. Any suggestions?

Thanks in advance

[boschkundendienst]

Today I tried it to fix it myself and it seemed it worked:

identify container id of postgres container

pgid=$(docker ps -a | grep postgres:9.6-alpine | awk '{print $1}')

start bash inside postgres container

docker exec -it $pgid /bin/bash
bash-4.4#

connect to database

bash-4.4# psql -d hackmd hackmd
psql (9.6.13)
Type "help" for help.

hackmd=#

find Notes that are empty and have no title

hackmd=# select id from public."Notes" where ((title = '') IS TRUE) AND ((content = '') IS TRUE);

                  id
--------------------------------------
 57670c01-7d4c-4bec-bb20-99d2b950f6e0
 a3b03a5d-c490-41ff-85f4-c8b58d2127a8
 887decc9-0230-40ca-9364-d96f273c1989
 964e41e3-12d4-4985-b4ca-2358e8b4f949
 f2dd2041-0b72-40ed-bd86-83e437d6557d
(5 rows)

delete above notes by id

hackmd=# delete from public."Notes" where id = '57670c01-7d4c-4bec-bb20-99d2b950f6e0';
DELETE 1
hackmd=# delete from public."Notes" where id = 'a3b03a5d-c490-41ff-85f4-c8b58d2127a8';
DELETE 1
hackmd=# delete from public."Notes" where id = '887decc9-0230-40ca-9364-d96f273c1989';
DELETE 1
hackmd=# delete from public."Notes" where id = '964e41e3-12d4-4985-b4ca-2358e8b4f949';
DELETE 1
hackmd=# delete from public."Notes" where id = 'f2dd2041-0b72-40ed-bd86-83e437d6557d';
DELETE 1

verify by exporting user data

From the CodiMD Web interface choose Export user data and download archive.zip. Open archive.zip with an unzip utility and verify that all markdown files have names based on their title. The files like

.md
-1.md
-2.md
-3.md
-4.md
-5.md

should be gone!