There seems to be some problem when indexing Mysql data in ES. These are
the logs of ES:
[DEBUG][NodeClient ] after bulk [18650] [succeeded=93255]
[failed=0] [5ms]
[DEBUG][NodeClient ] before bulk [18654] of 5 items, 2407
bytes, 1 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18651] [succeeded=93260]
[failed=0] [3ms]
[DEBUG][NodeClient ] after bulk [18653] [succeeded=93265]
[failed=0] [3ms]
[DEBUG][NodeClient ] before bulk [18655] of 5 items, 2381
bytes, 1 outstanding bulk requests
[DEBUG][NodeClient ] before bulk [18656] of 5 items, 2357
bytes, 2 outstanding bulk requests
[DEBUG][NodeClient ] before bulk [18657] of 5 items, 2399
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18656] [succeeded=93270]
[failed=0] [3ms]
[DEBUG][NodeClient ] before bulk [18658] of 5 items, 2242
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18655] [succeeded=93275]
[failed=0] [5ms]
[DEBUG][NodeClient ] before bulk [18659] of 5 items, 2316
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18658] [succeeded=93280]
[failed=0] [2ms]
[[DEBUG][NodeClient ] after bulk [18657] [succeeded=93285]
[failed=0] [4ms]
[DEBUG][NodeClient ] before bulk [18660] of 5 items, 2336
bytes, 2 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18654] [succeeded=93290]
[failed=0] [7ms]
[DEBUG][NodeClient ] before bulk [18661] of 5 items, 2202
bytes, 2 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18659] [succeeded=93300]
[failed=0] [3ms]
[DEBUG][NodeClient ] before bulk [18662] of 5 items, 2234
bytes, 1 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18660] [succeeded=93295]
[failed=0] [2ms]
[DEBUG][NodeClient ] before bulk [18663] of 5 items, 2176
bytes, 2 outstanding bulk requests
[DEBUG][NodeClient ] before bulk [18664] of 5 items, 2332
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18661] [succeeded=93305]
[failed=0] [7ms]
[DEBUG][NodeClient ] before bulk [18665] of 5 items, 2275
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18662] [succeeded=93310]
[failed=0] [8ms]
[DEBUG][NodeClient ] before bulk [18666] of 5 items, 2358
bytes, 3 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18664] [succeeded=93315]
[failed=0] [4ms]
[DEBUG][NodeClient ] after bulk [18665] [succeeded=93320]
[failed=0] [2ms]
[DEBUG][NodeClient ] before bulk [18667] of 5 items, 2307
bytes, 2 outstanding bulk requests
[DEBUG][NodeClient ] after bulk [18663] [succeeded=93325]
[failed=0] [6ms]
[DEBUG][org.xbib.elasticsearch.river.jdbc.strategy.simple.SimpleRiverSource]
merged 93338 rows
Though the total count looks fine but I can see count in my index which is
exactly half of what it is showing(93338). Any idea what is wrong here ?
Also, I can see some outstanding bulk requests which I think is creating
problem:
Here are the params which I'm using in my jdbc:
"fetchsize" : 100,
"maxconcurrrentbulkactions" : 5,
"maxbulkactions" : 5,
"max_retries" : 15,
"autocommit" : true
I tried changing these params to avoid above outstanding bulk requests but
no help.
Thanks,
On Tue, May 20, 2014 at 11:32 PM, joergprante@gmail.com <
joergprante@gmail.com> wrote:
-
I'd be thankful if you can post an issue on the JDBC river github page
with a (small) example where you can demonstrate that JDBC river is not
able to index all data. In my own projects I index millions of rows without
skipping any data. There are many possible reasons why this can happen but
I can try to track down such issues. A start would be to increase log level
to DEBUG and follow the messages JDBC river is writing about the number of
indexed documents. This can easily compared to the database rows (assuming
you transfer the primary key of a table to the _id field)
-
MySQL functions that return binary data (non-UTF-8) can not be indexed
into ES without base64 encoding. Maybe you should switch character set of
the JDBC MySQL connection URL if you are unsure that you really get UTF-8
from the database.
Jörg
On Tue, May 20, 2014 at 7:00 PM, Mukul Gupta mukulnitkkr@gmail.comwrote:
Yeah..I switched to JDBC 1.1.0.2 and it is now updating indexed docs but
still few issues are there.
-
Though JDBC can now index docs but not all Mysql docs are getting
index. I tried palying with
maxbulkactions - the length of each bulk index request submitted
maxconcurrrentbulkactions - the maximum number of concurrent bulk
requests
but not able to solve the issue. I thought might be some race condition
is causing the issue but seems like there is any other issue. Do you have
any idea what can be the possible reason ?
-
In order to index my Mysql documents in ES I'm using a sql query which
uses CONCAT_WS() function of Mysql in order to create a concatenated string
with _ but it seems like something is wrong with river because it's
indexing the docs with that same value. Its showing something like
23nshf3-r404053== (this kind of random value). Any idea is there any bug
with CONCAT_WS() function of Mysql ?
Thanks
On Sun, May 18, 2014 at 3:00 PM, joergprante@gmail.com <
joergprante@gmail.com> wrote:
-
There is a missing refresh step in JDBC 1.1.0.1, this will be fixed
in 1.1.0.2. It means the indexes written to are not refreshed after
indexing, until the river is deleted. You do not have to restart a node
though, just refresh before search.
-
Each river name has different mappings so there should be no mapping
conflicts between river definitions. Do not use the same river name for
JDBC and MongoDB river. In doubt, you can delete all rivers with curl
-XDELETE 'localhost:9200/_river' and recreate the river index by curl -XPUT
'localhost:9200/_river' before creating clean JDBC river and MongoDB river
instances with different names.
Jörg
On Sun, May 18, 2014 at 8:25 AM, Mukul Gupta mukulnitkkr@gmail.comwrote:
Thanks Jörg. I'm able to index my mysql data but there are few issues
which I'm facing:
-
Whenever I create an index with mapping and setting and then create
river instance using above configuration, jdbc river is not immediately
adding mysql data into ES. I need to restart my ES to see mysql data to be
added in my ES. Is this normal behaviour because in production it can be a
big problem. Everytime you add some data, you need to restart you ES.
-
I'm having one more river instance for indexing my mongo data into
ES. Actually half of my data is in mongo and other half is in Mysql. I know
that is weird but can't do anything. Requirement needs that. So, my point
is I'm not able to run these two rivers simultaneously because I'm getting
Mappingparsing Exception. Reason is I guess, the way I'm giving index and
type is like this:
"index":{
"index" : INDEX,
"type" : TYPE
}
which is not same as given in jdbc river. so it is giving an error:
org.elasticsearch.index.mapper.MapperParsingException: object mapping
for [INDEX] tried to parse as object, but got EOF, has a concrete value
been provided to it?
So, how can I tackle this problem, any idea ? Should I use your
previous versions which supports same types of index paradigm. But that way
I'll be losing some of the features.
Looking forward for your help.
Thanks
On Sun, May 18, 2014 at 1:25 AM, joergprante@gmail.com <
joergprante@gmail.com> wrote:
For selecting an index/type, you can use
curl -XPUT "localhost:9200/_river/INDEX/_meta" -d '
{
"type": "jdbc",
"jdbc": {
"url": "jdbc:mysql://localhost:3306/sapphire",
"driver": "com.mysql.jdbc.Driver",
"user" : "root",
"password": "root",
"sql" : "select * from flight",
"index" : "INDEX",
"type" : "TYPE"
}
}'
Jörg
On Sat, May 17, 2014 at 4:42 PM, Mukul Gupta mukulnitkkr@gmail.comwrote:
Not an issue. I upgraded my ES to 1.1.0 anf mongo river to 2.0.0.
Mongo river worked fine but jdbc river 1.1.0.1 is not working for me. I'm
not able to import Mysql data into my index though I'm able to create
mappings for it. But jdbc river is not importing any data into index. I
followed this sample:
curl -XPUT 'localhost:9200/_river/my_jdbc_river/_meta' -d '{
"type" : "jdbc",
"jdbc" : {
"url" : "jdbc:mysql://localhost:3306/test",
"user" : "",
"password" : "",
"sql" : "select * from orders"
}
}'
But not able to import data into a particular index.
Can you please tell me how can I give option for index and type in
the above mapping. For example, for index INDEX and TYPE I tried using this:
curl -XPUT "localhost:9200/_river/INDEX/_meta" -d '
{
"type": "jdbc",
"jdbc": {
"url": "jdbc:mysql://localhost:3306/sapphire",
"driver": "com.mysql.jdbc.Driver",
"user" : "root",
"password": "root",
"sql" : "select * from flight"
},
"index": {
"index" : "INDEX",
"type" : "TYPE"
}
}'
But it is not working for me. Can anyone point out my mistake here.
Also, I'm using a mapping for the above index. Mapping is getting
created properly but data is not getting imported into it.
Thanks
On Sat, May 17, 2014 at 3:05 PM, joergprante@gmail.com <
joergprante@gmail.com> wrote:
Only latest ES 0.90.x (currently 0.90.13) is possible to support for
JDBC river because of important bugfixes.
I'd have to build a custom version of JDBC river for 0.90.13
Jörg
On Sat, May 17, 2014 at 8:36 AM, Mukul Gupta mukulnitkkr@gmail.comwrote:
Can we use jdbc river plugin for older versions of ES 0.90.3 ?
On Sat, May 17, 2014 at 2:50 AM, joergprante@gmail.com <
joergprante@gmail.com> wrote:
Rivers did not change since ES 1.0.0 so I'm quite confident that
https://github.com/richardwilly98/elasticsearch-river-mongodb2.0.0 will run with ES 1.1.0
Jörg
On Fri, May 16, 2014 at 6:57 PM, coder mukulnitkkr@gmail.comwrote:
Hi,
I need to index documents from mysql to ES. I read about jdbc
river but it is compatible for ES 1.1.0. My problem is I'm using mongodb
also for indexing some documents and that too with ES version 0.90.3. I
know it's bit of a older version but that why I want to upgrade to newer
version but my problem is I'm not able to find any suitable mongo river
plugin for ES 1.1.0. Can anyone give me pointer to where I can get river
mongo plugin for ES 1.1.0 or jdbc river plugin for ES 0.90.3.
Thanks,
--
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/feff43d5-e0fe-400c-b9d1-40ebcc535422%40googlegroups.comhttps://groups.google.com/d/msgid/elasticsearch/feff43d5-e0fe-400c-b9d1-40ebcc535422%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/CAKdsXoEgtmg2pd1-b5U219tL3JY6DEZmC-g6Vb5iom7wyO8f0g%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAKdsXoEgtmg2pd1-b5U219tL3JY6DEZmC-g6Vb5iom7wyO8f0g%40mail.gmail.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/CAAVTvp5_%2B33G3VqKdjUeE%3DxPZPvzySqFy0T47BhcF3a3Wb7Z%3Dg%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAAVTvp5_%2B33G3VqKdjUeE%3DxPZPvzySqFy0T47BhcF3a3Wb7Z%3Dg%40mail.gmail.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/CAKdsXoFWBZmCwhDy3yXGx%2Bjg7tTwMRQMme9GxxGdTNF5_%3DduSA%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAKdsXoFWBZmCwhDy3yXGx%2Bjg7tTwMRQMme9GxxGdTNF5_%3DduSA%40mail.gmail.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/CAAVTvp4FYYkehJ935cwX4%3D4CYCGe9ekh%2B6RRXTrF%3DQsTS5S5ow%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAAVTvp4FYYkehJ935cwX4%3D4CYCGe9ekh%2B6RRXTrF%3DQsTS5S5ow%40mail.gmail.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/CAKdsXoG0sexcF6WB7%2BAj6zAGGoy%2B6ocLbzn%2BoAFXD1kzZrbk3A%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAKdsXoG0sexcF6WB7%2BAj6zAGGoy%2B6ocLbzn%2BoAFXD1kzZrbk3A%40mail.gmail.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/CAAVTvp4G1%3DT4QEZqFxB%2BtqqGSUEedn3Mb59Ef-SQbjXFsxK0YQ%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAAVTvp4G1%3DT4QEZqFxB%2BtqqGSUEedn3Mb59Ef-SQbjXFsxK0YQ%40mail.gmail.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/CAKdsXoEU-zPQY3PxOEku3Bew6u2PLvijVu1Kf8TOh95k%2BsCYBw%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAKdsXoEU-zPQY3PxOEku3Bew6u2PLvijVu1Kf8TOh95k%2BsCYBw%40mail.gmail.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/CAAVTvp4Ss%2BbWZcNF5gx%3DWdK8SHgCOVEw1oOvx4DhBftp_wh7jg%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAAVTvp4Ss%2BbWZcNF5gx%3DWdK8SHgCOVEw1oOvx4DhBftp_wh7jg%40mail.gmail.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/CAKdsXoFQWpKWHNZNf63%2Be6o2nJkk6aHSgqe_-cs-h14S6c_%3D-Q%40mail.gmail.comhttps://groups.google.com/d/msgid/elasticsearch/CAKdsXoFQWpKWHNZNf63%2Be6o2nJkk6aHSgqe_-cs-h14S6c_%3D-Q%40mail.gmail.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/CAAVTvp7w%2Bmw4mKxc2m%2B%2BReYarRdrEtr1dmWHmjS07DafRp-j4A%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.