Update the cursor manually breaks my connection

Connector Questions & Issues
1
  • Is this your first time deploying Airbyte: No
  • OS Version / Instance: Ubuntu 20.04 EC2 t3.xlarge
  • Memory / Disk: 16Gb / 100GB EBS
  • Deployment: Docker
  • Airbyte Version: 0.35.60-alpha
  • Source name/version: Oracle DB (0.3.14)
  • Destination name/version: Redshift (0.3.28)
  • Step: On sync with cursor change manually
  • Description: I have incremental replication in my connection. Sometimes I want to change the cursor to make a reprocess. I make the follow steps to change the cursor value.

I enter to the airbyte db with the following.
docker exec -ti airbyte-db psql -U docker airbyte

Then I execute the following query:
UPDATE state SET state = jsonb_set(state, '{state, streams, 0, cursor}', '"NEW CURSOR"', false) WHERE connection_id = 'CONNECTION ID';

When I run my sync again it fails.
ERROR i.a.i.d.j.c.CopyConsumerFactory(closeAsOneTransaction):149 - Failed to finalize copy to temp table due to: java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near "null"

I create a new connection identical and it works perfectly. I want to perform a good change of cursor value, it would help a lot to the behavior of my data.

logs-844.txt (91.2 KB)

2

Hello @boggdan,
Could you please share the value of your state before and after your update?

4

Hi @alafanechere

I updated for something like this.

UPDATE state SET state = jsonb_set(state, '{state, streams, 0, cursor}', '"2022-01-25T00:00:00Z"', false) WHERE connection_id = '592fd59f-2aa9-4852-9d2b-8d12e269faa1';

5

Could you please share what was the value before the update too?

6

Hi @alafanechere this is the original cursor.

2022-03-16 03:58:16 source > 2022-03-16 03:58:16 INFO i.a.i.s.r.StateManager(createCursorInfoForStream):108 - Found matching cursor in state. Stream: AirbyteStreamNameNamespacePair{name=‘SAP_V_COMPRAS’, namespace=‘EREA’}. Cursor Field: COM_FECHA Value: 2022-03-02T00:00:00.000000Z

7

It would be better if you could share the output of the SELECT query before UPDATE. But according to your logs I think that one problem could be that the expected value for the cursor has a different time precision compared to the one you have set manually.
Can you try:

UPDATE state SET state = jsonb_set(state, '{state, streams, 0, cursor}', '"2022-01-25T00:00:00.000000Z"', false) WHERE connection_id = '592fd59f-2aa9-4852-9d2b-8d12e269faa1';
1 Like
8

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.