# Logstash with Docker ( Unable to connect to database )

**URL:** <https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072>\
**Category:** Logstash\
**Tags:** docker\
**Created:** [April 9, 2024, 1:11pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072 "2024-04-09T13:11:12Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kaique292](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kaique292/32/133388_2.png) [@Kaique292](https://discuss.elastic.co/u/Kaique292)\
**Post date:** [April 9, 2024, 1:11pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/1 "2024-04-09T13:11:12Z")

</div>

I've already changed the driver, I've already checked the connection string for a possible error, but apparently everything is fine. I am trying to remotely connect to a SQL Server database through JDBC via Logstash running on Docker. And I'm getting the following error, I hid the real IP

```auto
Unable to connect to database. Tried 3 times {:message=>"Java::ComMicrosoftSqlserverJdbc::SQLServerException: The TCP/IP connection to the host IP_HIDDEN, port 1433 has failed. Error: \"Connect timed out. 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.\".", :exception=>Sequel::DatabaseConnectionError, :cause=>#<Java::ComMicrosoftSqlserverJdbc::SQLServerException: The TCP/IP connection to the host IP_HIDDEN, port 1433 has failed. Error: "Connect timed out. 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.".>

```

Here my JDBC Config:

```auto
jdbc { 
        tags => ["usuario"] 
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" 
        jdbc_driver_library => "/usr/share/logstash/plugins/connectors-database/sqlserver/mssql-jdbc-9.2.1.jre8.jar"
        jdbc_connection_string => "jdbc:sqlserver://IP_HIDDEN:1433;databaseName=DATABASE;integratedSecurity=false;"
        jdbc_user => "user" 
        jdbc_password => "password"
        sql_log_level => "debug"
        connection_retry_attempts => 3
        statement => "select * from usuario" 
    }

```

**If I connect through SQL Server Management Studio with the same IP and credentials, it works correctly. But with JDBC it doesn't work. Does anyone know what can it be?**

**Information that may be useful: SQL Server is hosted on AZURE**

Here are all the connectors I've tried, and both give the same error

![image](https://us1.discourse-cdn.com/elastic/original/3X/9/3/93cd369f0e140080d620e82c51188c5b8cb287da.png)

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 9, 2024, 3:23pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/2 "2024-04-09T15:23:25Z")

</div>

I have a similar problem but my problem is occurring in Output

output {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://10.0.00.00:5432/dwanaics"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
jdbc\_driver\_library =\> "/usr/share/logstash/vendor/jar/jdbc/postgresql-42.2.24.jar"  
statement =\> "INSERT INTO tms.nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)"  
jdbc\_user =\> "postgres"  
jdbc\_password =\> "D\*r"  
}  
}

displays the following erro  
[2024-04-09T15:12:45,430][ERROR][logstash.outputs.jdbc] Unknown setting 'jdbc\_user' for jdbc  
[2024-04-09T15:12:45,431][ERROR][logstash.outputs.jdbc] Unknown setting 'jdbc\_password' for jdbc  
[2024-04-09T15:12:45,431][ERROR][logstash.outputs.jdbc] Unknown setting 'jdbc\_driver\_library' for jdbc  
[2024-04-09T15:12:45,431][ERROR][logstash.outputs.jdbc] Unknown setting 'jdbc\_connection\_string' for jdbc  
[2024-04-09T15:12:45,431][ERROR][logstash.outputs.jdbc] Unknown setting 'jdbc\_driver\_class' for jdbc

the access data is correct

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 9, 2024, 3:28pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/3 "2024-04-09T15:28:47Z")

</div>

