I have a well formed table that I created using CSV import. Over time, I assume some bad data comes in, as I can no longer run
select * from mytable
This shows a red X in the web UI , but the column shows , and I can actually do something like
select * from mytable
limit 1,10
(and I get data). At some point if I play with the limit it will stop showing. I only have less than 50 rows.
I have checked to see if the table is suspected but that appears not to be the case. I checked the partitions and they are attached.
What I was hoping to do is export the table and review it to figure out what is wrong, but I don’t know the approach for that other than through the web gui.
Another approach might be to delete the row starting at the problematic row, but I am not clear what the right approach is in this case. I really don’t want to purge any data. I would like to clean it up and understand how to prevent this situation. I would appreciate any help as I like the database but don’t have a handle on these sorts of problems.
Please can you share server logs from when you run the query that returns a red ‘x’?
Also, what version of QuestDB are you using, and what is the schema of the table?
Sure I will pool the requested info in the early evening and post. And further details, it is an iphone or android app pushing data into the table, so it is possible there is some spotty data coming in.
@nwoolmer version is: 8.2.1
here is a snippet of the logs from my simple query:
025-02-04T06:48:32.251928Z I i.q.c.h.p.JsonQueryProcessorState [120259097109] timings [compiler: 536641, count: 0, execute: 4343239, q=`wal_tables()`]
2025-02-04T06:48:32.254430Z I i.q.g.e.QueryProgress exe [id=297, sql=`SHOW COLUMNS FROM 'halo1TmtRec_c';`, principal=admin, cache=false, jit=false]
2025-02-04T06:48:32.256143Z I i.q.g.e.QueryProgress fin [id=297, sql=`SHOW COLUMNS FROM 'halo1TmtRec_c';`, principal=admin, cache=false, jit=false, time=1734337]
2025-02-04T06:48:32.256168Z I i.q.c.h.p.JsonQueryProcessorState [747324322327] timings [compiler: 309134, count: 0, execute: 2193449, q=`SHOW COLUMNS FROM 'halo1TmtRec_c';`]
2025-02-04T06:48:38.046944Z I http-server disconnected [ip=..., fd=743029355030, src=queue]
2025-02-04T06:48:38.054013Z I http-server disconnected [ip=... fd=751619289624, src=queue]
2025-02-04T06:48:38.054228Z I http-server disconnected [ip=..., fd=120259097109, src=queue]
2025-02-04T06:48:38.060976Z I http-server disconnected [ip=..., fd=747324322327, src=queue]
Here is the schema dump:
id table_name designatedTimestamp partitionBy maxUncommittedRows o3MaxLag walEnabled directoryName dedup
10 halo1TmtRec_c timestamp DAY 500000 600000000 TRUE halo1TmtRec_c~10 FALSE
(NW EDIT: censor IPs)
Unfortunately, those logs do not contain anything obvious.
Please can you try using inspect on the browser and looking at the network traffic? When you send the query, I would expect you to get some sort of negative response - or a 200OK with some sort of error payload. That might hold some pertinent info.
It certainly sounds like you are hitting a segfault or similar that could return a 500ISE.
This was a good tip to try, didn’t occur to me. And we have a red flag. Internally we discussed that it was some problem with the data, but I wasn’t certain how to look since I can’t query. So the web UI is choking on something JSON. In my data in the response I found the culprint: “100000”
where the box is some control character.
So question: how can I remove that specific element? I think I can just do some sql upsert/update to fix the record in question. Let me experiment with that.
SyntaxError: JSON.parse: bad control character in string literal at line 1 column 1328 of the JSON data
32 requests
6.59 MB / 31.55 kB transferred
Finish: 9.84 s
DOMContentLoaded: 671 ms
load: 1.26 s
Unfortunately, we don’t have row-level delete. However, you could try nulling out this column using an UPDATEquery.
You could also remove this faulty row/characters from your dataset, drop the table, and reimport.