# The jdbc request is not executed

**URL:** <https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851>\
**Category:** Logstash\
**Created:** [September 25, 2018, 2:21pm UTC](https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851 "2018-09-25T14:21:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Igorr](https://avatars.discourse-cdn.com/v4/letter/i/77aa72/32.png) [@Igorr](https://discuss.elastic.co/u/Igorr)\
**Post date:** [September 25, 2018, 2:21pm UTC](https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851/1 "2018-09-25T14:21:37Z")

</div>

The jdbc request is not executed.

Config in logstash:  
input {  
jdbc {  
jdbc\_driver\_library =\> "/etc/logstash/mysql-connector-java-5.1.46-bin.jar"  
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
jdbc\_connection\_string =\> "jdbc:mysql://222.1.1.100:3306/voip.company.ru\_webapp?zeroDateTimeBehavior=convertToNull"  
jdbc\_user =\> "voip-monitor"  
jdbc\_password =\> "\*\*\*\*\*\*\*\*\*_"  
schedule =\> "_ \* \* \* \*"  
statement\_filepath =\> "/etc/logstash/include/statement/asterisk\_db\_rt\_agents"  
last\_run\_metadata\_path =\> "/etc/logstash/logstash\_jdbc\_last\_run/getdata\_voip.roseltorg.ru\_webapp\_rtagents"  
add\_field =\> { "typetable\_id" =\> "rt\_agents\_table" }  
add\_field =\> { "typeoutput\_id" =\> "webapp\_db" }  
}  
}  
output {  
if [typeoutput\_id] == "webapp\_db" {  
elasticsearch {  
hosts =\> "222.1.1.101:9200"  
index =\> "asterisk-pbx-db-rt"  
user =\> "elastic"  
password =\> "\*\*\*\*\*\*\*\*\*\*\*"  
doc\_as\_upsert =\> "true"  
document\_id =\> "%{typetable\_id}%{agent\_id}"  
}  
}  
}  
asterisk\_db\_rt\_agents file:

`SET @indexid=0;SELECT @indexid:=@indexid+1 AS indexid,agent_id,logindate,pausedate,answerdate,holddate,hangupdate,readydate,dialoutdate,phone,phone_offline,session_id,processing_enabled,web_updated,onform,paused,reason_id,ringing,queue_id,uniqueid,card_uniqueid,callerid,directid,calleeid FROM`voip.roseltorg.ru\_webapp`.rt_agents`

Why in not working? The error in the log:  
`[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 'SELECT @indexid:=@indexid+1 AS indexid,agent_id,logindate,pausedate,answerdate,h' at line 2: SET @indexid=0;`

The manual starts perfectly.

How best to number all the rows in the query?

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [September 26, 2018, 12:32am UTC](https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851/2 "2018-09-26T00:32:02Z")

</div>

> [@Igorr](#):
>
> SET @indexid=0;SELECT @indexid:=@indexid+1 AS indexid,agent\_id,logindate,pausedate,answerdate,holddate,hangupdate,readydate,dialoutdate,phone,phone\_offline,session\_id,processing\_enabled,web\_updated,onform,paused,reason\_id,ringing,queue\_id,uniqu

I believe the problem may be that the JDBC input requires a single statement, and that invoking `SET` in a separate statement may be getting in the way.

Here's a stack overflow answer about how to do it in a single statement:

> <https://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table/3127004#3127004>

---

<div class="post-metadata">

**Author:** ![Igorr](https://avatars.discourse-cdn.com/v4/letter/i/77aa72/32.png) [@Igorr](https://discuss.elastic.co/u/Igorr)\
**Post date:** [September 26, 2018, 8:51am UTC](https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851/3 "2018-09-26T08:51:24Z")

</div>

Perfect! Thank you so much.

---

<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:** [October 24, 2018, 9:06am UTC](https://discuss.elastic.co/t/the-jdbc-request-is-not-executed/149851/4 "2018-10-24T09:06:04Z")

</div>

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