Title
#users-public
b

Benjamin Böck

11/10/2022, 9:54 AM
Hi Quest DB Team, i run into a little trouble with my Database 😞 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

Nicolas Hourcard

11/10/2022, 10:49 AM
Hi @Benjamin Böck, great to hear from you guys. Does Datron use QDB in production?
10:51 AM
we’re going to look into this and help you
b

Benjamin Böck

11/10/2022, 10:59 AM
Ask me how happy I'm to her from you 😂 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

Nicolas Hourcard

11/10/2022, 11:17 AM
thanks - it would be great to keep a dialogue with the Datron team, we are here to help
Alex Pelagenko

Alex Pelagenko

11/10/2022, 12:12 PM
what QuestDB version you use?
b

Benjamin Böck

11/10/2022, 12:21 PM
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

Alex Pelagenko

11/10/2022, 12:32 PM
That's Feb 2021 release, will be tricky to get the data restored
12:33 PM
can you send the data, how big is it?
b

Benjamin Böck

11/10/2022, 12:36 PM
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

Alex Pelagenko

11/10/2022, 12:38 PM
it's biggish 😞
b

Benjamin Böck

11/10/2022, 12:39 PM
It looks like the compressin get it down in the range of 24GB (1/10th)
Alex Pelagenko

Alex Pelagenko

11/10/2022, 12:39 PM
this is better
12:39 PM
can you upload it? say google drive or S3
12:39 PM
you can send me a link in private
b

Benjamin Böck

11/10/2022, 12:40 PM
then i try to find a solution to transfer it to you... Ok google drive seems fine .... Yes i give it a try...
12:41 PM
But it seems like 18h compression time 😞
12:57 PM
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 🙂 )
Alex Pelagenko

Alex Pelagenko

11/10/2022, 1:08 PM
no, no simple way to reconstruct, but perhaps it's possible, need to look at the files
1:09 PM
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
b

Benjamin Böck

11/10/2022, 1:20 PM
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

Alex Pelagenko

11/10/2022, 1:27 PM
yes, best it to check timestamp column in each partition
1:28 PM
at some point in the file values become all 0s
1:28 PM
you then take this offset of all 0s and divide by 8 (8 bytes per timestamp)
1:29 PM
there is actually another way to do it
1:29 PM
in new version if you can try to detach / attach partitions one by one
1:30 PM
and see if it makes the data visible
b

Benjamin Böck

11/10/2022, 1:30 PM
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

Alex Pelagenko

11/10/2022, 1:31 PM
only tables to restore
1:31 PM
how many tables is there / tables corrupted
b

Benjamin Böck

11/10/2022, 1:36 PM
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 )
2:00 PM
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
2:02 PM
The Table is designed as following:
Alex Pelagenko

Alex Pelagenko

11/10/2022, 2:03 PM
when you query acceleration are some partitions missing? Can you try to attach missing partitions?
b

Benjamin Böck

11/10/2022, 2:05 PM
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
2:06 PM
this what I mean by “ATTACH”
b

Benjamin Böck

11/10/2022, 2:06 PM
Fine thanks for clarification!
Alex Pelagenko

Alex Pelagenko

11/10/2022, 2:07 PM
it’s supported in latest version
2:09 PM
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'
b

Benjamin Böck

11/10/2022, 2:10 PM
Ok i read the manual and give it a try.. may a detach the corrupted partition? and give it a try?
2:12 PM
It doesn't work... it seems to be broken earlier..
Alex Pelagenko

Alex Pelagenko

11/10/2022, 2:13 PM
ok, skip detach step, try to attach
2:13 PM
rename the folder
2:13 PM
and run attach
b

Benjamin Böck

11/10/2022, 2:13 PM
Ok
2:17 PM
Now all the folders after the first broken partion are gone.... And no folders to rename are shown up anymore 😉 (But i work on copy no trouble )
2:27 PM
Hey now we are on the winning street.... A copy the partition back und reattached them => works like a charm!!!🎉 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

Alex Pelagenko

11/10/2022, 2:28 PM
cool
b

Benjamin Böck

11/10/2022, 2:48 PM
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!