Problem index date yyyy-MM-dd’T'HH:mm:ss.SSS

Elastic Stack Elasticsearch
1

Hello,

I try to indexing datetime mysql like this : 2013-05-01 00:00:00
In ES it's represented like this : 2013-05-01T00:00:00.000Z
The real problem seems to be when I index this date : 0000-00-00 00:00:00

I have used this mapping :

"type":"date",
"format":"YYYY-MM-dd HH:mm:ss||MM/dd/yyyy||yyyy/MM/dd",
"index":"not_analyzed"

I have obtained this error :

[2014-07-02 10:11:56,503][INFO ][cluster.metadata ] [ik-test2]
[_river] update_mapping [source] (dynamic)
can not be represented as java.sql.Timestamp
java.io.IOException: java.sql.SQLException: Value '79180000-00-00 00:00:00
...

can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at
com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1102)
at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:576)
at
com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6592)
at
com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6192)
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5058)
at
org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.processRow(SimpleRiverSource.java:590)
at
org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.nextRow(SimpleRiverSource.java:565)
at
org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.merge(SimpleRiverSource.java:356)
at
org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.execute(SimpleRiverSource.java:257)
at
org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource.fetch(SimpleRiverSource.java:228)
... 3 more
[2014-07-02 10:11:56,633][WARN
][org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow]
aborting river
[2014-07-02 10:12:01,392][INFO
][org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverMouth] new
bulk [1] of [69 items], 1 outstanding bulk requests
[2014-07-02 10:12:01,437][INFO ][cluster.metadata ] [ik-test2]
[my_index] update_mapping [source] (dynamic)

Can you help me, with my problem ?

Thank to you in advance.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a572cbaa-5304-480d-9fc1-2e1783c36cea%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

As made, when I index date 0000-00-00 00:00:00 the indexing stop completly
with an error. (the begin work and stop instantly)
I have tried to put (mapping) the type : string to my date but it doesn't
work

Have you an idea to solve my problem ?

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

3

Hi Tanguy ,

How is this a valid date string - "java.io.IOException:
java.sql.SQLException: Value '79180000-00-00 00:00:00 " ?
This value cant be mapped to any date format or is valid in anyway.

Thanks
Vineth

On Wed, Jul 2, 2014 at 3:21 PM, Tanguy Bernard bernardtanguy1pro@gmail.com
wrote:

As made, when I index date 0000-00-00 00:00:00 the indexing stop completly
with an error. (the begin work and stop instantly)
I have tried to put (mapping) the type : string to my date but it doesn't
work

Have you an idea to solve my problem ?

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAGdPd5kfiLtGL6xu5LQKy9Hc1e10OhJsimHqm0Qu2XYhK%3DALag%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

4

What you can do is to set the mapping for the date field to have:

{ "type": "date", "format": "YYYY-MM-dd HH:mm:ss", "ignore_malformed":

true }

then it will just ignore those invalid dates rather than throwing an error

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAPt3XKSnThkYccGcfTCQYQ%3DJiyiQ_jS1Aq8Tmu9_4x%2Bm_XVQTg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

5

In my mysql table (type : datetime) :

| date_source |
+---------------------+
| 2008-09-15 18:29:07 |
| 2013-08-29 00:00:00 |
| 2013-07-04 00:00:00 |
| 2013-07-17 00:00:00 |
| 2013-07-17 00:00:00 |
| 0000-00-00 00:00:00 |
...
If I use a mapping (type :string)

And I index :

PUT /_river/test/_meta
{
"type" : "jdbc",
"jdbc" : {

"url" : "jdbc:mysql://ip:port/database",
"user" : "user",
"password" : "password",
"sql" : "select id_source as _id, title_source, date_source from

source", // if I add this "where date_source not like '%0000%'", it's
work but values miss for this date
"index" : "test",
"type" : "source",
"max_bulk_requests" : 5

}}

Le mercredi 2 juillet 2014 12:09:58 UTC+2, vineeth mohan a écrit :

Hi Tanguy ,

How is this a valid date string - "java.io.IOException:
java.sql.SQLException: Value '79180000-00-00 00:00:00 " ?
This value cant be mapped to any date format or is valid in anyway.

Thanks
Vineth

On Wed, Jul 2, 2014 at 3:21 PM, Tanguy Bernard <bernardt...@gmail.com
<javascript:>> wrote:

As made, when I index date 0000-00-00 00:00:00 the indexing stop
completly with an error. (the begin work and stop instantly)
I have tried to put (mapping) the type : string to my date but it doesn't
work

Have you an idea to solve my problem ?

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/1fc6c18b-a192-4972-92b6-9210be9c46aa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

6

What this date is supposed to represent?
month = 0 or day = 0 does not exist, right?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

Le 2 juillet 2014 à 12:29:29, Tanguy Bernard (bernardtanguy1pro@gmail.com) a écrit:

