Logstash JDBC gets only one record from MySQL with LIMIT 1

Question

I have a database in MySQL where I have a lot of CISCO CDR. I made VIEW to agrr calls with some option and want to sync to ES to use canvas. In test DB I have 10 records int SUMMARY view and try to do first sync but have only one record in ES. I checked ES log and saw the QUERY this:

SELECT count(*) AS `count` FROM (SELECT *, UNIX_TIMESTAMP(TimeStampAdd) AS unix_ts_in_secs FROM CALLS_SUMMARY WHERE (UNIX_TIMESTAMP(TimeStampAdd) > 1612444443 AND TimeStampAdd < NOW()) ORDER BY TimeStampAdd ASC) AS `t1` LIMIT 1

Question - What is LIMIT 1? I use python to import CDR files in MySQL and use BULK insert. Some times in summarized view I have more than 100 calls per minute.

Here is my Logstash conf

input {
  jdbc {
    jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.22.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/CUCM-CDR"
    jdbc_user => "csv"
    jdbc_password => "123"
    jdbc_paging_enabled => true
    tracking_column => "unix_ts_in_secs"
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/2 * * * * *"
    last_run_metadata_path => "/etc/logstash/conf.d/lastrun/.logstash_jdbc_last_run_mysql"
    jdbc_page_size => 1000
    statement => "SELECT *, UNIX_TIMESTAMP(TimeStampAdd) AS unix_ts_in_secs FROM CALLS_SUMMARY WHERE (UNIX_TIMESTAMP(TimeStampAdd) > :sql_last_value AND TimeStampAdd < NOW()) ORDER BY TimeStampAdd ASC"
  }
}
filter {
  mutate {
  #copy => { "id" => "[@metadata][_id]"}
    remove_field => ["@timestamp","TimeStampAdd"]
  }
        date {
             match => ["dateTimeDisconnect", "UNIX"]
                      target => "dateTimeDisconnect_formatted"
             }
}
output {
  #stdout { codec =>  "rubydebug"}
  elasticsearch {
      index => "calls-summary"
  #      document_id => "%{[@metadata][_id]}"
  }
}

P.S. If its posible to make some summarization VIEW in ES and get select from it?

question from:https://stackoverflow.com/questions/66050027/logstash-jdbc-gets-only-one-record-from-mysql-with-limit-1

Answer 1

Waitting for answers