Logstash 5.5 input jdbc schedule problem

Elastic Stack Logstash
1

Hi,

I have configured logstash to get data from mysql server and i have scheduled to get data every minute but it's not working. I think i have made a mistake on my configuration. If somebody can help me :slight_smile:

I have made some config file on the /etc/logstash/conf.d
This is an example :

#/etc/logstash/conf.d/config_mysql_glpi_tickets.conf 
input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/vendor/jdbc-mysql/mysql-connector-java-5.1.42-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://IP:3306/glpi"
    jdbc_user => "glpi_dashboard"
    jdbc_password => "XXXpassXXX"
    schedule => "* * * * *"
    jdbc_page_size => 50000
    jdbc_paging_enabled => true
    statement => "SELECT * FROM bvs_vue_tickets"
    use_column_value => true
    tracking_column => "id"
    tracking_column_type => "numeric"
  }
}
output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "glpi_tickets"
    document_type => "tickets"
    user => "elastic"
    password => "changeme"
    document_id => "%{id}"
  }
}

If i run bin/logstash -f /etc/logstash/conf.d/config_mysql_glpi_tickets.conf
It's ok but if i use the service
service logstash restart
there no new data.

2

Is it a normal comportment ?

3

Have you cleared the jdbc_last_run file that records the id of the last processed row? If not, Logstash will think it's already done unless you've inserted more rows since the last run.

4

in fact, i have configured this parameter because i want to reindex all and not just add the new value.
And when i use logstash -f it's working properly.

5

Hmm, now I see that your query doesn't use the parameter that tracks the last row fetched so my last post can be disregarded. Since it work when you run it but now when it's run as a service I'd suspect a permissions issue. Look in the Logstash log and raise the log level if necessary.

6

I have the same idea but when i check the permission on my conf.d folder i have this

root@ubuntu-elastic:/usr/share/logstash/bin# ls -lh /etc/logstash/conf.d/
total 20K
-rw-r--r-- 1 logstash logstash 2,3K juil. 27 14:35 config_mssql_astech.conf
-rw-r--r-- 1 logstash logstash 742 juil. 27 11:50 config_mysql_depot_sauvage.conf
-rw-r--r-- 1 logstash logstash 756 juil. 31 11:41 config_mysql_glpi_tasks.conf
-rw-r--r-- 1 logstash logstash 764 août 2 10:56 config_mysql_glpi_tickets.conf
-rw-r--r-- 1 logstash logstash 782 juil. 27 12:26 config_oracle_election.conf

I have seen one thing on my logstash.yml i don't have a parameter whith conf.d folder, is it normal ?

7

In my logstash.yml i have this directive

path.config: /etc/logstash/conf.d
But always not ok :frowning:

8

When i run this command
sudo -u logstash ./logstash -f /etc/logstash/conf.d/config_mysql_glpi_tickets.conf
I have this result

11:50:03.479 [Ruby-0-Thread-23: /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:283] INFO logstash.inputs.jdbc - (0.071000s) SELECT count() AS count FROM (SELECT * FROM bvs_vue_tickets) AS t1 LIMIT 1
11:50:03.577 [Ruby-0-Thread-23: /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:283] INFO logstash.inputs.jdbc - (0.095000s) SELECT * FROM (SELECT * FROM bvs_vue_tickets) AS t1 LIMIT 50000 OFFSET 0
{ 372706 rufus-scheduler intercepted an error:
372706 job:
372706 Rufus::Scheduler::CronJob "/5 * * * *" {}
372706 error:
372706 372706
372706 Errno::EACCES
372706 Permission denied - /home/user/.logstash_jdbc_last_run
372706 org/jruby/RubyFile.java:370:in initialize' 372706 org/jruby/RubyIO.java:871:in new'
372706 org/jruby/RubyIO.java:4058:in write' 372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-4.2.1/lib/logstash/inputs/jdbc.rb:285:in update_state_file'
372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-4.2.1/lib/logstash/inputs/jdbc.rb:251:in run' 372706 org/jruby/RubyProc.java:281:in call'
372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:234:in do_call' 372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:258:in do_trigger'
372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:300:in start_work_thread' 372706 org/jruby/RubyProc.java:281:in call'
372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:299:in start_work_thread' 372706 org/jruby/RubyKernel.java:1479:in loop'
372706 /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:289:in `start_work_thread'
372706 tz:
372706 ENV['TZ']:
372706 Time.now: 2017-08-04 11:50:05 +0200
372706 scheduler:
372706 object_id: 205120
372706 opts:
372706 {:max_work_threads=>1}
372706 frequency: 0.3
372706 scheduler_lock: #Rufus::Scheduler::NullLock:0x7a74bf5c
372706 trigger_lock: #Rufus::Scheduler::NullLock:0x1a1ea5cf
372706 uptime: 102.014 (1m42s13)
372706 down?: false
372706 threads: 2
372706 thread: #Thread:0x58c6141d
372706 thread_key: rufus_scheduler_205120
372706 work_threads: 1
372706 active: 1
372706 vacant: 0
372706 max_work_threads: 1
372706 mutexes: {}
372706 jobs: 1
372706 at_jobs: 0
372706 in_jobs: 0
372706 every_jobs: 0
372706 interval_jobs: 0
372706 cron_jobs: 1
372706 running_jobs: 1
372706 work_queue: 0
} 372706 .

9

I have fix the right on the file with chown logstash:logstash
And on the file i have this value :
--- 70
I don't know if it's normal ?
It's always not ok when i use the service

10

I have not give more details but i use ubuntu 16.04 and elk 5.5.1 with a .deb installation.

11

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