Title
h
Henri Asseily
03/09/2023, 3:16 PMHello all. I have a question regarding qdb 7 not inserting data (although it returns ok) in a table, from any insertion technique including the console. I think it started right after an alter table to create a symbol column. Has anyone had this happen to them?
i
Imre
03/09/2023, 3:31 PMhi @Henri Asseily, what do you see if you run
wal_tables()is the table suspended?
h
Henri Asseily
03/09/2023, 3:32 PMYes! How can that happen?
i
Imre
03/09/2023, 3:32 PMthat means all data is the database in WAL
but not moved into the table because there was an error
needs to be resolved.
you can resume WAL apply
h
Henri Asseily
03/09/2023, 3:33 PMHmmm... Okay, so what kind of error suspends the wal? And how can I check that?
i
Imre
03/09/2023, 3:35 PMthis is the command to restart WAL apply to the table:
https://questdb.io/docs/reference/sql/alter-table-resume-wal/
h
Henri Asseily
03/09/2023, 3:35 PMLet me read the docs
Thank you very much!
i
Imre
03/09/2023, 3:35 PMhowever, you should check the log what was the error.
please, send the error to us, we will take a look
h
Henri Asseily
03/09/2023, 3:36 PMaha the wal can't be resumed. So the error is still there
i
Imre
03/09/2023, 3:37 PMcould you check the log, please?
try to find something around the time when the data stopped appearing in the table
h
Henri Asseily
03/09/2023, 3:37 PMok will do. I don't have ssh access to the machine right now so it'll take a bit of time
i
Imre
03/09/2023, 3:37 PMyou can grep for
EExceptionwill be here, whenever you have the logs
btw, have you tried to RESUME from a transaction number?
or just simply tried
ALTER TABLE weather_wal RESUME WAL;you probably have to skip the problematic transaction if the plain resume does not work
h
Henri Asseily
03/09/2023, 3:42 PMJust tried without anything. I am interested in the error before having the table work again
i
Imre
03/09/2023, 3:43 PMok, if you can wait.
h
Henri Asseily
03/09/2023, 3:43 PMHow can I check the problematic tran, is it possible directly?
i
Imre
03/09/2023, 3:45 PMwal_tables()h
Henri Asseily
03/09/2023, 3:46 PMGood. I have the writerTxn for the last successful transaction. I'll figure out the logs.
Okay let's see. When trying to vacuum the problematic table (after having added a symbol column), I get this:
i.q.c.VacuumColumnVersions skipping column version purge VACUUM, invalid partition directory name [name=wal307, path=/var/lib/questdb/db/event~15]
As far as I can tell, when I added the column, the WALWriter started work and said
TableUtils locked '/var/lib/questdb/db/event~15/wal292.lock' [fd=183]
Which looks like it's normal behavior, and added the column.
i.q.c.TableWriter ADDED column 'vendorcode[SYMBOL], columnName txn 2805373 to /var/lib/questdb/db/event~15
i.q.c.p.WriterPool << [table=
event~15Later on, on the first insert, I get this:
i.q.c.w.ApplyWal2TableJob unsolicited table lock [table=event~15, lock_reason=unknown]
And finally, this:
C i.q.c.w.ApplyWal2TableJob WAL apply job failed, table suspended [table=event~15, error=unexpected new WAL structure version [walStructure=1, tableStructureVersion=1], errno=0]
v
Vlad
03/10/2023, 9:26 AM@Alex Pelagenko could you have a look?
a
Alex Pelagenko
03/10/2023, 9:31 AMHey Henri, can you post the log please?
h
Henri Asseily
03/10/2023, 9:32 AMLet me get the relevant parts.
a
Alex Pelagenko
03/10/2023, 9:32 AMaround the time you added the column
v
Vlad
03/10/2023, 9:35 AMFull unaltered log would be perfect if possible
Every log line matters
h
Henri Asseily
03/10/2023, 9:48 AMSent via pm. Thanks!
a
Alex Pelagenko
03/10/2023, 9:50 AMcan you send to me please?
h
Henri Asseily
03/10/2023, 9:51 AMsent
a
Alex Pelagenko
03/10/2023, 10:01 AMOk, I see you were unhappy to trip over a bug with adding column. The column is added to the table but the WAL transaction is not market as processed and then we try to process it again it fails on the checks (because the column is already added). The but is fixed but the fix is not released yet. In this case it’s safe to skip the transaction with resume SQL
h
Henri Asseily
03/10/2023, 10:02 AMI thought it was something like that. Thanks for confirming!
And thank you for the quick turnaround
i
Imre
03/10/2023, 10:04 AM@Henri Asseily, please, ping me if you need help with the RESUME command
h
Henri Asseily
03/10/2023, 10:05 AMI'll try to resume after the problematic transaction, but if that's the column insert, then won't the other transactions fail?
i
Imre
03/10/2023, 10:05 AMthe problem was the ALTER TABLE ADD COLUMN, INSERT should be ok
a
Alex Pelagenko
03/10/2023, 10:06 AMif you select from the table now, I expect it to have the column
i
Imre
03/10/2023, 10:06 AMsorry, misunderstood your comment
h
Henri Asseily
03/10/2023, 10:06 AMYes the selects do have the column.
i
Imre
03/10/2023, 10:06 AMyou should resume from the txn after the add column
h
Henri Asseily
03/10/2023, 10:06 AMok
a
Alex Pelagenko
03/10/2023, 10:06 AMSo column is added, but it tries to add it again because transaction is not marked as completed
i
Imre
03/10/2023, 10:07 AMsomething like (txn number can be taken from the result of the
wal_tables()ALTER TABLE weather_wal RESUME WAL TXN 5;a
Alex Pelagenko
03/10/2023, 10:07 AMresume will force mark column add transaction as completed
h
Henri Asseily
03/10/2023, 10:08 AMNope it doesn't work. It gives an error
i
Imre
03/10/2023, 10:08 AMtry the next txn
+1 from what was displayed
h
Henri Asseily
03/10/2023, 10:09 AMTried the subsequent 3
Ah wait.
the RESUME returned an error, but it's actually working. When checking wal_tables() it is increasing the index. And the log is saying processing wal
i
Imre
03/10/2023, 10:12 AMthat txn might not have been existed.
what was the error for the RESUME?
h
Henri Asseily
03/10/2023, 10:14 AMOne sec, will send you guys the new logs. You'll find it interesting
a
Alex Pelagenko
03/10/2023, 10:25 AM