Table get deleted at service restart

Community
1

I tried to set the o3MaxLag parameter at 2s when setting the global O3MinLag at 10s … since then, affected tables get deleted automatically when I restart the service… If I recreate the table, whatever I set this parameter or not, it get deleted automatically.

The command was:
ALTER TABLE fpc_cbag0002_bis_sensor_stat SET PARAM o3MaxLag = 2s;
with config:
cairo.o3.min.lag=10000

I tried to reproduce the issue without success so far.

And the relevant corresponding lines in the log after restarting the service are:

Line   126: 2024-09-13T13:28:56.420040Z I i.q.c.m.EngineMigration upgrading [path=D:\qdbroot\db\fpc_cbag0002_bis_sensor_stat, fromVersion=426, toVersion=427]
	Line   127: 2024-09-13T13:28:56.420096Z I i.q.c.m.EngineMigration upgrading table [path=D:\qdbroot\db\fpc_cbag0002_bis_sensor_stat, toVersion=427]
	Line   472: 2024-09-13T13:28:58.700793Z I i.q.c.p.WriterPool open [table=`fpc_cbag0002_bis_sensor_stat`, thread=21]
	Line   474: 2024-09-13T13:28:58.700848Z I i.q.c.TableWriter open 'fpc_cbag0002_bis_sensor_stat'
	Line   495: 2024-09-13T13:28:58.727510Z E i.q.g.e.QueryProgress err [id=-1, sql=`INSERT INTO FPC_CBAG0002_bis_variable (timestamp, operation_name, operation_version, variable_name, variable_value)    select '2024-09-13T13:28:50.0000000', 'conveyor_average_current', 1,name, avg(value)    FROM  FPC_CBAG0002_bis_sensor_stat    where timestamp BETWEEN '2024-09-13T13:28:46.0000000' and '2024-09-13T13:28:50.0000000' and name = 'FPC_Conveyor_M1_Current'`, principal=admin, cache=false, jit=false, time=1148600, msg=[213] table does not exist [table=FPC_CBAG0002_bis_sensor_stat], errno=0, pos=213]
	Line   530: 2024-09-13T13:28:58.759991Z E i.q.g.e.QueryProgress err [id=-1, sql=`INSERT INTO FPC_CBAG0002_bis_variable (timestamp, operation_name, operation_version, variable_name, variable_value)    select '2024-09-13T13:28:50.0000000', 'conveyor_max_current', 1,name, max(value)    FROM  FPC_CBAG0002_bis_sensor_stat    where timestamp BETWEEN '2024-09-13T13:28:46.0000000' and '2024-09-13T13:28:50.0000000' and name = 'FPC_Conveyor_M1_Current'`, principal=admin, cache=false, jit=false, time=1129300, msg=[209] table does not exist [table=FPC_CBAG0002_bis_sensor_stat], errno=0, pos=209]
	Line   549: 2024-09-13T13:28:58.820484Z I i.q.c.TableWriter switched partition [path=\fpc_cbag0002_bis_sensor_stat\2024-09-13]
	Line   550: 2024-09-13T13:28:58.821467Z I i.q.c.p.WriterPool >> [table=`fpc_cbag0002_bis_sensor_stat`, thread=21]
	Line   551: 2024-09-13T13:28:58.821513Z I i.q.c.TableWriter closing table files [table=fpc_cbag0002_bis_sensor_stat, dirName=fpc_cbag0002_bis_sensor_stat]
	Line   553: 2024-09-13T13:28:58.822961Z I i.q.c.TableWriter closed 'fpc_cbag0002_bis_sensor_stat'
	Line   558: 2024-09-13T13:28:58.825206Z I i.q.c.p.WriterPool closed [table=`fpc_cbag0002_bis_sensor_stat`, reason=DISTRESSED, by=21]
	Line  9414: 2024-09-13T13:29:12.212501Z I i.q.c.w.WalPurgeJob table is fully dropped [tableDir=D:\qdbroot\db\fpc_cbag0002_bis_sensor_stat]

Any idea to recover from this?

And is there a way to force a longer O3 buffer on some tables without affecting the responsivity of the others ?

2

Here is a capture of the “presence” of the table before the restart:

image
image1439×109 60.5 KB

And it is also present on the disk. Why does it get deleted at service reboot ?

3

If I recreate them with WAL activated… the issue is solved.

4

Hi @Gabriel,

This sounds a little strange. However, some alterations to tables only apply on a database restart.

Are you in a good place by making them WAL, or do you need further assistance?

It would be great if you could open an issue with the reproduction steps: Issues · questdb/questdb · GitHub