# Logstash jdbc plugin how to ingest data from mysql table

**URL:** https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335
**Category:** Logstash
**Created:** [January 31, 2020, 9:25am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335 "2020-01-31T09:25:13Z")
**Posts on this page:** 5
**Page:** 1

<div class="post-metadata">

### Author: ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)
#### Post date: [January 31, 2020, 9:25am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335/1 "2020-01-31T09:25:13Z")

</div>

Hi everyone!

I'm trying to transport data from MySQL table `INFORMATION_SCHEMA.PROCESSLIST` via Logstash jdbc plugin.

I never worked with jdbc before so I installed jdbc with `sudo apt-get install libmysql-java` command and created the following mysql.conf file in logstash conf.d directory:

```
input {
  jdbc {
    jdbc_driver_library => "/usr/share/java/mysql-connector-java-5.1.45.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/es_db"
    jdbc_user => <user12>
    jdbc_password => <user>
    jdbc_paging_enabled => true
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/5 * * * * *"
    statement => "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST"
  }
}

output {
  # stdout { codec => "rubydebug"}
  elasticsearch {
      hosts => ["localhost:9200"]
      index => "rdbms_sync_idx"

  }
}

```

The error my logs show me when I try to restart Logstash is:

```
logstash[8678]: [2020-01-31T09:10:47,994][ERROR][logstash.agent] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of [\\t\\r\\n], \"#\", [A-Za-z0-9_-], '\"', \"'\", [A-Za-z_], \"-\", [0-9], \"[\", \"{\" at line 34, column 18 (byte 1200) after input {\n jdbc {\n jdbc_driver_library => \"/usr/share/java/mysql-connector-java-5.1.45.jar\"\n jdbc_driver_class => \"com.mysql.jdbc.Driver\"\n jdbc_connection_string => \"jdbc:mysql://localhost:3306/es_db\"\n jdbc_user => ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in `compile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2584:in `map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:156:in `initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:26:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in `execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:326:in `block in converge_state'"]}

```

The config file doesn't even have 34 line 😃 Where do you think is a problem?

I also tried the tutorial from Elastic [blog](https://www.elastic.co/blog/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash) where I tried to set everything like there is and it showed me the same error. Could it be caused by different version of jdbc ?

Thanks for any ideas!!

---

<div class="post-metadata">

### Author: ![TWhitehouse](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@TWhitehouse](https://discuss.elastic.co/u/TWhitehouse)
#### Post date: [January 31, 2020, 9:55am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335/2 "2020-01-31T09:55:09Z")

</div>

It's saying theres a formatting error I think.  
Have you missed some quotes or something, or used a special character?  
In your example it shows \<user\>  
Is that how it's actually typed in the pipeline? With \<\>?

---

<div class="post-metadata">

### Author: ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)
#### Post date: [January 31, 2020, 10:47am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335/3 "2020-01-31T10:47:20Z")

</div>

Thank you for reply!

Yeah there was the formatting error in jdbc\_user and jdbc\_password fields.

So now Logstash starts but in log I see this error:

```
logstash[14954]: { 2092 rufus-scheduler intercepted an error:
logstash[14954]: 2092 job:
logstash[14954]: 2092 Rufus::Scheduler::CronJob "*/5 * * * * *" {}
logstash[14954]: 2092 error:
logstash[14954]: 2092 2092
logstash[14954]: 2092 LogStash::PluginLoadingError
logstash[14954]: 2092 Unable to find driver class via URLClassLoader in given driver jars: com.mysql.jdbc.Driver and com.mysql.jdbc.Driver
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.16/lib/logstash/plugin_mixins/jdbc/jdbc.rb:181:in `open_jdbc_connection'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.16/lib/logstash/plugin_mixins/jdbc/jdbc.rb:253:in `execute_statement'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.16/lib/logstash/inputs/jdbc.rb:309:in `execute_query'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.16/lib/logstash/inputs/jdbc.rb:276:in `block in run'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:234:in `do_call'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:258:in `do_trigger'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:300:in `block in start_work_thread'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:299:in `block in start_work_thread'
logstash[14954]: 2092 org/jruby/RubyKernel.java:1425:in `loop'
logstash[14954]: 2092 /usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:289:in `block in start_work_thread'
```

---

<div class="post-metadata">

### Author: ![TWhitehouse](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@TWhitehouse](https://discuss.elastic.co/u/TWhitehouse)
#### Post date: [January 31, 2020, 11:49am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335/4 "2020-01-31T11:49:19Z")

</div>

What version of logstash are you using?

Does the file exist at /usr/share/java/mysql-connector-java-5.1.45.jar?

Does the user running logstash have access to that path?

---

<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: [February 28, 2020, 11:49am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-how-to-ingest-data-from-mysql-table/217335/5 "2020-02-28T11:49:20Z")

</div>

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