Apache Solr MySQL Sample Data Config

ApacheSolr is an indexing server from apache. It is written in Java and can be used to make search pages on your website. Most Websites use a MySQL database to store its data and standard MySQL search using SQL or FULLTEXT Searching by MySQL. But with ApacheSolr we can not only search but also improve the search results. Solr recognizes plurals and similar words like read, reading or A-DATA, adata etc. Therefore Solr returns more efficient search results.

Download the JDBC Driver

Download the JDBC Driver for MySQL from http://mysql.spd.co.il/Downloads/Connector-J/mysql-connector-java-3.1.14.zip Put the "mysql-connector-java-3.1.14-bin.jar" in

Solr Dir/example/lib

Configuring Solr to Use MySQL

Solr can be configured to connect to a MySQL Data base using the DataImportHandler. To do so first in

solrconfig.xml

(This file would be in

Solr Dir/example/conf

) add a new requestHandler which is handled by DIH (DataImportHandler).

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

Create a new file called data-config.xml in the same directory and configure the database connection and table schema to reflect your database structure. Sample data-config.xml

<dataConfig>
	<dataSource type="JdbcDataSource"
   driver="com.mysql.jdbc.Driver"
   url="jdbc:mysql://SERVER/DATABASE"
   user="USERNAME" 
   password="PASSWORD"/>
	<document name="content">
        <entity name="node" query="select node.nid AS nid,node_revisions.body AS body,node_revisions.title AS title from node,node_revisions where node.status = 1 and node.nid = node_revisions.nid and node.vid = node_revisions.vid">
            <field column="nid" name="id" />
            <field column="body" name="body" />
            <field column="title" name="title" />
        </entity>
    </document>
</dataConfig>

The dataSource configuration attributes , query in entity tag and database fields must be modified to match your database structure. The Query given in the example is a simple Join of the drupal node and node revisions tables. Now restart or start ApacheSolr using java -jar start.jar. Hit the full-commit url (http://SERVER:PORT/solr/dataimport?command=full-import) and your website would start getting indexed.

<response>

<lst name="responseHeader">
<int name="status">0</int>
<int name="QTime">0</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="Total Requests made to DataSource">1</str>
<str name="Total Rows Fetched">1056</str>
<str name="Total Documents Skipped">0</str>
<str name="Full Dump Started">2010-02-22 14:46:35</str>

<str name="">
Indexing completed. Added/Updated: 1056 documents. Deleted 0 documents.
</str>
<str name="Committed">2010-02-22 14:46:42</str>
<str name="Optimized">2010-02-22 14:46:42</str>
<str name="Total Documents Processed">1056</str>
<str name="Time taken ">0:0:6.562</str>
</lst>

<str name="WARNING">
This response format is experimental.  It is likely to change in the future.
</str>
</response>

N documents fetched 0 documents processed

If you get this problem when SOLR says none of them are indexed, Then your input data did not have one more required columns specified in the fields in schema.xml. Recheck your SQL and provide all required fields, or make them optional or remove them. 22 Feb, 2010

Related Searches
solr jdbc mysql
apache codeigniter allowoverride
intel 8086 sample assembly program
sample predictive text dictionary file
dreamhost add mysql database to different server
free mysql injection book

getting solr 404

i cant get the answer. i follow the all the steps . but i got an error page not found " error 404".

By Solr data import problem on 22 Dec, 2011
- Check your solr path in solr-config.xml
  
  Find where /select comes, if you are running in multi core mode, your URL would look like /core1/solr/select/?
  
  By digitalpbk on 25 Dec, 2011

How can I do it for multiple queries???

My indexing is working fine and now I am able to search it form php.
But the indexing I created was done on the data fetched by one query only or you can say one table.

How can I do it for multiple queries???
I mean create indexes for data of multiple queries and how to them find them using php??

Thanks in advance
Jawed

By Jawed Shamshedi on 15 Nov, 2011
- Hi. I am running into the same problem but with Oracle db. Total rows fetched shows 65 but Total documents processed is 0. So i am not able to search. Please let me also know where was the problem. Thanks in advance.
  
  By myQuest on 25 Dec, 2011
  - Check your required fields
    
    Check if your SQL returns all required fields as specified in schema.xml
    
    By digitalpbk on 25 Dec, 2011
  - Hi. Its working fine for me now. Looks like the column names in data-config file should be in caps as Oracle returns it that way.
    
    By myQuest on 28 Dec, 2011
    - myQuest, you mesed up Jaweds question. You are not running into the same problem, Jawed Shamshedi mentions about completely different issue. He wants to index multiple queries and make requests on different indexes.
      
      By shamshedi on 07 Feb, 2012

I did not put the jdbc driver
Hi, Sorry I did not put the jdbc driver to I got the prev error, But now I am getting this error. <response> − <lst name="responseHeader"> <int name="status">0</int> <int name="QTime">0</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="Total Requests made to DataSource">1</str> <str name="Total Rows Fetched">100</str> <str name="Total Documents Skipped">0</str> <str name="Full Dump Started">2011-11-11 17:09:24</str> − <str name=""> Indexing completed. Added/Updated: 0 documents. Deleted 0 documents. </str> <str name="Committed">2011-11-11 17:09:25</str> <str name="Optimized">2011-11-11 17:09:25</str> <str name="Total Documents Processed">0</str> <str name="Time taken ">0:0:0.110</str> </lst> − <str name="WARNING"> This response format is experimental. It is likely to change in the future. </str> </response> The documents are not being created. Please help Thanks
By jawed shamshedi on 11 Nov, 2011
- Check your query
  
  Does your SQL query give information when you execute it ?
  
  By digitalpbk on 11 Nov, 2011
  - Yes, when I execute it in phpmyadmin, it returns exact result.
    Where else should I execute it???
    
    And thanks for the reply.
    
    By Jawed Shamshedi on 11 Nov, 2011

Solr data import problem
Hi, I am doing the same thing and for the first time I am using Solr. I added the below code in the following file. c:/solr/example/solr/conf/solarconfig.xml <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler"> <lst name="defaults"> <str name="config">data-config.xml</str> </lst> </requestHandler> after node. and create a file data-config.xml in the same location with the following code <dataConfig> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/stockdatabase" user="root" password=""/> <document name="content"> <entity name="node" query="select ST_StockID,ST_StockCode,ST_Name,ST_ItemDetail from stock where estatus = 'Active' limit 100"> <field column="ST_StockID" name="ST_StockID" /> <field column="ST_StockCode" name="ST_StockCode" /> <field column="ST_Name" name="ST_Name" /> <field column="ST_ItemDetail" name="ST_ItemDetail" /> </entity> </document> </dataConfig> But no record is being fetch. After I hit the below given url. http://{my_local_IP}:8983/solr/dataimport?command=full-import I get the following result. <response> − <lst name="responseHeader"> <int name="status">0</int> <int name="QTime">0</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:0.234</str> <str name="Total Requests made to DataSource">0</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-11-11 16:21:08</str> <str name="">Indexing failed. Rolled back all changes.</str> <str name="Rolledback">2011-11-11 16:21:08</str> </lst> − <str name="WARNING"> This response format is experimental. It is likely to change in the future. </str> </response> Apart from the configuration that you mentioned in you post, I haven't made any other changes. Please help. Thanks
By Jawed Shamshedi on 11 Nov, 2011

This Document is not brief about how to connect(config) solr with Mysql,to get brief idea we need to go through instructions of readme file of solr dir\example\example-DIH...

By mukthyar on 05 Nov, 2011

query

I have done all configuration according to above instruction but get error:
<response>
−
<lst name="responseHeader">
<int name="status">0</int>
<int name="QTime">9</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:11:12.766</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-08-09 15:40:55</str>
<str name="">Indexing failed. Rolled back all changes.</str>
<str name="Rolledback">2011-08-09 15:42:35</str>
</lst>
−
<str name="WARNING">
This response format is experimental. It is likely to change in the future.
</str>
</response>

please help me............

By rahul on 09 Aug, 2011

Thanks

You have saved my day... Cheers!!! :-)

