# Reading mysql database: You have an error in your SQL syntax; **URL:** \ **Category:** Logstash\ **Created:** [April 23, 2018, 1:02pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107 "2018-04-23T13:02:46Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![AJ\_NOURI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aj_nouri/32/29741_2.png) [@AJ\_NOURI](https://discuss.elastic.co/u/AJ_NOURI)\ **Post date:** [April 23, 2018, 1:02pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/1 "2018-04-23T13:02:47Z")
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`t1`LIMIT 1' at line 1: SELECT count(*) AS`count`FROM (SELECT * FROM employees;) AS`t1`LIMIT 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? ---
**Author:** ![TimWard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timward/32/19574_2.png) [@TimWard](https://discuss.elastic.co/u/TimWard)\ **Post date:** [April 23, 2018, 1:07pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/2 "2018-04-23T13:07:04Z")
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. ---
**Author:** ![AJ\_NOURI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aj_nouri/32/29741_2.png) [@AJ\_NOURI](https://discuss.elastic.co/u/AJ_NOURI)\ **Post date:** [April 23, 2018, 1:17pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/3 "2018-04-23T13:17:10Z")
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? ---
**Author:** ![TimWard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timward/32/19574_2.png) [@TimWard](https://discuss.elastic.co/u/TimWard)\ **Post date:** [April 23, 2018, 1:24pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/4 "2018-04-23T13:24:34Z")
Something somewhere is generating "SELECT COUNT(\*) FROM (\)" and is also going to have to run \ 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. ---
**Author:** ![AJ\_NOURI](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aj_nouri/32/29741_2.png) [@AJ\_NOURI](https://discuss.elastic.co/u/AJ_NOURI)\ **Post date:** [April 23, 2018, 1:52pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/5 "2018-04-23T13:52:42Z")
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? ---
**Author:** ![TimWard](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/timward/32/19574_2.png) [@TimWard](https://discuss.elastic.co/u/TimWard)\ **Post date:** [April 23, 2018, 2:19pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/6 "2018-04-23T14:19:50Z")
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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 21, 2018, 2:19pm UTC](https://discuss.elastic.co/t/reading-mysql-database-you-have-an-error-in-your-sql-syntax/129107/7 "2018-05-21T14:19:50Z")
Closed on May 21, 2018, 2:19pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.