The jdbc output is a third-party plugin. None of those options exist, they all have other names. See [here](https://github.com/theangryangel/logstash-output-jdbc).

---

<div class="post-metadata">

**Author:** ![Kaique292](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kaique292/32/133388_2.png) [@Kaique292](https://discuss.elastic.co/u/Kaique292)\
**Post date:** [April 9, 2024, 3:37pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/4 "2024-04-09T15:37:43Z")

</div>

Because your config is in output. Should be Input

```auto
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://10.0.00.00:5432/dwanaics"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_driver_library => "/usr/share/logstash/vendor/jar/jdbc/postgresql-42.2.24.jar"
statement => "INSERT INTO tms.nota_fiscal_conhecimento (oid_conhecimento, oid_nota_fiscal, dt_gravacao, usuario_gravacao) VALUES (?, ?, ?, ?)"
jdbc_user => "postgres"
jdbc_password => "D*r"
}
}

```

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 9, 2024, 6:06pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/5 "2024-04-09T18:06:29Z")

</div>

my goal is to extract data from a postgresql database and insert it into another postgresql database

I will force the complete code

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://db.consulta.interno.transpofrete.com.br:7432/transpofrete"  
jdbc\_user =\> "anals"  
jdbc\_password =\> "HbBXEt"  
jdbc\_driver\_library =\> "/usr/share/logstash/vendor/jar/jdbc/postgresql-42.2.24.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
schedule =\> "\* \* \* \* \*"  
statement =\> "SELECT oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao FROM nota\_fiscal\_conhecimento"  
tracking\_column =\> "oid\_conhecimento"  
use\_column\_value =\> true  
tracking\_column\_type =\> "numeric"  
last\_run\_metadata\_path =\> "/usr/share/logstash/.logstash\_jdbc\_last\_run"  
}  
}  
output {  
jdbc {  
connection\_string =\> "jdbc:postgresql://10.2.10.00:5432/transpofrete\_tms?user=postgres&password=Linc2077"  
driver\_class =\> "org.postgresql.Driver"  
statement =\> "INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)"  
}  
}

Now it shows the following error

[2024-04-09T18:02:00,083][INFO][logstash.inputs.jdbc][main][34d2bcf272f4a1d0e9ce3d20017dd0d109ecf12eb90850ef891e5d9d19e77e6f] (0.017555s) SELECT oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao FROM nota\_fiscal\_conhecimento where oid\_nota\_fiscal = '489041'  
[2024-04-09T18:02:00,218][ERROR][logstash.outputs.jdbc][main][77220514e6e06f7e998cc005a4cd891671f42e01e6872d299d45f3d26b0c2250] JDBC - Exception. Not retrying {:exception=\>#\<Java::OrgPostgresqlUtil::PSQLException: No value specified for parameter 1.\>, :statement=\>"INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)", :event=\>"{"dt\_gravacao":null,"@version":"1","@timestamp":"2024-04-09T18:02:00.087760016Z","usuario\_gravacao":null,"oid\_conhecimento":366304,"oid\_nota\_fiscal":489041}"}  
[2024-04-09T18:03:00,296][INFO][logstash.inputs.jdbc][main][34d2bcf272f4a1d0e9ce3d20017dd0d109ecf12eb90850ef891e5d9d19e77e6f] (0.015347s) SELECT oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao FROM nota\_fiscal\_conhecimento where oid\_nota\_fiscal = '489041'  
[2024-04-09T18:03:00,418][ERROR][logstash.outputs.jdbc][main][77220514e6e06f7e998cc005a4cd891671f42e01e6872d299d45f3d26b0c2250] JDBC - Exception. Not retrying {:exception=\>#\<Java::OrgPostgresqlUtil::PSQLException: No value specified for parameter 1.\>, :statement=\>"INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)", :event=\>"{"dt\_gravacao":null,"@version":"1","@timestamp":"2024-04-09T18:03:00.301779262Z","usuario\_gravacao":null,"oid\_conhecimento":366304,"oid\_nota\_fiscal":489041}"}

what to do to insert the values ​​of the other columns

However, when trying to insert this data into the other table using the INSERT statement, some of the values ​​are missing or null, which is causing the error.

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 9, 2024, 8:01pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/6 "2024-04-09T20:01:03Z")

</div>

Can you help me please?

This is the first time I use logstash

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 9, 2024, 9:46pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/7 "2024-04-09T21:46:30Z")

</div>

> [@JOPBI\_Digital](#):
>
> [77220514e6e06f7e998cc005a4cd891671f42e01e6872d299d45f3d26b0c2250] JDBC - Exception. Not retrying {:exception=\>#\<Java::OrgPostgresqlUtil::PSQLException: No value specified for parameter 1.\>, :statement=\>"INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)", :event=\>"{"dt\_gravacao":null,"@version":"1","@timestamp":"2024-04-09T18:03:00.301779262Z","usuario\_gravacao":null,"oid\_conhecimento":366304,"oid\_nota\_fiscal":489041}"}

You cannot assume that logstash will fill in the oid\_conhecimento column from the [oid\_conhecimento] field -- you need to tell it. I cannot test it, but perhaps something like

statement =\> "INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (%{oid\_conhecimento}, %{oid\_nota\_fiscal}, %{dt\_gravacao}, %{usuario\_gravacao})"

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 10, 2024, 10:57am UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/8 "2024-04-10T10:57:35Z")

</div>

input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:postgresql://db.consul.com.br:7432/trane"  
jdbc\_user =\> "anaics"  
jdbc\_password =\> "HXEt"  
jdbc\_driver\_library =\> "/usr/share/logstash/vendor/jar/jdbc/postgresql-42.2.24.jar"  
jdbc\_driver\_class =\> "org.postgresql.Driver"  
schedule =\> "\* \* \* \* \*"  
statement =\> "SELECT oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao FROM nota\_fiscal\_conhecimento where oid\_nota\_fiscal = 489041"  
tracking\_column =\> "oid\_conhecimento"  
use\_column\_value =\> true  
tracking\_column\_type =\> "numeric"  
last\_run\_metadata\_path =\> "/usr/share/logstash/.logstash\_jdbc\_last\_run"  
}  
}

