# Delete elasticsearch document with logstash-jdbc-input
**URL:** https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490
**Category:** Logstash
**Created:** [April 15, 2016, 9:20am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490 "2016-04-15T09:20:45Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [April 15, 2016, 9:20am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/1 "2016-04-15T09:20:45Z")
Insert and update work with below config file
but elasticseach has no response as i excute delete-sql on mysql
input {
jdbc {
jdbc\_driver\_library =\> "/opt/logstash-2.2.2/mysql-connector-java-5.1.38/mysql-connector-java-5.1.38-bin.jar"
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"
jdbc\_connection\_string =\> "jdbc:mysql://192.168.1.1:3306/databasename"
jdbc\_user =\> "username"
jdbc\_password =\> "password"
schedule =\> "\* \* \* \* \*"
statement =\> "SELECT \* FROM tablename
where `tablename`.`update_time` \> :sql\_last\_value"
use\_column\_value =\> true
tracking\_column =\> update\_time
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [April 16, 2016, 8:32pm UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/2 "2016-04-16T20:32:17Z")
> [@suntuo](#):
>
> but elasticseach has no response as i excute delete-sql on mysql
What does this mean?
All you have shown us is an input, if there is an output that does something in ES it would be useful to see it.
---
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [April 18, 2016, 2:21am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/3 "2016-04-18T02:21:24Z")
output{
elasticsearch {
hosts =\> ["192.168.1.1:9200"]
index =\> "index\_tablename"
document\_id =\> "%{id}" }
}
With the option : document\_id =\> "%{id}"
The existing entries in Elasticsearch with the same ID will be overwrite when i update data on mysql.
but when i delete data form mysql, those ES entries won't be removed.
How can i remove those old data in ES which have been deleted on mysql.
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [April 18, 2016, 2:36am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/4 "2016-04-18T02:36:36Z")
You can't it's only an update/insert process.
I'm not sure how you can get around this.
---
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [April 18, 2016, 2:54am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/5 "2016-04-18T02:54:04Z")
Thanks
---
### Author:  [@laoyang360](https://discuss.elastic.co/u/laoyang360)
#### Post date: [June 22, 2016, 8:53am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/6 "2016-06-22T08:53:11Z")
I also have this problem, But I don't know how to resolve it?
Any help would be sincerely appreciate!
---
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [June 22, 2016, 10:15am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/7 "2016-06-22T10:15:12Z")
You can add a "status" column, and flag the entry as deleted, instead of deleting the entry.
---
### Author:  [@laoyang360](https://discuss.elastic.co/u/laoyang360)
#### Post date: [June 23, 2016, 1:23am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/8 "2016-06-23T01:23:34Z")
> [@suntuo](#):
>
> where tablename.update\_time \> :sql\_last\_value"
thank you!
I have one question about what you said above:
when I use sql like above
"where tablename.update\_time \> :sql\_last\_value"
The below bug appeard:
Exception when executing JDBC query {:exception=\>#\, :level=\>:warn}
Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: Unknown column 'cc.update\_time' in 'where clause': SELECT count(\*) AS `count` FROM (select
\*
cc is my table name, update\_time is not exist, can you help me?
thanks !
---
### Author:  [@laoyang360](https://discuss.elastic.co/u/laoyang360)
#### Post date: [June 23, 2016, 1:52am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/9 "2016-06-23T01:52:57Z")
First, you know, In mysql we just use delete from \* to delete some date we don't use.
Second, We want to sync the delete information, as you say, "add a "status" column", what you say is 🙂
1. in mysql, just add a status column to indetify the date is ok or not, just like:
mysql\> select \* from cc;
+----+--------------------+--------+
| id | name | status |
+----+--------------------+--------+
| 1 | laoyang360 | ok |
| 4 | intok | ok |
| 5 | jdbc\_test\_update08 | ok |
| 7 | test7 | ok |
| 8 | test008 | ok |
| 9 | test9 | ok |
| 10 | test10 | ok |
+----+--------------------+--------+
7 rows in set (0.00 sec)
mysql\>
mysql\>
mysql\> update cc set status = 'deleted' where id = 10;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql\> select \* from cc;
+----+--------------------+---------+
| id | name | status |
+----+--------------------+---------+
| 1 | laoyang360 | ok |
| 4 | intok | ok |
| 5 | jdbc\_test\_update08 | ok |
| 7 | test7 | ok |
| 8 | test008 | ok |
| 9 | test9 | ok |
| 10 | test10 | deleted |
+----+--------------------+---------+
7 rows in set (0.00 sec)
[2.In](http://2.In) elasticsearch, revise the data status, I think in elasticsearch revise is very difficult.
I want to listen your advise,
---
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [June 23, 2016, 2:12am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/10 "2016-06-23T02:12:49Z")
> [@suntuo](#):
>
> The existing entries in Elasticsearch with the same ID will be overwrite when i update data on mysql.
you can add a timestamp column, as you modify the status the timestamp value will be updated, Using paramter tablename.timestamp \> :sql\_last\_value", logstash will only update the entry which timestamp has been updated.
---
### Author:  [@laoyang360](https://discuss.elastic.co/u/laoyang360)
#### Post date: [June 23, 2016, 4:10am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/11 "2016-06-23T04:10:55Z")
Thanks. I already added timestamp column named modified\_at, just like:
mysql\> select \* from cc;
+----+--------------------+---------+---------------------+
| id | name | status | modified\_at |
+----+--------------------+---------+---------------------+
| 10 | test10 | deleted | 0000-00-00 00:00:00 |
| 11 | test1111 | ok | 2016-06-23 04:10:00 |
| 12 | test012 | ok | 2016-06-23 04:21:56 |
+----+--------------------+---------+---------------------+
and the sql is 🙂
[root@5b9dbaaa148a logstash\_jdbc\_test]# cat jdbc.sql
select
\*
from
cc
where cc.modified\_at \> :sql\_last\_value
and the result is
{"id":11,"name":"test1111","status":"ok","modified\_at":"2016-06-23T03:10:00.000Z","@version":"1","@timestamp":"2016-06-23T04:10:00.185Z","type":"cc\_type"}
{"id":12,"name":"test012","status":"ok","modified\_at":"2016-06-23T03:21:56.000Z","@version":"1","@timestamp":"2016-06-23T04:10:00.187Z","type":"cc\_type"}
---
### Author:  [@chrisribe](https://discuss.elastic.co/u/chrisribe)
#### Post date: [January 18, 2017, 8:47pm UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/12 "2017-01-18T20:47:25Z")
Hi,
I get that you need to flag / log the delete operation and I get that done via triggers in mysql.
But how can I instruct the logstash-jdbc-input plugin to delete the given document ids ??
Seems a simple task for the plugin no?
Thanks
Chris
---
### Author:  [@suntuo](https://discuss.elastic.co/u/suntuo)
#### Post date: [January 19, 2017, 1:43am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/13 "2017-01-19T01:43:48Z")
> [@warkolm](#):
>
> You can't it's only an update/insert process.
You can add a "status" column, and flag the entry as deleted, instead of deleting the entry.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 4:29am UTC](https://discuss.elastic.co/t/delete-elasticsearch-document-with-logstash-jdbc-input/47490/14 "2017-07-06T04:29:17Z")