Logstash jdbc plugin, connection timed out

[sahere37 (LR)]

hi all,
I am using logstash jdbc plugin to load data from database, my jdbc conf is as following:

input {
jdbc {
    jdbc_driver_library => "sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://localhost:3333;databaseName=Total"
    jdbc_user => "****"
    jdbc_password => "****"
	schedule => "15 11-23/1 * * *"
	last_run_metadata_path => "E:\logstashlog\.my_last_run"
    statement => "
	DECLARE @DDate1  CHAR(10)
   select @DDate1=REPLACE(MAXpDate),'/','') from Total.dbo.Total_Control
   select [BaseDate_Accept_Setel]
     from dbo.vw_Total where (BaseDate_Accept_Setel <= @DDate1) and (BaseDate_Accept_Setel > :sql_last_value) 
	"
use_column_value => "true"
tracking_column => "basedate_accept_setel"
}

}

after some hours following error has been found in the logstash log

 [2019-03-14T17:17:05,619][ERROR][logstash.inputs.jdbc     ] Unable 
 to connect to database. Tried 1 times{:error_message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: The TCP/IP connection to the host localhost, port 3333 has failed. Error: \"Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.\"."}`

and after that, it seems there is no try in order to connect to database.
how can i handle this issue? i want to logstash try to connect even after a connection failed

[hermann102]

Hello sahere37

The error message is clear though. Parameter (Port,host ...) connection are wrong.
One or more of these parameters are to be modified.

Or if not, check if you do not have a firewall. If it does, check that it allows your port.

[sahere37 (LR)]

hello hermann, many thanks for your reply,
every mentioned issue was OK when I checked the logstash output, but logstash didn't try to connect to database. I think for a period of time connection failed, but it is expected to logstash reconnect after that period of time. how can i set jdbc plugin which after problem handling, automatically connect to database? i find "connection_retry_attempts" and "connection_retry_attempts_wait_time"; what are the best amount for these two filed? I am using following schedule parameter:

  schedule => "30 9-23/1 * * *"

[hermann102]

In principle, your JDBC connector should run every time your system date matches your schedule.

If for a given date corresponding to your schedule, the connection fails, normally at the next corresponding date (Day +1), the connector must still run and try a new connection.

connection_retry_attempts = Maximum number of times to try connecting to database

connection_retry_attempts_wait_time = Number of seconds to sleep between connections

Unless I am mistaken, these parameters are only valid during the period corresponding to your schedule. For good values, I'm not sure I have the right answers. But you can already see what your Database supports in terms of connection.

Use this schedule,normally you must have a connection attempt every minute.

schedule ='' * * * * * ''

[sahere37 (LR)]

many thanks for your reply, so the logstash should attempt to connect to db according schedule even if in one attempt connection faile it should do it again based on the schedule?
notably, i cannot do it as "* * * * *" due to load on db side

[hermann102]

schedule ='' * * * * * ''

This is just for testing on a short schedule. To better see the behavior.

[hermann102]

Exactly

so the logstash should attempt to connect to db according schedule even if in one attempt connection faile it should do it again based on the schedule?

[sahere37 (LR)]

many thanks