output {  
jdbc {  
connection\_string =\> "jdbc:postgresql://10.2.00.20:5432/transe\_tms?user=analytics&password=Hbt"  
driver\_class =\> "org.postgresql.Driver"  
statement =\> "INSERT INTO nota\_fiscal\_conhecimento1 (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (%{oid\_conhecimento}, %{oid\_nota\_fiscal}, %{dt\_gravacao}, %{usuario\_gravacao})"  
}  
}

But it appears that the field values ​​are being interpreted incorrectly. Placeholders `"%{oid_conhecimento}", "%{oid_nota_fiscal}", "%{dt_gravacao}", "%{usuario_gravacao}"` are not being replaced with actual event values.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 10, 2024, 4:13pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/9 "2024-04-10T16:13:00Z")

</div>

> [@Badger](#):
>
> statement =\> "INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (%{oid\_conhecimento}, %{oid\_nota\_fiscal}, %{dt\_gravacao}, %{usuario\_gravacao})"

Having taken another look at the documentation, you could enable the unsafe\_statement option to make the output sprintf that, but first try

statement =\> ["INSERT INTO nota\_fiscal\_conhecimento (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)", "oid\_conhecimento", "oid\_nota\_fiscal", "dt\_gravacao", "usuario\_gravacao"]

You could use "%{oid\_conhecimento}" etc in that, but it will inhibit automatic type conversion in the output.

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 10, 2024, 4:31pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/10 "2024-04-10T16:31:03Z")

</div>

The error indicates that the "oid\_conhecimento" column is of type bigint, but the expression provided is character varying.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 10, 2024, 4:47pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/11 "2024-04-10T16:47:43Z")

</div>

That's seems likely if you use "%{oid\_conhecimento}" etc, but for "oid\_conhecimento" the [code](https://github.com/theangryangel/logstash-output-jdbc/blob/aea74f237cfda3b151dae29cf94b44123c2bc408/lib/logstash/outputs/jdbc.rb#L296) should be converting that value to an integer (not that it needs converting).

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 10, 2024, 4:51pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/12 "2024-04-10T16:51:37Z")

</div>

I managed to fix this part now it's the dt\_gravação that has the error

filter {  
mutate {  
# convert =\> {  
convert =\> { "dt\_gravacao" =\> "string" }  
# "oid\_conhecimento" =\> "integer"  
# "oid\_nota\_fiscal" =\> "integer"  
# }  
remove\_field =\> ["@timestamp", "@version"]  
}  
}

output {  
jdbc {  
connection\_string =\> "jdbc:postgresql://10.200.00:5002/tra\_tms?user=anacs&password=HXEt"  
statement =\> [  
"INSERT INTO nota\_fiscal\_conhecimento1 (oid\_conhecimento, oid\_nota\_fiscal, dt\_gravacao, usuario\_gravacao) VALUES (?, ?, ?, ?)", "oid\_conhecimento", "oid\_nota\_fiscal", "dt\_gravacao", "usuario\_gravacao" ]  
}  
}

ERROR: column "dt\_gravacao" is of type timestamp without time zone but expression is of type character varying

---

<div class="post-metadata">

**Author:** ![JOPBI\_Digital](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jopbi_digital/32/133392_2.png) [@JOPBI\_Digital](https://discuss.elastic.co/u/JOPBI_Digital)\
**Post date:** [April 10, 2024, 5:04pm UTC](https://discuss.elastic.co/t/logstash-with-docker-unable-to-connect-to-database/357072/13 "2024-04-10T17:04:39Z")

</div>

I solved the problem

```
    statement => ["INSERT INTO nota_fiscal_conhecimento1 (oid_conhecimento, oid_nota_fiscal, dt_gravacao, usuario_gravacao) VALUES (?, ?, TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS'), ?)", "oid_conhecimento", "oid_nota_fiscal", "dt_gravacao", "usuario_gravacao"]
}

```

}

Thank you master, it helped me a lot to find the solution