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 FROMvoip.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?