Unable to retrieve any rows using SOLR DataImportHandler from DB

Question

I have a simple test database for my first attempt to index database data using SOLR DIH. Unfortunately, I get the following result from a full import every time:

<response> <lst name="responseHeader"> <int name="status">0</int> <int name="QTime">15</int> </lst> <lst name="initArgs"> <lst name="defaults"> <str name="config">data-config.xml</str> </lst> </lst> <str name="command">full-import</str> <str name="status">idle</str> <str name="importResponse"/> <lst name="statusMessages"> <str name="Time Elapsed">0:0:2.187</str> <str name="Total Requests made to DataSource">1</str> <str name="Total Rows Fetched">0</str> <str name="Total Documents Processed">0</str> <str name="Total Documents Skipped">0</str> <str name="Full Dump Started">2011-03-06 21:30:07</str> <str name="">Indexing failed. Rolled back all changes.</str> <str name="Rolledback">2011-03-06 21:30:07</str> </lst> <str name="WARNING"> This response format is experimental. It is likely to change in the future. </str> </response> 

In my solrconfig.xml file, the following requestHandler is specified:

 <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler"> <lst name="defaults"> <str name="config">data-config.xml</str> </lst> </requestHandler> 

my data-config.xml contains the following:

 <dataConfig> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:8889/Dev1" user="root" password="***"/> <document> <entity name="business_profile" query="select business_id,business_name,address1,address2,city,state,zip from business_profile"> </entity> </document> </dataConfig> 

schema.xml scope definition:

 <field name="business_id" type="int" indexed="true" stored="true"/> <field name="business_name" type="string" indexed="true" stored="true"/> <field name="address1" type="string" indexed="true" stored="true"/> <field name="address2" type="string" indexed="true" stored="true"/> <field name="city" type="string" indexed="true" stored="true"/> <field name="state" type="string" indexed="true" stored="true"/> <field name="zip" type="string" indexed="true" stored="true"/> 

If general queries to the data source display “1”, does this mean that the JDBC driver is configured correctly, and is this a possible problem with MySQL permissions at this point or not?

I played with the DIH dev console trying to return something, but always 1 query and 0 rows, which makes me think about JDBC or mySQL permissions. I excluded the DB name, port #, and user / pass, but maybe the JDBC driver is not installed correctly.?

thanks

---

This is what I get from solr magazine

 0:0:0:0:0:0:0:1%0 - - [07/03/2011:17:50:41 +0000] "GET /solr/dataimport?command=full-import&mode=debug HTTP/1.1" 200 853 0:0:0:0:0:0:0:1%0 - - [07/03/2011:17:50:47 +0000] "GET /solr/dataimport?command=full-import&mode=debug HTTP/1.1" 200 851 0:0:0:0:0:0:0:1%0 - - [07/03/2011:17:51:03 +0000] "GET /solr/dataimport?command=full-import&mode=debug HTTP/1.1" 200 853 0:0:0:0:0:0:0:1%0 - - [07/03/2011:17:51:13 +0000] "GET /solr/dataimport?command=full-import&mode=debug HTTP/1.1" 200 852 0:0:0:0:0:0:0:1%0 - - [07/03/2011:17:51:14 +0000] "GET /solr/dataimport?command=full-import&mode=debug HTTP/1.1" 200 852 

Answer 1

Look at the SOLR logs, it will print a stack when indexing fails.

Answer 2

Please run the query from data-config.xml directly in MySQL and check if any rows are returned.

Also, check your server’s error logs (catalina.out if you are using Tomcat) and post error messages there.

Answer 3

You, of course, added the fields business_id, business_name, address1, address2, city, state, zip to your schema.xml?

Have you found errors in the server log?

I'm not sure if you need to specify a data source if you have only one, but a way to tell the object which data source to use:

 <dataConfig> <dataSource name="dev" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" encoding="UTF-8" url="jdbc:sqlserver://____:1433;databaseName=____" user="____" password="____" readOnly="true" autoCommit="false" /> <document> <entity name="metadataObject" dataSource="dev" pk="ITEM_MOID" transformer="RegexTransformer,DateFormatTransformer" query="___" ... </document> </dataConfig> 

Answer 4

The data is config.xml, which you publish here, at url = "jdbc: mysql: // localhost: 8889 / Dev1". Pls change it to url = "jdbc: mysql: // localhost / Dev1" and you put the jdbc driver in the lib directory. check the jdbc driver where it is in the lib directory or not ...

Answer 5

This is a long snapshot, but based on your Solr logs, ipv6 is enabled on your server.

Some applications do not support ipv6 by default, you can check your MySQL configuration to make sure that it allows connections via ipv6 on the loopback interface.

See the MySQL version 5.5 manual, for example: http://dev.mysql.com/doc/refman/5.5/en/ipv6-local-connections.html

In particular, steps 3 and 4 in the above guide can be used to test connectivity, for example

 shell> mysql -h localhost -u root -pYourRootPassword mysql> STATUS 

Answer 6

I had a similar problem. If your schema.xml (or managed-schema) contains an identifier or a unique identifier, as shown below Example: <field name="id" type="string" indexed="true" stored="true" required="true" multiValued="false" />

Then you need to select this file in the data-config.xml file as the main key or set a new key with the required = "true" or delete this part.

Answer 7

Are you connecting to remote db using ssh or tunnel? it seems to me that you are connecting to a remote db. try connecting to your local mysql server, if that works, then you will have a connection problem using SSH.