Adding Data Import Handlers to ColdFusion 2016 Solr

Question

I am trying to index tables from our MySQL database on the Solr version that came with our ACF 2016 installation. Adobe's docs state that I need to use Solr's Data Import Handler to do this, which they say entails modifying solrconfig.xml and creating a data-config.xml file.

This does not work as is. Further reading leads me to believe that I need to:

1. Download a solr-dataimporthandler.jar
2. Copy ColdFusion's MySQL connector so that Solr can use it
3. Edit solrconfig.xml to account for these changes
4. Create a data-config.xml

Am I correct so far? Because I've been trying that, and when I try to reload my collection, the CF administrator gives me an error "Error handling 'reload' action." The Solr admin itself says:

org.apache.solr.common.SolrException:org.apache.solr.common.SolrException: Error loading class 'org.apache.solr.handler.dataimport.DataImportHandler'.

I don't know much about this, but it sounds to me like Solr cannot load one or both of the two jar files I added. If this is the case, then:

1. What solrconfig.xml should I be editing? I've been working on the one in the specific collection I've set up to index our database to.
2. Do I need both of those jars? Where should I put them? I have tried them in C:\ColdFusion2016\cfusion\jetty\lib and in a custom lib I set up at C:\ColdFusion2016\cfusion\jetty\multicore\lib.
3. Some sources (not Adobe) say I need to add lib directives to solrconfig.xml, while others say that any jars in a lib in Solr's "root" directory will automatically get added. I've tried both ways, and get the errors described above.
4. Still other sources say I need to add them to my classpath. I am hesitant to do this on our server if we do not need to.

I know this question is all over the place, but I have gotten myself quite confused and I would really appreciate any help or pushes in the right direction. My hope is that I am just making some dumb mistakes somewhere, because I don't think it should be this complicated!

Note that Solr itself is running fine and some collections I have set up that index directories of PDFs are working, no troubles. None of the solrconfig.xml files in the other collections have any request handlers or libs referring to data import handlers.

Thanks in advance! I appreciate your reading all of this! :-)

Answer 1

Okay so I finally got it working. As suspected, the root issue was Solr not locating the MySQL connector and data import handler jars. For the benefit of any others who might stumble across this, here is what I did. We are using Adobe ColdFusion 2016 and Solr 5.2.1 that shipped with ACF.

1. You do need the MySQL connector and data import handler jars. I used a version-matched data import handler called solr-dataimporthandler-5.2.1.jar and I downloaded it from here. Make sure you select the jar file in the "files" section to start the download. For the MySQL connector, I just copied the one that came from ColdFusion. Mine was called mysql-connector-java-5.1.38-bin.jar, and for my CF install, it was located at C:\ColdFusion2016\cfusion\lib.

2. I learned that Solr will automatically look for jars if they are in a particular place. No need for lib directives or any file editing. For me, I created a folder called "lib" in my Solr instance which is at C:\ColdFusion2016\cfusion\jetty\multicore and I put the jars in there. So the full path to the new jars is C:\ColdFusion2016\cfusion\jetty\multicore\lib, but you do not need to edit any file to account for that.

3. You do need to edit solrconfig.xml to account for the data import handler. For me, the only way I could get this to work was to edit solrconfig.xml for each collection. Editing any of the various other versions would not work. So for my collection called "dmfile," which I had previously created in the CF Admin, the solrconfig.xml to edit was at C:\ColdFusion2016\cfusion\jetty\multicore\collections\vfs_dmfile\conf. I added the following to the file in the section where the other request handlers were:

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

That's all I needed to do for solrconfig.xml.

4. In that same directory, create a data-config.xml file. Here's mine:

<dataSource type="JdbcDataSource" 
    driver="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/myDatabase" 
    user="myUsername" 
    password="myPassword"/>\
<document>
    <entity name="dmfile" query="SELECT filename, ObjectID from dmfile WHERE status = 'approved'">
        <field column="filename" name="filename" />
        <field column="ObjectID" name="uid" />
        <field column="status" name="dmfile_status" />
    </entity>
</document>

To get started, I tried to keep things simple. Note how the entity name matches the name of the collection. I matched the unique ID from our database table (ObjectID) to the standard unique ID field that Solr has (uid). "Column" is the column from our database and "name" is whatever name I want Solr to use. Your database of course will likely be different.

5. Finally I edited schema.xml, also in the same directory:

   <field name="filename" type="string" indexed="true"  stored="true" required="false" />
   <field name="dmfile_status" type="string" indexed="true"  stored="true" required="false" />
   

The "name" attribute needs to match whatever you set in data-config.xml. Note that I did not add a field for uid -- it was already in schema.xml by default.

6. I am on a Windows server, so I went to services.msc and restarted the ColdFusion 2016 Add-On Services service. NOTE: restarting ColdFusion itself did not work for me. I needed to restart ColdFusion 2016 Add-On Services, and only that.

7. Finally I could reload my collection, and (more importantly) I could browse the core in the Solr admin at http://localhost:8989/solr/#/. I could select my dmfile core in the "Core Selector" dropdown, and was able to choose the DataImportHandler without getting an error.

That is how I got it to work for me. I found that I needed to repeat steps 3-6 for every core that I wished to connect to MySQL. Some documentation states that you can do at least Step 3 at a global level, but that did not work for me at all.

Anyways, it took me quite a while to figure all of that out, so hopefully this will help any other CFers out there who were stuck like I was.