# Logstash JDBC unable to run multiple statements and ingest data from different tables

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015>\
**Category:** Logstash\
**Tags:** docker\
**Created:** [October 13, 2023, 8:43pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015 "2023-10-13T20:43:58Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 13, 2023, 8:43pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/1 "2023-10-13T20:43:58Z")

</div>

Hi,

I'm running Logstash version 8.10.2 in a Docker container to ingest data from a MySQL DB into Elastic.

I don't know why the first statement is executed but the second one does not execute.

Below is my logstash config:

```auto
input {

  jdbc {
          clean_run => true
          jdbc_driver_library => "/usr/share/logstash/config/mysql-connector-j-8.1.0.jar"
          jdbc_driver_class => "com.mysql.jdbc.Driver"
          jdbc_connection_string => "jdbc:mysql://10.10.10.1:3306/myDB"
          jdbc_user => "username"
          jdbc_password => "password"
          schedule => "* * * * *"
          statement => "select * from contacts where icn > :sql_last_value"
          use_column_value => true
          tracking_column => "icn"
          type => "contacts"
          last_run_metadata_path => "/usr/share/logstash/data/import-contacts.yml"
      }
      
  jdbc {
          clean_run => true
          jdbc_driver_library => "/usr/share/logstash/config/mysql-connector-j-8.1.0.jar"
          jdbc_driver_class => "com.mysql.jdbc.Driver"
          jdbc_connection_string => "jdbc:mysql://10.10.10.1:3306/myDB"
          jdbc_user => "username"
          jdbc_password => "password"
          schedule => "* * * * *"
          statement => "select * from jobs where jobid > :sql_last_value"
          use_column_value => true
          tracking_column => "jobid"
          type => "jobs"
          last_run_metadata_path => "/usr/share/logstash/data/import-jobs.yml"
      }

filter {
    
}

output {

  stdout { 
    id => "all_output"
    codec => rubydebug 
  }
  

  elasticsearch {
    hosts => ["https://10.1.10.26:9200"]
    index => "z4_db"
    user => "elastic"
    password => "changeme"
    ssl_verification_mode => "none"
  }

}

```

Both `import-contacts.yml` and `import-jobs.yml` get created in the container, however, only the `import-contacts.yml` file has the last value in it.

---

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 13, 2023, 8:51pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/2 "2023-10-13T20:51:25Z")

</div>

Also, when I'm restarting the container it is re-running the first statement again thus causing duplicates inside of Elastic. For some reason, the `import-contacts.yml` file is not persisting between container restarts.

---

<div class="post-metadata">

**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\
**Post date:** [October 14, 2023, 2:54pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/3 "2023-10-14T14:54:54Z")

</div>

> [@mohsin106](#):
>
> For some reason, the `import-contacts.yml` file is not persisting between container restarts.

Are you binding mounting the path `/usr/share/logstash/data/` to a path in your host?

---

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 14, 2023, 8:07pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/4 "2023-10-14T20:07:55Z")

</div>

I believe I am mounting the path in my docker-compose.yml file:

```auto
version: '3.8'
services:
  logstash-mysql:
    image: logstash:8.10.2
    user: 1000:1000
    volumes:
      - "./logstash.yml:/usr/share/logstash/config/logstash.yml"
      - "./logstash.conf:/usr/share/logstash/pipeline/logstash.conf"
      - "./mysql-connector-j-8.1.0.jar:/usr/share/logstash/config/mysql-connector-j-8.1.0.jar"
      # - "./import-contacts:/usr/share/logstash/data/import-contacts.yml"
      # - "./import-jobs:/usr/share/logstash/data/import-jobs.yml"
      # - "./import_skills:/usr/share/logstash/config/import_skills"
      - mysqldata:/usr/share/logstash/data
volumes:
  mysqldata:
    driver: local

```

I also tired mounting a local file `import-contacts` to `/usr/share/logstash/data/import-contacts.yml` but had no luck.

---

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 14, 2023, 8:43pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/5 "2023-10-14T20:43:41Z")

</div>

When I bind mount the `import-contact` file from my local host to `/usr/share/logstash/data/import-contacts.yml` inside the container, I get a permission denied error:

```auto
exception=>#<Errno::EACCES: Permission denied - /usr/share/logstash/data/import-contacts.yml>

```

---

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 14, 2023, 9:50pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/6 "2023-10-14T21:50:27Z")

</div>

I'm not sure if I'm running into a bug or not. I have now changed my `docker-compose.yml` to this:

