Reading mysql database: You have an error in your SQL syntax;

Elastic Stack Logstash
1

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 ';) ASt1LIMIT 1' at line 1: SELECT count(*) AScountFROM (SELECT * FROM employees;) ASt1LIMIT 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?

2

Well, I'd try taking the semicolon off the "statement => ...", but the SELECT COUNT(*) FROM ... looks like something is going to run your query twice, so best hope that that isn't a performance disaster.

3

Thanks @TimWard for your prompt reply, indeed, that worked!

I am new to logstash, why the single request is going to be read twice?
What is the performance risk?

4

Something somewhere is generating "SELECT COUNT(*) FROM (<your query>)" and is also going to have to run <your query> separately to get the actual results. So < your query> is likely to be run twice (depending on how clever the RDBMS might be at optimising the first query, but given how catastrophic COUNT(*) is on many RDBMSs I wouldn't hold out much hope).

If the employees table isn't very big and/or isn't scanned very often this isn't going to be a problem, but if you're already at the limits of performance you could run into problems unnecessarily early as the table grows.

5

Do you mean that I need to run multiple specific queries to get the data from the database instead of reading the entire table and get filter specific data?

6

Sorry, I was just trying to help with your original problem of the syntax error, and observed the generated second query in passing. I don't know anything specific about what the Logstash JDBC input is doing or how to control it.