Hi Quest DB Team i run into a little trouble with my Databas

Question

Hi Quest DB Team i run into a little trouble with my Database disappointed After a power loss of the server i can only aces the first 1 3 of my data some information s quest running at windows we use partitioned data same tables are corrupt timestamps binary 00 the main data table files seems ok check with hex viewer for content all column file sizes in the partition folders match in size seem ok datarange check is performed ba select from tab limit 10 her is see its only the first part of the data based on timestamp At this point i reached out for help In the past Tim Borowski was part of the company and fixed a problem with broken data He wrote a script to remove bad data in the binary files Now my question habe you any hint to fix this kind of problem If it made sense I m able to edit the binarys i m happy for all suggestions Benjamin

Nicolas Hourcard · Answer

Hi < Benjamin Böck> great to hear from you guys Does Datron use QDB in production

Nicolas Hourcard · Answer

we re going to look into this and help you

Benjamin Böck · Answer

Ask me how happy I m to her from you joy Production Yes a kind of We use it as bevor as a testing Equipment for high frequency measuring Data for HSC mill spindle test We now have on different DB instances nearly 40TB of data collected and it works like a charm Also the update on higher sampling rates 100kHz with 8 channels works without a flaw The main problem with this data is that s the base of a master thesis and the poor Student get a little shaky As i mentioned i m happy for all suggestions If you need more information i try to collect it THANKS

Nicolas Hourcard · Answer

thanks it would be great to keep a dialogue with the Datron team we are here to help

Alex Pelagenko · Answer

what QuestDB version you use

Benjamin Böck · Answer

First questdb 5 0 6 1 rt windows amd64 After this a tried a newer version questdb 6 5 5 rt windows amd64 No luck with booth of them But i make a copy of the original BD folder for the tests with the newer version

Alex Pelagenko · Answer

That s Feb 2021 release will be tricky to get the data restored

Alex Pelagenko · Answer

can you send the data how big is it

Benjamin Böck · Answer

Uncompressed 234 GB Wait i give compression a go and look how good it performs But it works with the newest version questdb 6 5 5 rt windows amd64 it gets up running like on the old one if this helps you

Alex Pelagenko · Answer

it s biggish disappointed

Benjamin Böck · Answer

It looks like the compressin get it down in the range of 24GB 1 10th

Alex Pelagenko · Answer

this is better

Alex Pelagenko · Answer

can you upload it say google drive or S3

Alex Pelagenko · Answer

you can send me a link in private

Benjamin Böck · Answer

then i try to find a solution to transfer it to you Ok google drive seems fine Yes i give it a try

Benjamin Böck · Answer

But it seems like 18h compression time disappointed

Benjamin Böck · Answer

Is there a way to reconstruct the DB from the column Files New header etc without the corrupt tables My may i can try this local Otherwise i can send you an Teamviewer Access so you can work on our system I think a man with hat and cigar is trusty slightly smiling face

Alex Pelagenko · Answer

no no simple way to reconstruct but perhaps it s possible need to look at the files

Alex Pelagenko · Answer

if you can figure out how many rows is in each partition I can perhaps guide you how to restore a table in latest QuestDB version

Benjamin Böck · Answer

Ok i think thats possible I take the aproach over the data size File Size divided by data size double 64Bit Timestamp 64Bit Then i can make a check for the rows

Alex Pelagenko · Answer

yes best it to check timestamp column in each partition

Alex Pelagenko · Answer

at some point in the file values become all 0s

Alex Pelagenko · Answer

you then take this offset of all 0s and divide by 8 8 bytes per timestamp

Alex Pelagenko · Answer

there is actually another way to do it

Alex Pelagenko · Answer

in new version if you can try to detach attach partitions one by one

Alex Pelagenko · Answer

and see if it makes the data visible

Benjamin Böck · Answer

You need it for all tables Or only for the tables we need to restore Ok give me 30 min and i check it all

Alex Pelagenko · Answer

only tables to restore

Alex Pelagenko · Answer

how many tables is there tables corrupted

Benjamin Böck · Answer

Ok we have 4 tables in the DB We only need one to restore acceleration there are 14 partitions in this table The last one ist the one with some missing timestamps We can drop the other tables these have massive missing timestamps

Benjamin Böck · Answer