By Kyathi on 19 May, 2011

retrieving query results

Thnks for the tutorial. I aws able get the data indexed thorig DI, as the status say 62000 indexed, however, I am not getting any results back when I query . My config looks like this

<code>
<document>
<entity name="photo"
query="select article_id,caption,credit,file from ARTICLE_PHOTO">
<field column="article_id" name="id"/>
<field column="caption" name="caption_t"/>
<field column="desc" name="desc_t"/>
</entity>
</document>
</code>

Can you please help

By Anonymous on 25 Aug, 2010

Solr DIH

Thank you for this useful tutorial. I am facing a problem with <str name="Total Documents Failed">1</str>. Rows are fetched without any issues except for this part which i am thinking is because of this that i cannot do a query based on the fetched row from the database in the solr admin. Can you assist me what could be the problem.

Thanks.

By Andre on 28 Jul, 2010
- Same Error!
  
  Have you been able to find out the solution?
  
  I am having the same error that solr shows that it has indexed my rows but on querying it returns NO result
  
  Regards,
  Mehdi
  
  By Mehdi Hassan on 21 Jun, 2011

thnks

Thanks dude i was trying to import data from mysql to solr but there was no index getting created i checked solr logs nothing even there after reading ur post i found i have copied mysql jdbc in wrong directory now its working fine

Raj

By Raj on 05 May, 2010

Thanks

Thanks a lot man. Really good post. Was trying to find an easy to understand web page for msql data import for quite some time. Cheers!!!

By Imran on 31 Mar, 2010

Apache Solr MySQL Sample Data Config

Download the JDBC Driver

Configuring Solr to Use MySQL

N documents fetched 0 documents processed

getting solr 404

Check your solr path in solr-config.xml

How can I do it for multiple queries???

Check your required fields

I did not put the jdbc driver

Check your query

Solr data import problem

query

Thanks

retrieving query results

Solr DIH

Same Error!

thnks

Thanks