I have a mysql fake database which I would like to read as input to logstash & then send the data to elasticsearch.
The database IP, the password, the port, the database and the table are correct As well as the query:
From the database host
$ mysql -h 127.0.0.1 -u root -p -Bse "USE employees; SELECT * FROM employees;"
...
|26859|1955-11-09|Jaana|Fadgyas|M|1989-09-19|
|26860|1959-11-21|Indrajit|Molberg|M|1988-10-14|
|26861|1964-06-17|Candida|Falck|F|1989-10-09|
|26862|1954-08-25|Sadegh|Hebert|F|1989-12-06|
|26863|1954-08-14|Badri|Shinomoto|M|1989-08-29|
|26864|1964-09-04|Yinghua|Strooper|M|1995-06-05|
|26865|1953-01-20|Qiwen|Ventosa|M|1988-11-05|
|26866|1962-02-03|Denis|Gecseg|F|1985-10-18|
|26867|1959-11-26|Qunsheng|Kitsuregawa|F|1988-09-11|
|26868|1956-11-22|Elrique|Khalil|F|1987-02-26|
|26869|1955-04-26|Jasminko|Vecchio|F|1985-10-29|
|26870|1960-12-12|Stepehn|Peot|M|1991-07-23|
...
From logstash
$ logstash -f mylogstash/config-dir/pipelines.conf --config.reload.automatic
12:49:00.100 [Ruby-0-Thread-18: /usr/share/logstash/vendor/bundle/jruby/1.9/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/jobs.rb:283] ERROR logstash.inputs.jdbc - Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';) AS
t1LIMIT 1' at line 1: SELECT count(*) AS
countFROM (SELECT * FROM employees;) AS
t1LIMIT 1
$ cat pipelines.conf
input {
jdbc {
jdbc_driver_library => "/mylogstash/plugin/mysql-connector-java-5.1.23-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://192.168.0.146:3306/employees"
jdbc_user => "root"
jdbc_password => "passwd"
statement => "SELECT * FROM employees;"
schedule => "* * * * *"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}
filter {
}
output {
elasticsearch {
hosts => [ "elasticsearch:9200" ]
document_type => "default"
http_compression => true
}
}
The query looks OK.
Any hint what is wrong with the config?