I scan all the data and calculate the rows Also i search 00 timestamps with the hex editor and calculate the row File Size Byte Rows File size 8 Broken at hex offset Broken at row Filename 3656744960 457093120 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 05 timestamp d 3656744960 457093120 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 05 x1 d 3656744960 457093120 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 05 y1 d 3656744960 457093120 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 05 z1 d 1675843968 209480496 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 06 timestamp d 1675843968 209480496 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 06 x1 d 1675843968 209480496 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 06 y1 d 1675843968 209480496 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 06 z1 d 7110266184 888783273 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 07 timestamp d 7110266184 888783273 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 07 x1 d 7110266184 888783273 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 07 y1 d 7110266184 888783273 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 07 z1 d 13918865712 1739858214 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 08 timestamp d 13918865712 1739858214 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 08 x1 d 13918865712 1739858214 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 08 y1 d 13918865712 1739858214 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 08 z1 d 13690208256 1711276032 32FBDFFFF 1 710 735 359 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 21 timestamp d 13690208256 1711276032 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 21 x1 d 13690208256 1711276032 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 21 y1 d 13690208256 1711276032 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 21 z1 d 7179468800 897433600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 23 timestamp d 7179468800 897433600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 23 x1 d 7179468800 897433600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 23 y1 d 7179468800 897433600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 23 z1 d 1671659520 208957440 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 26 timestamp d 1671659520 208957440 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 26 x1 d 1671659520 208957440 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 26 y1 d 1671659520 208957440 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 26 z1 d 778341472 97292684 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 27 timestamp d 778341472 97292684 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 27 x1 d 778341472 97292684 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 27 y1 d 778341472 97292684 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 27 z1 d 891535360 111441920 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 28 timestamp d 891535360 111441920 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 28 x1 d 891535360 111441920 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 28 y1 d 891535360 111441920 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 28 z1 d 1053900800 131737600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 29 timestamp d 1053900800 131737600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 29 x1 d 1053900800 131737600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 29 y1 d 1053900800 131737600 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 29 z1 d 4260248984 532531123 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 30 timestamp d 4260248984 532531123 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 30 x1 d 4260248984 532531123 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 30 y1 d 4260248984 532531123 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 09 30 z1 d 1209466080 151183260 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 06 timestamp d 1209466080 151183260 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 06 x1 d 1209466080 151183260 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 06 y1 d 1209466080 151183260 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 06 z1 d 870317336 108789667 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 10 timestamp d 870317336 108789667 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 10 x1 d 870317336 108789667 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 10 y1 d 870317336 108789667 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 10 z1 d 4177526784 522190848 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 28 timestamp d 4177526784 522190848 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 28 x1 d 4177526784 522190848 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 28 y1 d 4177526784 522190848 F8000000 520 093 696 E Masterarbeit2022 EpNe Kopie 2 qdbroot db acceleration 2022 10 28 z1 d

Benjamin Böck · Answer

The Table is designed as following

Alex Pelagenko · Answer

when you query acceleration are some partitions missing Can you try to attach missing partitions

Benjamin Böck · Answer

Ok only for understanding attach means send to you I can send you the one with the missing values if this will be the sollution

Alex Pelagenko · Answer

Alex Pelagenko · Answer

this what I mean by ATTACH

Benjamin Böck · Answer

Fine thanks for clarification

Alex Pelagenko · Answer

it s supported in latest version

Alex Pelagenko · Answer

if partition needs a restore you run `alter table acceleration DETACH partition list 2022 10 28 ` then rename folder `2011 10 28 detached` into `2022 10 28 attachable` and run `alter table acceleration ATTACH partition list 2022 10 20 `

Benjamin Böck · Answer

Ok i read the manual and give it a try may a detach the corrupted partition and give it a try

Benjamin Böck · Answer

It doesn t work it seems to be broken earlier

Alex Pelagenko · Answer

ok skip detach step try to attach

Alex Pelagenko · Answer

rename the folder

Alex Pelagenko · Answer

and run attach

Benjamin Böck · Answer

Ok

Benjamin Böck · Answer

Now all the folders after the first broken partion are gone And no folders to rename are shown up anymore wink But i work on copy no trouble

Benjamin Böck · Answer

Hey now we are on the winning street A copy the partition back und reattached them = gt works like a charm tada The partition is added and the data range is expanded The next step is to reattach all other partitions I give feeback if it works

Alex Pelagenko · Answer

cool

Benjamin Böck · Answer

Ok know we are at the end of the journey I think you make some People real happy today Your Solution was the right point a was able to reconnect all the partitions and can aces all the data That s an example of superior help So many thanks from DATRON to QuestDB and special to you