# River JDBC - some difficulties to work with "table" strategy

**URL:** <https://discuss.elastic.co/t/river-jdbc-some-difficulties-to-work-with-table-strategy/13107>\
**Category:** Elasticsearch\
**Created:** [August 7, 2013, 3:30pm UTC](https://discuss.elastic.co/t/river-jdbc-some-difficulties-to-work-with-table-strategy/13107 "2013-08-07T15:30:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![chenry12](https://avatars.discourse-cdn.com/v4/letter/c/f0a364/32.png) [@chenry12](https://discuss.elastic.co/u/chenry12)\
**Post date:** [August 7, 2013, 3:30pm UTC](https://discuss.elastic.co/t/river-jdbc-some-difficulties-to-work-with-table-strategy/13107/1 "2013-08-07T15:30:53Z")

</div>

Hello,

I encounter some difficulties to work with the "table" strategie in JDBC  
river

I have installed :  
ES 0.90.1 + JDBC 2.2.0

I realized the following steps:

step 1: Mysql tables creation

CREATE TABLE IF NOT EXISTS `test_prd` (  
`_index` varchar(64) NOT NULL DEFAULT '',  
`_type` varchar(64) NOT NULL DEFAULT '',  
`_id` varchar(64) NOT NULL DEFAULT '',  
`source_timestamp` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP,  
`source_operation` varchar(8) NOT NULL DEFAULT '',  
`prd_title` varchar(150) DEFAULT NULL,  
`cat_id` int(11) DEFAULT NULL,  
PRIMARY KEY (`_index`,`_type`,`_id`,`source_timestamp`,`source_operation`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS `test_prd_ack` (  
`_index` varchar(64) NOT NULL DEFAULT '',  
`_type` varchar(64) NOT NULL DEFAULT '',  
`_id` varchar(64) NOT NULL DEFAULT '',  
`target_timestamp` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE  
CURRENT\_TIMESTAMP,  
`target_operation` varchar(8) DEFAULT 'n/a',  
`target_failed` tinyint(1) DEFAULT NULL,  
`target_message` varchar(255) DEFAULT NULL,  
PRIMARY KEY (`_index`,`_type`,`_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Instead of the documentation, I added the following fields : \_index, \_type  
and \_id because the table "my\_jdbc\_river\_ack" shows PRIMARY\_KEY on absent  
fields  
([Create new page · jprante/elasticsearch-jdbc Wiki · GitHub](https://github.com/jprante/elasticsearch-river-jdbc/wiki/Updates-with-database-table))

Step 2:  
I added two insert in the table "test\_prd":

INSERT INTO `pal`.`test_prd` (`_index` ,`_type` ,`_id` ,`source_timestamp`  
,`source_operation` ,`prd_title` ,`cat_id`)  
VALUES ('qml', 'card', '1',CURRENT\_TIMESTAMP , 'create', 'title1', '100');

INSERT INTO `pal`.`test_prd` (`_index` ,`_type` ,`_id` ,`source_timestamp`  
,`source_operation` ,`prd_title` ,`cat_id`)  
VALUES ('qml', 'card', '1',CURRENT\_TIMESTAMP , 'create', 'title2', '101');

Step 3:  
I created the mapping associated to the table "test\_prd"

curl -XPUT '[http://localhost:9200/qml/card/\_mapping](http://localhost:9200/qml/card/_mapping)' -d '  
{  
"card" : {  
"properties" : {  
"prd\_title" : {"type" : "string"},  
"cat\_id" : {"type" : "integer"}  
}  
}  
}'

Step 4:  
I created the jdbc river using the "table" strategy:

curl -XPUT 'localhost:9200/\_river/test\_prd/\_meta' -d '  
{  
"type" : "jdbc",  
"jdbc" : {  
"driver" : "com.mysql.jdbc.Driver",  
"url" : "jdbc:mysql://localhost:3306/pal",  
"user" : "root",  
"password" : "abc",  
"poll" : "10s",  
"strategy" : "table"  
},  
"index" : {  
"index" : "qml",  
"type" : "card",  
"bulk\_size" : 100,  
"max\_bulk\_requests" : 30,  
"bulk\_timeout" : "60s"  
}  
}'

* * *

The log file displays no error:

[2013-08-07 17:22:26,764][INFO][cluster.metadata] [Rankin,  
Calvin] [\_river] update\_mapping [test\_prd] (dynamic)  
[2013-08-07 17:22:26,781][INFO][river.jdbc] [Rankin,  
Calvin] [jdbc][test\_prd] starting JDBC river: URL  
[jdbc:mysql://localhost:3306/pal], driver [com.mysql.jdbc.Driver], strategy  
[table], index [qml]/[card]  
[2013-08-07 17:22:31,801][INFO][cluster.metadata] [Rankin,  
Calvin] [\_river] update\_mapping [test\_prd] (dynamic)  
[2013-08-07 17:22:31,857][INFO  
][org.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow] next run,  
waiting 10s  
[2013-08-07 17:22:41,874][INFO  
][org.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow] next run,  
waiting 10s  
[2013-08-07 17:22:51,896][INFO  
][org.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow] next run,  
waiting 10s  
[2013-08-07 17:23:01,913][INFO  
][org.elasticsearch.river.jdbc.strategy.simple.SimpleRiverFlow] next run,  
waiting 10s

* * *

I have the feeling that nothing happens.  
Nothing is updated in the tables "test\_prd" and "test\_prd\_ack"  
Nothing is inserted in elasticsearch:

> curl -XGET 'localhost:9200/qml/card/\_search'  
> {"took":5,"timed\_out":false,"\_shards":{"total":5,"successful":5,"failed":0},"hits":{"total":0,"max\_score":null,"hits":}}

Please, do you have any idea where I make a mistake ?

thanks,  
Christophe

--  
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](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 2:22am UTC](https://discuss.elastic.co/t/river-jdbc-some-difficulties-to-work-with-table-strategy/13107/2 "2017-07-06T02:22:23Z")

</div>

Closed on July 6, 2017, 2:22am UTC