Connector to database stopped

[pistoletov1974]

I deploy few outboundSQL connectors. When my VM with database stopped for the backup procedure, connectors go into stopping state due to error database connection. I need to shutdown and start connectors manually to restore functionality. Can connectors restore working state automaticaly after errors?

[digikata]

Hi @pistoletov1974 to fully address this requires automatic reconnect logic and offset management. For the first, an issue was opened to port the logic from the http connector infinyon/sql-connector#116 This shouldn't take long to add and should solve your immediate problem. With this, database stoppages will leave the connector up and running and waiting for the database to come back up.

We are in the close to an update to add offset management api which is required for restoring progress after a shutdown. This is required for a fuller set of conditions for which working state can be restored.

[pistoletov1974]

@digikata now i resolve my problem with cron script, deploy - shutdown -start connector, but it is bad solution. What is offset management api? Ыtopping the connector is very bad because it can lead to data loss. Tell me, do you have any plans to solve this problem?

[digikata]

The offset management api is described here https://github.com/infinyon/fluvio/blob/master/rfc/offset-management.md, right now it's just on the finishing steps of the implementation. The core logic is in the mainline hidden behind a features while the full set of admin user cli/api implementation and extended testing is completed. Once that is released, a fluvio cluster will be able to keep the last offset consumed vs a consumer_id. If you connector is shut down or is disconnected, on restart it will resume working at the last synced offset.

[ajhunyady]

@digikata, if we implement the retry logic in the database connector, it should allow @pistoletov1974 to remove the cron job. However, there is still packet loss until the offset management is finished.

[ajhunyady]

@galibey, is there an ETA for when @pistoletov1974 can test the engineering build?

[galibey]

End of the next week

[pistoletov1974]

Is there any news about retry logic? And one more question - What is the best way to autostart fluvio on system start? I need few commands to start fluvio - start claster and after that start connectors ?

[digikata]

There is retry logic in the 0.4.0 sql sink connector release, and we have released offset management with our latest stable fluvio 0.11.6, next we plan to add offset management to the sql connector.

[ajhunyady]

pistoletov1974 All these issues have been addressed, hence ready for testing.

[pistoletov1974]

@ajhunyady I will test in few days and reply

[ajhunyady]

Link to the docs:

• https://www.fluvio.io/docs/fluvio/concepts/produce-consume#consumer-offsets

[pistoletov1974]

@ajhunyady @digikata sorry for the long time of silence I was busy with another project. I have updated fluvio and sql connector to versions respectively 0.12.0 and 0.4.3. Unfortunately, the problem remained after disconnecting from the database, the connector stops.
The last rows in connector log:

Error: error returned from database: closing the connection at the command of the administrator
Caused by:
                 closing the connection at the command of the administrator  

The similar situation arises with infinyon-mqtt-source-0.2.9. connector. The connector go to stopped state The end of log is:
2024-10-18T11:09:59.317491Z ERROR mqtt_source::source: Mqtt error Mqtt state: Io error: Custom { kind: ConnectionAborted, error: "connection closed by peer" }. Finishing mqtt loop

@ajhunyady the links in the post above do not open

[ajhunyady]

@pistoletov1974, we've migrated the docs to a new format. I've updated the link.

[pistoletov1974]

@ajhunyady @galibey
HI! I added an offset section to the yaml file, but still after a short shutdown of the database for backup, the connector stops.
This problem results in loss of data flow.
I noticed a difference in api versions. Where can I see the description of api versions
apiVersion: 0.2.0

`# sql.yml
apiVersion: 0.2.0
meta:
 name: sql-first-vodomat
 type: sql-sink
 version: 0.4.3
 topic:
   meta:
     name: test-telemetry
 create-topic: true
 consumer:
   id: my-sql-sink
   offset:
      strategy: auto
      start: beginning
      flush-period:
        secs: 200
        nanos: 0
sql:
   url: "postgres://fluvio:[email protected]/water"
transforms:
 - uses: infinyon/[email protected]
   with:
     invoke: insert
     mapping:
       table: "water_level"
       map-columns:
         "pressure_raw":
             json-key: "payload.pr_raw"
             value:
                type: "int"
                default: "0"
                required: true
         "temperature":
             json-key: "payload.t1"
             value:
                type: "float"
                default: "0"
                required: true
         "volume":
             json-key: "payload.w_vol"
             value:
                type: "int"
                default: "0"
                required: true

[fraidev]

Hi @pistoletov1974, the issue related to losing data of offset when shutdown the connector is fixed with sql-sink 0.4.4, can you test this version?