Hi i m getting `max txn inflight limit reached txn=27503 min

Question

Hi i m getting `max txn inflight limit reached txn=27503 min=11119 size=16384 ` while query how can i fix it Thanks

Andrey Pechkurov · Answer

Hello What version are you using We have fixed a similar bug in one of the recent patch releases so you should try the latest release 6 5 3

Hà Hữu · Answer

Hi i ve fixed it but due update to latest version but after few mins questdb can t connect

Andrey Pechkurov · Answer

How does it look like Any errors in the server logs

Hà Hữu · Answer

yes when it happend i ll send log error to u

Andrey Pechkurov · Answer

+1

Hà Hữu · Answer

< Andrey Pechkurov> ```questdb 1 | 2022 10 06T06 46 13 498904Z C i q c p PGWireServer internal error ex= questdb 1 | java lang NullPointerException Cannot invoke io questdb griffin engine orderby RecordTreeChain$TreeCursor close because this chainCursor is null questdb 1 | at io questdb griffin engine orderby SortedRecordCursor close SortedRecordCursor java 44 questdb 1 | at io questdb griffin engine orderby SortedRecordCursorFactory close SortedRecordCursorFactory java 65 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb griffin engine LimitRecordCursorFactory close LimitRecordCursorFactory java 62 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb cutlass pgwire TypesAndSelect close TypesAndSelect java 42 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb std AssociativeCache put AssociativeCache java 137 questdb 1 | at io questdb cutlass pgwire PGConnectionContext clearCursorAndFactory PGConnectionContext java 1056 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewBatchQuery PGConnectionContext java 1707 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewQuery PGConnectionContext java 1718 questdb 1 | at io questdb cutlass pgwire PGConnectionContext parse PGConnectionContext java 1570 questdb 1 | at io questdb cutlass pgwire PGConnectionContext handleClientOperation PGConnectionContext java 415 questdb 1 | at io questdb cutlass pgwire PGJobContext handleClientOperation PGJobContext java 84 questdb 1 | at io questdb cutlass pgwire PGWireServer$1 lambda$$0 PGWireServer java 85 questdb 1 | at io questdb network AbstractIODispatcher processIOQueue AbstractIODispatcher java 181 questdb 1 | at io questdb cutlass pgwire PGWireServer$1 run PGWireServer java 112 questdb 1 | at io questdb mp Worker run Worker java 118 questdb 1 | ```

Hà Hữu · Answer

i got this in questdb

Hà Hữu · Answer

anyway to fix it now

Andrey Pechkurov · Answer

This is clearly a bug Could you create a GH issue with this error the query that you re running and your table schema <https github com questdb questdb issues new assignees= amp labels=bug amp template=bug report yaml>

Andrey Pechkurov · Answer

The error is related with ORDER BY being used in the query If possible you could try to remove ORDER BY from the query or re write the query so that the ORDER BY is on a different level this should help to avoid this error until we release the bugfix

Hà Hữu · Answer

I can t remove order by because i need to use limit

Hà Hữu · Answer

Another question is there anyway to make kafka will not sync duplicated to questdb i got too many duplicated data

Andrey Pechkurov · Answer

gt because i need to use limit then it may be possible to re write the query to work around the bug

Andrey Pechkurov · Answer

as for Kafka < Jaromir Hamala> might know better

Nicolas Hourcard · Answer

< Hà Hữu> I think you could be using Flink for avoiding duplicates Jaromir will confirm

Jaromir Hamala · Answer

Hello < Hà Hữu> are duplicates already present in Kafka topics or they are somehow created on the way between Kafka and QuestDB

Andrey Pechkurov · Answer

< Hà Hữu> could you share your table schema and the query I m working on a similar bugfix and I might fix your issue while I m on it

Hà Hữu · Answer

My table ```CREATE TABLE trades id INT market id STRING price DOUBLE amount DOUBLE total DOUBLE maker order id INT taker order id INT maker id INT taker id INT side STRING created at TIMESTAMP updated at TIMESTAMP TIMESTAMP created at PARTITION BY DAY ```

Hà Hữu · Answer

i ll send query soon

Hà Hữu · Answer

no problem about kafka sorry it s my bad

Jaromir Hamala · Answer

ok glad to hear that my ask what do you use to transfer data from Kafka to QuestDB Do you use Kafka Connect infrastructure or something you wrote on your own

Hà Hữu · Answer

i m using kafka connect

Jaromir Hamala · Answer

JDBC Connector

Hà Hữu · Answer

yes JDBC Sink

Hà Hữu · Answer

Debezium to JDBC

Hà Hữu · Answer

Hi < Andrey Pechkurov> i ve tested many query but working good just my app starting up then questdb got that problem

Hà Hữu · Answer

```SELECT FROM SELECT FIRST price AS open max price AS high min price AS low last price AS close sum amount AS volume created at as timestamp FROM trades WHERE market id = btcusdt AND created at gt dateadd m 60 systimestamp SAMPLE BY 60m FILL NULL NULL NULL NULL 0 ALIGN TO CALENDAR ORDER BY timestamp DESC LIMIT 0 1```

Hà Hữu · Answer

this is my full sql query

Hà Hữu · Answer

ok maybe i got it

Hà Hữu · Answer

```questdb 1 | 2022 10 06T11 18 41 962005Z I i q c p PGConnectionContext parse fd=8 q= questdb 1 | SELECT FROM questdb 1 | SELECT questdb 1 | FIRST price AS open questdb 1 | max price AS high questdb 1 | min price AS low questdb 1 | last price AS close questdb 1 | sum amount AS volume questdb 1 | created at as timestamp questdb 1 | FROM questdb 1 | trades questdb 1 | WHERE questdb 1 | market id = ltcusdt AND created at gt dateadd m 1440 systimestamp questdb 1 | SAMPLE BY 1440m questdb 1 | FILL NULL NULL NULL NULL 0 ALIGN TO CALENDAR questdb 1 | ORDER BY timestamp DESC LIMIT 1 0 questdb 1 | 2022 10 06T11 18 41 962020Z I i q c p PGConnectionContext query cache used fd=8 questdb 1 | 2022 10 06T11 18 41 962022Z I i q c p PGConnectionContext prepare name=lrupsc 31 4 questdb 1 | 2022 10 06T11 18 41 962099Z I i q c p PGConnectionContext query cache used fd=8 questdb 1 | 2022 10 06T11 18 41 962602Z I i q c p PGConnectionContext parse fd=8 q= questdb 1 | SELECT FROM questdb 1 | SELECT questdb 1 | FIRST price AS open questdb 1 | max price AS high questdb 1 | min price AS low questdb 1 | last price AS close questdb 1 | sum amount AS volume questdb 1 | created at as timestamp questdb 1 | FROM questdb 1 | trades questdb 1 | WHERE questdb 1 | market id = ltcusdt AND created at gt dateadd m 4320 systimestamp questdb 1 | SAMPLE BY 4320m questdb 1 | FILL NULL NULL NULL NULL 0 ALIGN TO CALENDAR questdb 1 | ORDER BY timestamp DESC LIMIT 1 0 questdb 1 | 2022 10 06T11 18 41 962827Z I i q g SqlCompiler plan q=`select choose open high low close volume timestamp from select group by FIRST price open max price high min price low last price close sum amount volume timestamp FIRST price open max price high min price low last price close sum amount volume timestamp from select choose price amount created at timestamp price amount created at timestamp from select price amount created at market id from trades timestamp created at where market id = ltcusdt and created at gt dateadd m 4320 systimestamp sample by 4320m fill NULL NULL NULL NULL 0 align to calendar with offset 00 00 order by timestamp desc limit 1 0` fd=8 questdb 1 | 2022 10 06T11 18 41 963100Z I i q c p PGConnectionContext prepare name=lrupsc 31 5 questdb 1 | 2022 10 06T11 18 41 963159Z C i q c p PGWireServer internal error ex= questdb 1 | java lang NullPointerException Cannot invoke io questdb griffin engine orderby RecordTreeChain$TreeCursor close because this chainCursor is null questdb 1 | at io questdb griffin engine orderby SortedRecordCursor close SortedRecordCursor java 44 questdb 1 | at io questdb griffin engine orderby SortedRecordCursorFactory close SortedRecordCursorFactory java 65 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb griffin engine LimitRecordCursorFactory close LimitRecordCursorFactory java 62 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb cutlass pgwire TypesAndSelect close TypesAndSelect java 42 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb std AssociativeCache put AssociativeCache java 137 questdb 1 | at io questdb cutlass pgwire PGConnectionContext clearCursorAndFactory PGConnectionContext java 1056 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewBatchQuery PGConnectionContext java 1707 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewQuery PGConnectionContext java 1718 questdb 1 | at io questdb cutlass pgwire PGConnectionContext parse PGConnectionContext java 1570 questdb 1 | at io questdb cutlass pgwire PGConnectionContext handleClientOperation PGConnectionContext java 415 questdb 1 | at io questdb cutlass pgwire PGJobContext handleClientOperation PGJobContext java 84 questdb 1 | at io questdb cutlass pgwire PGWireServer$1 lambda$$0 PGWireServer java 85 questdb 1 | at io questdb network AbstractIODispatcher processIOQueue AbstractIODispatcher java 181 questdb 1 | at io questdb cutlass pgwire PGWireServer$1 run PGWireServer java 112 questdb 1 | at io questdb mp Worker run Worker java 118 questdb 1 | questdb 1 | 2022 10 06T11 18 41 963210Z I pg server scheduling disconnect fd=8 reason=17 questdb 1 | 2022 10 06T11 18 41 963235Z I pg server disconnected ip=172 29 0 31 fd=8 src=queue questdb 1 | 2022 10 06T11 18 41 963323Z C server main unhandled error job=io questdb network IODispatcherLinux 3ce3db41 ex= questdb 1 | java lang NullPointerException Cannot invoke io questdb griffin engine orderby RecordTreeChain$TreeCursor close because this chainCursor is null questdb 1 | at io questdb griffin engine orderby SortedRecordCursor close SortedRecordCursor java 44 questdb 1 | at io questdb griffin engine orderby SortedRecordCursorFactory close SortedRecordCursorFactory java 65 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb griffin engine LimitRecordCursorFactory close LimitRecordCursorFactory java 62 questdb 1 | at io questdb cairo AbstractRecordCursorFactory close AbstractRecordCursorFactory java 50 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb cutlass pgwire TypesAndSelect close TypesAndSelect java 42 questdb 1 | at io questdb std Misc free Misc java 46 questdb 1 | at io questdb std AssociativeCache put AssociativeCache java 137 questdb 1 | at io questdb cutlass pgwire PGConnectionContext clearCursorAndFactory PGConnectionContext java 1056 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewBatchQuery PGConnectionContext java 1707 questdb 1 | at io questdb cutlass pgwire PGConnectionContext prepareForNewQuery PGConnectionContext java 1718 questdb 1 | at io questdb cutlass pgwire PGConnectionContext clear PGConnectionContext java 301 questdb 1 | at io questdb std WeakMutableObjectPool clear WeakMutableObjectPool java 54 questdb 1 | at io questdb std WeakMutableObjectPool clear WeakMutableObjectPool java 31 questdb 1 | at io questdb std WeakObjectPoolBase push WeakObjectPoolBase java 63 questdb 1 | at io questdb std WeakMutableObjectPool push WeakMutableObjectPool java 42 questdb 1 | at io questdb network MutableIOContextFactory done MutableIOContextFactory java 62 questdb 1 | at io questdb network MutableIOContextFactory done MutableIOContextFactory java 35 questdb 1 | at io questdb network AbstractIODispatcher doDisconnect AbstractIODispatcher java 289 questdb 1 | at io questdb network AbstractIODispatcher disconnectContext AbstractIODispatcher java 274 questdb 1 | at io questdb mp SCSequence consumeAll SCSequence java 73 questdb 1 | at io questdb network AbstractIODispatcher processDisconnects AbstractIODispatcher java 309 questdb 1 | at io questdb network IODispatcherLinux runSerially IODispatcherLinux java 119 questdb 1 | at io questdb mp SynchronizedJob run SynchronizedJob java 39 questdb 1 | at io questdb mp Worker run Worker java 118 questdb 1 | questdb 1 | 2022 10 06T11 19 05 834692Z I i q c h p StaticContentProcessor 372 incoming url= assets vs base worker workerMain js ```

Andrey Pechkurov · Answer

Thanks

Hà Hữu · Answer

may i need create issuse in github

Andrey Pechkurov · Answer

not really

Andrey Pechkurov · Answer

`dateadd m 60 systimestamp ` should be `dateadd m 60 systimestamp ` unless you have data from the future

Hà Hữu · Answer

hmmaybe i rwrite wrong but in my source code is neg number

Andrey Pechkurov · Answer

`dateadd m 60 systimestamp ` gives you the past timestamp of 60 minutes from the current time

Andrey Pechkurov · Answer

while `dateadd m 60 systimestamp ` is a future timestamp

Hà Hữu · Answer

yes number is past

Hà Hữu · Answer

i think it is error

Hà Hữu · Answer

Andrey Pechkurov · Answer

Your fix will be hopefully included into this PR <https github com questdb questdb pull 2598>

Andrey Pechkurov · Answer

Once it passes code review I can share a snapshot QuestDB version so that you can use it until we release the next patch version

Hà Hữu · Answer

yes thanks i ll check it every hours smiling face with tear

Hà Hữu · Answer

< Andrey Pechkurov> do you plan to add support limit amp order for SAMPLE BY query

Andrey Pechkurov · Answer

It s currently support via a sub query just like in the query you re running If you d like to see it at the same level as SAMPLE BY please submit a feature request <https github com questdb questdb issues new assignees= amp labels=New+feature amp template=feature request yml>

Hà Hữu · Answer

Hà Hữu · Answer

added it

Andrey Pechkurov · Answer

My bad didn t check the existing issues

Andrey Pechkurov · Answer

No nearest future plans AFAIK we re pretty busy with replication but we could add support in future

Jaromir Hamala · Answer

see <https github com questdb questdb pull 2572>

Jaromir Hamala · Answer

it s brewing hopefully the next release

Jaromir Hamala · Answer

it s a silly bug in SQL parser

Hà Hữu · Answer

i have one more request for example i have a table named `trades` with column price i can build kline from it using `SELECT FIRST price as open MIN price as low MAX price as high LAST price as close FROM trades SAMPLE BY 15m FILL PREV PREV PREV PREV ` but if in `1 hour I don t have any trades` some row will filled by PREV it s good but in kline PREV of open close high low must be FILL BY PREV of last if data is empty

Hà Hữu · Answer

i have talk with < Pei>

Andrey Pechkurov · Answer

Hopefully it resolves the bug for you

Pei · Answer

Hi < Hà Hữu> did the snapshot resolve your issue

Hà Hữu · Answer

Hi i ll try in today can i get the branch i ll make a docker image

Andrey Pechkurov · Answer

gt can i get the branch Not sure if I got the question Could you elaborate

Hà Hữu · Answer

Ahh no problem sorry

Hà Hữu · Answer

i ve try with questdb web console it s still work with same sql command

Hà Hữu · Answer

ahh problem by tasks max value 1 is no more problem

Jaromir Hamala · Answer

yeah that s right there cannot be parallel SQL inserts

Jaromir Hamala · Answer

would you be willing to try the ILP based Kafka Connector I can help you to set it up

Jaromir Hamala · Answer

with ILP you can have more than 1 kafka connect task

Hà Hữu · Answer

yes i m need it

Hà Hữu · Answer

can u share it for me yesterday i ve checked data very slowly

Jaromir Hamala · Answer

do you have the kafka project checkout if not then please do `git clone <https github com questdb kafka questdb connector git>`

Hà Hữu · Answer

do you have jar i m not a java developer

Jaromir Hamala · Answer

sure give me a second

Jaromir Hamala · Answer

I understood you are now using the Kafka Connect JDBC sink Do you know if you use Kafka Connect Distributed or Standalone mode

Hà Hữu · Answer

thanks i ll try it now

Hà Hữu · Answer

I m using confluent kafka connect amp redpanda

Jaromir Hamala · Answer

can you paste here your JDBC sink configuration or perhaps send it to my privately obviously with all secrets edited out

Jaromir Hamala · Answer

I can help to turn it into QuestDB Sink configuration

Hà Hữu · Answer

``` auto create false auto evolve false connection password quest connection url jdbc <postgresql questdb 8812 qdb useSSL=false> connection user admin connector class io confluent connect jdbc JdbcSinkConnector dialect name PostgreSqlDatabaseDialect insert mode insert key converter schemas enable true name questdb jdbc sink pk mode none tasks max 20 topics pg trades pg activities pg operations assets pg operations expenses pg operations liabilities pg operations revenues transforms unwrap dropPrefix addPrefix createdAtTimestampConverter updatedAtTimestampConverter transforms addPrefix regex transforms addPrefix replacement $0 transforms addPrefix type org apache kafka connect transforms RegexRouter transforms createdAtTimestampConverter field created at transforms createdAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms createdAtTimestampConverter target type string transforms createdAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value transforms dropPrefix regex pg transforms dropPrefix replacement $1 transforms dropPrefix type org apache kafka connect transforms RegexRouter transforms unwrap drop tombstones false transforms unwrap type io debezium transforms ExtractNewRecordState transforms updatedAtTimestampConverter field updated at transforms updatedAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms updatedAtTimestampConverter target type string transforms updatedAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value value converter schemas enable true ```

Hà Hữu · Answer

this is my config

Jaromir Hamala · Answer

please try this ``` host quest connector class io questdb kafka QuestDBSinkConnector key converter schemas enable true name questdb ilp sink tasks max 1 topics pg trades pg activities pg operations assets pg operations expenses pg operations liabilities pg operations revenues transforms unwrap dropPrefix addPrefix createdAtTimestampConverter updatedAtTimestampConverter transforms addPrefix regex transforms addPrefix replacement $0 transforms addPrefix type org apache kafka connect transforms RegexRouter transforms createdAtTimestampConverter field created at transforms createdAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms createdAtTimestampConverter target type string transforms createdAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value transforms dropPrefix regex pg transforms dropPrefix replacement $1 transforms dropPrefix type org apache kafka connect transforms RegexRouter transforms unwrap drop tombstones false transforms unwrap type io debezium transforms ExtractNewRecordState transforms updatedAtTimestampConverter field updated at transforms updatedAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms updatedAtTimestampConverter target type string transforms updatedAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value value converter schemas enable true ```

Hà Hữu · Answer

yes thanks i ll try now

Jaromir Hamala · Answer

are you running Kafka Connect inside a Docker container Do you know what Java version is used for Kafka Connect

Hà Hữu · Answer

not sure about this the image built by confluentinc

Jaromir Hamala · Answer

right what s the name of the image

Hà Hữu · Answer

confluentinc cp kafka connect 7 2 2

Hà Hữu · Answer

sink is good now i ll test sync

Jaromir Hamala · Answer

ok let me know I will add Debezium source into our testing pipeline and will write a guide

Hà Hữu · Answer

good

Hà Hữu · Answer

tada thanks u so much

Jaromir Hamala · Answer

is the record in QuestDB

Jaromir Hamala · Answer

because this log means Debezium source read a change in your Postgres database and send it to Kafka now the QuestDB ILP Sink has to send the record to QuestDB

Hà Hữu · Answer

yes i m try again

Hà Hữu · Answer

yea not found in questdb

Hà Hữu · Answer

neutral face

Hà Hữu · Answer

but i dont get any error

Jaromir Hamala · Answer

any error in the log can you try `curl lt kafka connect host gt 8083 connectors `

Hà Hữu · Answer

yes i ve saw the logs restart and try create new record no log

Jaromir Hamala · Answer

what s the output of `curl lt kafka connect host gt 8083 connectors`

Hà Hữu · Answer

wait me a sec

Jaromir Hamala · Answer

ok now please do `curl localhost 8083 connectors questdb ilp sink status`

Jaromir Hamala · Answer

ok this looks good no error indeed so the question is why it s not pushing anything to questdb

Jaromir Hamala · Answer

can you execute `show tables ` in QuestDB SQL Console

Hà Hữu · Answer

smiling face with tear really can t understand

Jaromir Hamala · Answer

are you sure there are no new records

Hà Hữu · Answer

yep i ve try with query id

Jaromir Hamala · Answer

can you please share questdb log

Hà Hữu · Answer

sure

Jaromir Hamala · Answer

this is ILP by default there is a commit lag it means records will be visible only after some time the lag is configurable

Jaromir Hamala · Answer

so maybe it s not visible yet but the questdb log should tell us more

Hà Hữu · Answer

Hi

Jaromir Hamala · Answer

anything in kafka connect log

Hà Hữu · Answer

let me check again

Hà Hữu · Answer

i m not sure but i ll send u everything i ve

Hà Hữu · Answer

after i create a record state of group is cchanged

Hà Hữu · Answer

Consumer group exists but does not have any members

Hà Hữu · Answer

got the error now

Hà Hữu · Answer

```org apache kafka connect errors ConnectException Exiting WorkerSinkTask due to unrecoverable exception at org apache kafka connect runtime WorkerSinkTask deliverMessages WorkerSinkTask java 611 at org apache kafka connect runtime WorkerSinkTask poll WorkerSinkTask java 333 at org apache kafka connect runtime WorkerSinkTask iteration WorkerSinkTask java 234 at org apache kafka connect runtime WorkerSinkTask execute WorkerSinkTask java 203 at org apache kafka connect runtime WorkerTask doRun WorkerTask java 188 at org apache kafka connect runtime WorkerTask run WorkerTask java 243 at java base java util concurrent Executors$RunnableAdapter call Executors java 515 at java base java util concurrent FutureTask run FutureTask java 264 at java base java util concurrent ThreadPoolExecutor runWorker ThreadPoolExecutor java 1128 at java base java util concurrent ThreadPoolExecutor$Worker run ThreadPoolExecutor java 628 at java base java lang Thread run Thread java 829 Caused by io questdb cutlass line LineSenderException 32 send error at io questdb cutlass line tcp PlainTcpLineChannel send PlainTcpLineChannel java 100 at io questdb cutlass line AbstractLineSender sendAll AbstractLineSender java 380 at io questdb cutlass line LineTcpSender flush LineTcpSender java 81 at io questdb kafka QuestDBSinkTask put QuestDBSinkTask java 52 at org apache kafka connect runtime WorkerSinkTask deliverMessages WorkerSinkTask java 581 10 more```

Jaromir Hamala · Answer

ok that s a progress slightly smiling face anything in questdb logs

Hà Hữu · Answer

2022 10 09T08 47 08 614492Z E i q c l t LineTcpConnectionContext 7 could not parse measurement NONE at 374 line may be mangled due to partial parsing activities key id=10484252i id=10484252i user id=5i category= user user ip= 58 186 164 249 user ip country= VN user agent= Mozilla 5 0 Windows NT 10 0 Win64 x64 AppleWebKit 537 36 KHTML like Gecko Chrome 104 0 0 0 Safari 537 36 topic= session action= login result= succeed device= fake created at= 2022 08 13 15 53 54 095 updated at= 2022 08 13 15 53 54 095

Hà Hữu · Answer

i think it s eror

Hà Hữu · Answer

eyes

Jaromir Hamala · Answer

anything above that

Hà Hữu · Answer

just that

Hà Hữu · Answer

```2022 10 09T08 47 08 614483Z E i q c l t LineTcpConnectionContext 7 could not process line data table=activities msg=cast error for line protocol string columnWriterIndex=13 columnType=TIMESTAMP errno=0 questdb 1 | 2022 10 09T08 47 08 614492Z E i q c l t LineTcpConnectionContext 7 could not parse measurement NONE at 374 line may be mangled due to partial parsing activities key id=10484252i id=10484252i user id=5i category= user user ip= 58 186 164 249 user ip country= VN user agent= Mozilla 5 0 Windows NT 10 0 Win64 x64 AppleWebKit 537 36 KHTML like Gecko Chrome 104 0 0 0 Safari 537 36 topic= session action= login result= succeed device= fake created at= 2022 08 13 15 53 54 095 updated at= 2022 08 13 15 53 54 095 questdb 1 | 2022 10 09T08 47 08 614504Z I tcp line server scheduling disconnect fd=7 reason=0 ```

Jaromir Hamala · Answer

what s the schema of your table

Jaromir Hamala · Answer

I think we are getting close I thnk the schema expected by the ILP connector is differents from your actual schema

Hà Hữu · Answer

hmm i dont see anything differents |

Jaromir Hamala · Answer

ok yes that s it the connectors sends columns `created at` amp `updated at` as strings even they should be timestamp that should be solvable via transforms

Hà Hữu · Answer

i need to make it to nano sec

Hà Hữu · Answer

got it

Hà Hữu · Answer

changed timestamp to Timestamp type

Jaromir Hamala · Answer

there will be multiple steps 1 you need to delete the existing connector you can do it via `curl X DELETE localhost 8083 connectors questdb ilp sink` 2 then submit it with a different configuration ``` host quest connector class io questdb kafka QuestDBSinkConnector key converter schemas enable true name questdb ilp sink tasks max 1 topics pg trades pg activities pg operations assets pg operations expenses pg operations liabilities pg operations revenues transforms unwrap dropPrefix addPrefix createdAtTimestampConverter updatedAtTimestampConverter transforms addPrefix regex transforms addPrefix replacement $0 transforms addPrefix type org apache kafka connect transforms RegexRouter transforms createdAtTimestampConverter field created at transforms createdAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms createdAtTimestampConverter target type unix transforms createdAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value transforms dropPrefix regex pg transforms dropPrefix replacement $1 transforms dropPrefix type org apache kafka connect transforms RegexRouter transforms unwrap drop tombstones false transforms unwrap type io debezium transforms ExtractNewRecordState transforms updatedAtTimestampConverter field updated at transforms updatedAtTimestampConverter format yyyy MM dd HH mm ss SSS transforms updatedAtTimestampConverter target type Timestamp transforms updatedAtTimestampConverter type org apache kafka connect transforms TimestampConverter$Value value converter schemas enable true timestamp field name created at ```

Hà Hữu · Answer

but the timestamp is 1970 smiling face with tear

Jaromir Hamala · Answer

we are almost there

Jaromir Hamala · Answer

If you include ` timestamp field name created at ` in connector configuration then the date should be OK

Hà Hữu · Answer

yea now work with your config

Hà Hữu · Answer

many thanks

Jaromir Hamala · Answer

woohooo

Hà Hữu · Answer

pray

Hà Hữu · Answer

i ll try with 20 task

Jaromir Hamala · Answer

+1

Jaromir Hamala · Answer

bear in mind ILP is much faster than JDBC so perhaps just 1 task is enough

Jaromir Hamala · Answer

20 definitely sounds too much

Jaromir Hamala · Answer

but feel free to experiement

Hà Hữu · Answer

< Jaromir Hamala> i ve a question

Jaromir Hamala · Answer

go ahead

Hà Hữu · Answer

questdb will create two row 1 created 1 update

Hà Hữu · Answer

neutral face

Hà Hữu · Answer

how i can slove that

Jaromir Hamala · Answer

ILP is append only you will have the complete history of all events you can LATEST ON to get the latest row

Jaromir Hamala · Answer

QuestDB is heavily optimized for appending news rows The ILP protocol reflect that SQL can do UPDATEs too but that s meant to be used exceptionally for example for fixing bad data As it s doing quite a lot of work and it s not fast

Hà Hữu · Answer

sometime i dont update anything but it still record neutral face

Jaromir Hamala · Answer

what do you mean can you elaborate it

Hà Hữu · Answer

i mean debezium still record a event i dont update anything

Hà Hữu · Answer

sorry my bad smile

Jaromir Hamala · Answer

Debezium is reading logs of your database It should not create record out of thin air hopefully slightly smiling face

Jaromir Hamala · Answer

may I ask what do you use as a source for Debezium is it Postgres Or some other DB

Hà Hữu · Answer

yyess it s Postgres

Hà Hữu · Answer

does debezium have config for insert event only

Jaromir Hamala · Answer

not sure let me check

Hà Hữu · Answer

maybe not

Hà Hữu · Answer

smiling face with tear

Jaromir Hamala · Answer

If there is no configuraiton in Debezium it should be possible to filter out UPDATEs via Kafka Connect Transforms

Jaromir Hamala · Answer

skimming this documentation <https debezium io documentation reference stable connectors postgresql html postgresql events>

Jaromir Hamala · Answer

can you paste here your Debezium Connector configuration

Hà Hữu · Answer

column exclude list public users password digest public users data connector class io debezium connector postgresql PostgresConnector database dbname barong production database hostname db database password zsmartex database server name pg database user zsmartex decimal handling mode double key converter schemas enable true name pg barong connector slot name barong connector snapshot mode never table include list public activities public api keys public profiles public attachments public users time precision mode connect transforms dropPrefix transforms dropPrefix regex pg public transforms dropPrefix replacement pg $1 transforms dropPrefix type org apache kafka connect transforms RegexRouter value converter schemas enable true

Hà Hữu · Answer

the unwarp transform always make it create one more face with spiral eyes

Jaromir Hamala · Answer

we can filter before the unwrap transform

Jaromir Hamala · Answer

we could actually filter on the source side so only INSERT events will make it to Kafka

Jaromir Hamala · Answer

this might be your best shot <https debezium io documentation reference stable transformations filtering html> apply on the the debezium side each message emitted by debezium has a field `op` ``` c = create u = update d = delete r = read applies to only snapshots t = truncate m = message``` so you would use filter to get only message where `op == c `

Jaromir Hamala · Answer

yeah something along these lines

Hà Hữu · Answer

i ll try it very soon

Hà Hữu · Answer

anyway thanks for your help raised hands

Jaromir Hamala · Answer

no problem at all I will explore it in parallel I m already working on adding a Debezim scenario into our testing pipeline

Jaromir Hamala · Answer

Hello < Hà Hữu> good news Debezium source has a configuration property `skipped operations` see and search for `skipped operations`

Jaromir Hamala · Answer

it should be enough to add this property to ``` column exclude list public users password digest public users data connector class io debezium connector postgresql PostgresConnector database dbname barong production database hostname db database password zsmartex database server name pg database user zsmartex decimal handling mode double key converter schemas enable true name pg barong connector slot name barong connector snapshot mode never table include list public activities public api keys public profiles public attachments public users time precision mode connect transforms dropPrefix transforms dropPrefix regex pg public transforms dropPrefix replacement pg $1 transforms dropPrefix type org apache kafka connect transforms RegexRouter value converter schemas enable true ``` something like ` skipped operations u d t `

Hà Hữu · Answer

Hi < Jaromir Hamala>

Hà Hữu · Answer

i m try to sync debezium to ILP questdb but i got an error about bigInterger

Hà Hữu · Answer

```org apache kafka connect errors ConnectException Exiting WorkerSinkTask due to unrecoverable exception at org apache kafka connect runtime WorkerSinkTask deliverMessages WorkerSinkTask java 611 at org apache kafka connect runtime WorkerSinkTask poll WorkerSinkTask java 333 at org apache kafka connect runtime WorkerSinkTask iteration WorkerSinkTask java 234 at org apache kafka connect runtime WorkerSinkTask execute WorkerSinkTask java 203 at org apache kafka connect runtime WorkerTask doRun WorkerTask java 188 at org apache kafka connect runtime WorkerTask run WorkerTask java 243 at java base java util concurrent Executors$RunnableAdapter call Executors java 515 at java base java util concurrent FutureTask run FutureTask java 264 at java base java util concurrent ThreadPoolExecutor runWorker ThreadPoolExecutor java 1128 at java base java util concurrent ThreadPoolExecutor$Worker run ThreadPoolExecutor java 628 at java base java lang Thread run Thread java 829 Caused by java lang NoClassDefFoundError sun misc FDBigInteger at io questdb std Numbers append Numbers java 2214 at io questdb std Numbers append Numbers java 255 at io questdb std Numbers append Numbers java 211 at io questdb std str CharSink put CharSink java 109 at io questdb cutlass line AbstractLineSender field AbstractLineSender java 145 at io questdb cutlass line AbstractLineSender doubleColumn AbstractLineSender java 151 at io questdb cutlass line AbstractLineSender doubleColumn AbstractLineSender java 46 at io questdb kafka QuestDBSinkTask tryWritePhysicalTypeFromSchema QuestDBSinkTask java 180 at io questdb kafka QuestDBSinkTask handleObject QuestDBSinkTask java 118 at io questdb kafka QuestDBSinkTask handleStruct QuestDBSinkTask java 87 at io questdb kafka QuestDBSinkTask tryWritePhysicalTypeFromSchema QuestDBSinkTask java 196 at io questdb kafka QuestDBSinkTask handleObject QuestDBSinkTask java 118 at io questdb kafka QuestDBSinkTask handleSingleRecord QuestDBSinkTask java 63 at io questdb kafka QuestDBSinkTask put QuestDBSinkTask java 50 at org apache kafka connect runtime WorkerSinkTask deliverMessages WorkerSinkTask java 581 10 more```

Jaromir Hamala · Answer

I see that s a known issue due Java version compatibility there is a workaround earlier today I sent you a zip with two JAR files 1 kafka questdb connector 0 1 SNAPSHOT jar 2 questdb 6 5 2 jdk8 jar you uploaded these 2 JAR files into Kafka Connect is that right

Hà Hữu · Answer

yes

Hà Hữu · Answer

that right

Jaromir Hamala · Answer

so delete questdb 6 5 2 jdk8 jar from your Kafka Connect and replace it with this <https repo1 maven org maven2 org questdb questdb 6 5 3 questdb 6 5 3 jar>

Jaromir Hamala · Answer

then it should work

Hà Hữu · Answer

i ll try it now

Hà Hữu · Answer

hmm i got another error

Hà Hữu · Answer

```questdb 1 | 2022 10 09T16 56 07 279604Z E i q c l t LineTcpConnectionContext 163 could not process line data table=operations liabilities msg=cast error for line protocol integer columnWriterIndex=3 columnType=STRING errno=0 questdb 1 | 2022 10 09T16 56 07 279613Z E i q c l t LineTcpConnectionContext 163 could not parse measurement NONE at 196 line may be mangled due to partial parsing operations liabilities key id=11i id=11i code=212i member id=1i currency id= bnb debit=0 015 credit=0 0 reference type= Withdraw reference id=12i updated at=1660746601670000t 1660746601670000000 ```

Jaromir Hamala · Answer

is there anything before this

Jaromir Hamala · Answer

what is the schema of this table

Hà Hữu · Answer

no nothing

Hà Hữu · Answer

just this error

Jaromir Hamala · Answer

you have `memberId` as string but it looks it s Integer in Kafka Postgres

Hà Hữu · Answer

OMG

Hà Hữu · Answer

it s my bad

Hà Hữu · Answer

face with spiral eyes

Jaromir Hamala · Answer

haha that I m glad it s not a problem with the connect slightly smiling face

Hà Hữu · Answer

ahh

Hà Hữu · Answer

why ILP insert a key id

Hà Hữu · Answer

neutral face

Jaromir Hamala · Answer

it s key from kafka message

Jaromir Hamala · Answer

would you like to filter it out it should be possible via Kafka Connect Transform setting the key to `null` should do it but perhaps it s a common use case so I can add explicit support for this to the Quest ILP connector

Hà Hữu · Answer

yes i think u should add it to connector but for now it s not a big problem for me

Hà Hữu · Answer

ILP very fast OMG face with spiral eyes

Jaromir Hamala · Answer

very cool the connector will get even faster over time I have done zero performance optimizations so far I know some things can be improved I ll do it after the basic functionality is there

Jaromir Hamala · Answer

here is a new version of the connect it supports a new config option `include key` when you set it to `false` then message keys won t be include in questdb tables see this for all options <https github com questdb kafka questdb connector configuration>

Hà Hữu · Answer

i ll try it in tomorrow

Hà Hữu · Answer

but i m getting problem

Hà Hữu · Answer

i know ilp very fast but why the consume group have some lag messagee

Hà Hữu · Answer

is there any batch size config

Jaromir Hamala · Answer

Is the lag decreasing over time

Hà Hữu · Answer

yes

Jaromir Hamala · Answer

Maybe there are out of order inserts Is questdb busy Anything about O3 in questdb log

Jaromir Hamala · Answer

Do you know if messages are kafka are out of order You might want to try just 1 task High level of parallelism in kafka might lead to our of order in questdb

Hà Hữu · Answer

ok let me try face with spiral eyes

Jaromir Hamala · Answer

Maybe one task per table topic would be best

Hà Hữu · Answer

hmm i ll try with one task on topic one table for now i m using one task but it still lag for my trading bot

Hà Hữu · Answer

smiling face with tear elasticsearch too

Hà Hữu · Answer

hmm maybe the hardware problem elasticsearch one topic one index with 3 tasks still like that

Hà Hữu · Answer

face with spiral eyes anyway thanks you for this day

Jaromir Hamala · Answer

You are very welcome

Jaromir Hamala · Answer

It helped me to understand requirements for ILP Connect sink better So thank you for that