In my mysql table (type : datetime) :

| date_source |
+---------------------+
| 2008-09-15 18:29:07 |
| 2013-08-29 00:00:00 |
| 2013-07-04 00:00:00 |
| 2013-07-17 00:00:00 |
| 2013-07-17 00:00:00 |
| 0000-00-00 00:00:00 |
...
If I use a mapping (type :string)

And I index :

PUT /_river/test/_meta
{
"type" : "jdbc",
"jdbc" : {

"url" : "jdbc:mysql://ip:port/database",
"user" : "user",
"password" : "password",
"sql" : "select id_source as _id, title_source, date_source from source",  // if I add this "where date_source not like '%0000%'", it's work but values miss for this date
"index" : "test",
"type" : "source",
"max_bulk_requests" : 5

}}

Le mercredi 2 juillet 2014 12:09:58 UTC+2, vineeth mohan a écrit :
Hi Tanguy ,

How is this a valid date string - "java.io.IOException: java.sql.SQLException: Value '79180000-00-00 00:00:00 " ?
This value cant be mapped to any date format or is valid in anyway.

Thanks
Vineth

On Wed, Jul 2, 2014 at 3:21 PM, Tanguy Bernard bernardt...@gmail.com wrote:
As made, when I index date 0000-00-00 00:00:00 the indexing stop completly with an error. (the begin work and stop instantly)
I have tried to put (mapping) the type : string to my date but it doesn't work

Have you an idea to solve my problem ?

You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/57d83657-a032-4ed8-8236-143a8e44c5fc%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/1fc6c18b-a192-4972-92b6-9210be9c46aa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/etPan.53b3e1b3.431bd7b7.f8fb%40MacBook-Air-de-David.local.
For more options, visit https://groups.google.com/d/optout.

7

This date is created when a document is created, but an error occur and I
have this 0000-00-00 ^^
I'm in company while exist since 10 years, the database is old and they are
this kind of error.

For the moment, I will use :

"sql" : "select id_source as _id, title_source, date_source from source", //
if I add this "where date_source not like '%0000%'", it's work but values
miss for this date
Or not index date_source. My goal was to sort my result with date_source.

Le mercredi 2 juillet 2014 12:40:58 UTC+2, David Pilato a écrit :

What this date is supposed to represent?
month = 0 or day = 0 does not exist, right?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/6eca7137-875f-47e3-8719-537ed5ad0310%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

8

I would recommend updating the SQL database! :slight_smile:

So may be update all dates where date is 0000-00-00 to 1970-01-01 if it fits with your use case.

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

Le 2 juillet 2014 à 12:54:36, Tanguy Bernard (bernardtanguy1pro@gmail.com) a écrit:

This date is created when a document is created, but an error occur and I have this 0000-00-00 ^^
I'm in company while exist since 10 years, the database is old and they are this kind of error.

For the moment, I will use :

"sql" : "select id_source as _id, title_source, date_source from source", // if I add this "where date_source not like '%0000%'", it's work but values miss for this date
Or not index date_source. My goal was to sort my result with date_source.

Le mercredi 2 juillet 2014 12:40:58 UTC+2, David Pilato a écrit :
What this date is supposed to represent?
month = 0 or day = 0 does not exist, right?

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet | @elasticsearchfr

You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/6eca7137-875f-47e3-8719-537ed5ad0310%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/etPan.53b3e561.628c895d.f8fb%40MacBook-Air-de-David.local.
For more options, visit https://groups.google.com/d/optout.

9

Yes, it's just some date. I think that it can be update quickly. It's the
better way :slight_smile:
Thank you all.

Le mercredi 2 juillet 2014 12:56:59 UTC+2, David Pilato a écrit :

I would recommend updating the SQL database! :slight_smile:

So may be update all dates where date is 0000-00-00 to 1970-01-01 if it
fits with your use case.

--
David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr

Le 2 juillet 2014 à 12:54:36, Tanguy Bernard (bernardt...@gmail.com
<javascript:>) a écrit:

This date is created when a document is created, but an error occur and I
have this 0000-00-00 ^^
I'm in company while exist since 10 years, the database is old and they
are this kind of error.

For the moment, I will use :

"sql" : "select id_source as _id, title_source, date_source from source", //
if I add this "where date_source not like '%0000%'", it's work but values
miss for this date
Or not index date_source. My goal was to sort my result with date_source.

Le mercredi 2 juillet 2014 12:40:58 UTC+2, David Pilato a écrit :

What this date is supposed to represent?
month = 0 or day = 0 does not exist, right?

 --

David Pilato | Technical Advocate | Elasticsearch.com
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr

--
You received this message because you are subscribed to the Google Groups
"elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/6eca7137-875f-47e3-8719-537ed5ad0310%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/6eca7137-875f-47e3-8719-537ed5ad0310%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/bb21d146-0094-4765-baf5-9232977ad4e8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.