```auto
version: '3.8'
services:
  logstash-mysql:
    image: logstash:8.10.2
    volumes:
      - "./logstash.yml:/usr/share/logstash/config/logstash.yml"
      - "./conf.d:/usr/share/logstash/pipeline/"
      - "./mysql-connector-j-8.1.0.jar:/usr/share/logstash/config/mysql-connector-j-8.1.0.jar"
      - mysqldata:/usr/share/logstash/data
volumes:
  mysqldata:
    driver: local

```

I have split my original logstash.conf file into two separate conf files (`logstash1.conf` and `logstash2.conf`) and stored them locally inside the `conf.d` directory.

I put one JDBC input from above into each logstash.conf file. So logstash1.conf is going to query the **contacts** table and logstash2.conf is going to query the **job** table.

I then just mount the `conf.d` directory to the `/usr/share/logstash/pipeline/`

I'm loading both logstash.conf file from my logstash.yml file like this:

```auto
config.reload.automatic: true
config.reload.interval: 5s
# log.level: debug

xpack.management.pipeline.id:
  - logstash*

```

I was initially seeing an error message indicating I was using a duplicate id in my `stdout` output plugin in my `logstash2.conf` file. So I changed the `id` to `"all_output_logstash2_conf"` which made that error go away.

Even though I have two separate logstash.conf files running, for some reason it thinks I'm running one logstash.conf file with 2 `stdout` output plugins using the same `id` value.

Currently, both logstash.conf files are running, but only the JDBC input from `logstash1.conf` is executing. The JDBC input from `logstash2.conf` is not executing.

To confirm both logstash.conf files are running I changed the `index` value from `logstash2.conf` to `"z4_db_logstash2_conf"` and I see indices from both logstash.conf files writing to ES. The data from the `logstash1.conf` is being stored in the index from `logstash2.conf`.

---

<div class="post-metadata">

**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\
**Post date:** [October 15, 2023, 12:30pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/7 "2023-10-15T12:30:17Z")

</div>

There is a couple of issues here.

First, separating your config in multiple files will not make logstash run them as different pipelines if you do not use `pipelines.yml`, which you seem to not be using as you aren't binding mounting the `pipelines.yml`, check this [question](https://discuss.elastic.co/t/multiple-pipelines-docker/141728) for an example.

You would need to have a file like this:

```auto
- pipeline.id: pipeline-1
  path.config: "/usr/share/logstash/pipeline/logstash1.conf"
- pipeline.id: pipeline-2
  path.config: "/usr/share/logstash/pipeline/logstash2.conf"

```

And bind mount this file as `/usr/share/logstash/config/pipelines.yml`

If you do not do that, logstash will merge both files and run as a single pipeline with the name `main`, it would be the same thing as having just a single configuration.

I suggest that you create this file and mount it as `pipelines.yml` to make logstash run your configuration as two different pipelines.

> [@mohsin106](#):
>
> So I changed the `id` to `"all_output_logstash2_conf"` which made that error go away.

The `id` field on a filter is optional and mostly used to troubleshoot some performance issues, you may remove it if you want.

> [@mohsin106](#):
>
> ```auto
> xpack.management.pipeline.id:
> - logstash*
> 
> ```

You should also remove this from your `logstash.yml`, this is used when you have Centralized Pipeline Management configured and configure all your pipelines with Kibana, this is a paid feature that will only work if you have at least a platinum license.

> [@mohsin106](#):
>
> The data from the `logstash1.conf` is being stored in the index from `logstash2.conf`.

As mentioned before, you are not running two independent pipelines, you are running just one pipeline which is a merge of your two configurations, so this is expected as you do not have any conditionals in your output.

I see no errors in your `jdbc` configuration, so the only things that I can think for it to not work are:

- There is no data being returned for your query.
- There is data being returned but for some reason it has some conflict with the data returned by the first jdbc query, which would make elasticsearch reject the document, but this would generate a log and you didn't share anything about it.

Can you share a sample of the data returned by both of those queries?

The fact that the second `jdbc` filter is not creating the last run metadata path suggests that it is not returning any data.

Also, can you share your logstash logs when you start your docker-compose to see if it is generating any WARN/ERROR lines?

---

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 15, 2023, 5:19pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/8 "2023-10-15T17:19:10Z")

</div>

> [@leandrojmp](#):
>
> The fact that the second `jdbc` filter is not creating the last run metadata path suggests that it is not returning any data.

This was it. For some reason, Logstash is not receiving any data when this query is run `"select * from jobs where jobid > :sql_last_value"` inside of the JDBC plugin.  
However, if I log into the db and run the query `select * from jobs` I receive 59 rows back.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 12, 2023, 5:20pm UTC](https://discuss.elastic.co/t/logstash-jdbc-unable-to-run-multiple-statements-and-ingest-data-from-different-tables/345015/9 "2023-11-12T17:20:01Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.