Having trouble adding muliple tables using logstash

Elastic Stack Logstash
,
1

so i have 7 tables in my sql database and i'v been trying to add all of them to an index trough logstash jdbc using a query file , the query goes somthing like

SELECT * FROM [TABLE];
SELECT * FROM [TABLE];
SELECT * FROM [TABLE];
...

but it when runing logstash i get a

Java::JavaSql::SQLSyntaxErrorException: 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 * FROM book.Customers;

i also tried joining tables and other statement , but the only thing that worked was doing each select * at time meaning which is not practical

2

HI @Mustapha_Hadj,

Welcome to the Elastic Community.

As mentioned in doc configuring_multiple_sql_statements , You need to define separate Logstash configuration files for each statement.

Also you can achieve something using join query like this.

1 Like
3

i did try that just now but it gave me

Java::JavaSql::SQLSyntaxErrorException: SELECT command denied to user 'avid_reader'@'172.18.0.5' for table 'BranchesBooks':  SELECT * FROM book.BranchesBooks

here is how the pipeline config goes :

input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://mysql:3306"
    jdbc_user => "avid_reader"
    jdbc_password => ""
    sql_log_level => "debug"  # Set Logstash logging level as this
    clean_run => true # Set to true for indexing from scratch
    record_last_run => false
  statement => " SELECT * FROM book.Bookstores"
  }
jdbc {
    jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://mysql:3306"
    jdbc_user => "avid_reader"
    jdbc_password => ""
    sql_log_level => "debug"  # Set Logstash logging level as this
    clean_run => true # Set to true for indexing from scratch
    record_last_run => false
  statement => " SELECT * FROM book.Customers"
  }
4

This is not a Logstash error, this is a permissions error from your database, you need to check the permissions of that user.

3 Likes
6

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