The subquery question: You can't use a sub-query, but perhaps you could use a join instead. For example:
hashJoin(jdbc(), search()) Joel Bernstein http://joelsolr.blogspot.com/ On Tue, Sep 13, 2022 at 2:36 PM James Greene <ja...@jamesaustingreene.com> wrote: > Houston, > > Placing the jdbc driver in the webapp lib folder did the > trick!!! +1 +1 +1 +1 +1 > > Do you know if it's possible to use a field value from a stream in a > 'subquery'? > > I'm hoping to take the stream source and for each record run a jdbc query > like 'delete from tbl where id = <solr.doc_id> returning ifnull(id, 0)` for > every row returned in the stream. > > > On Tue, Sep 13, 2022 at 1:19 PM Houston Putman <hous...@apache.org> wrote: > > > Is this the jira issue you are referring to: SOLR-16131 > > <https://issues.apache.org/jira/browse/SOLR-16131>? > > > > If so that got fixed in 9.0. Otherwise, even though the driver works for > > the DIH endpoint, the classloading between the jdbc streaming expression > > and DIH is likely very different. > > So try and add the JAR in > > "<solr-installation>/server/solr-webapp/webapp/WEB-INF/lib", > > it should work at that point. > > > > However, given the link above, it should work if you upgrade to Solr 9.0. > > > > - Houston > > > > On Tue, Sep 13, 2022 at 1:08 PM James Greene < > ja...@jamesaustingreene.com> > > wrote: > > > > > I found this jira bug which seems suspiciously similar to the issue i'm > > > having with the mariadb jdbc driver. @joelbernstein, what other > > location > > > should I place the jdbc client jar file to aid its ability to load when > > > using the stream endpoint: > > > https://solr.apache.org/guide/8_11/stream-source-reference.html > > > > > > > > > Mariadb jdbc driver i'm using: > > > > > > > > > https://dlm.mariadb.com/2346414/Connectors/java/connector-java-3.0.7/mariadb-java-client-3.0.7.jar > > > > > > > > > https://mariadb.com/downloads/connectors/connectors-data-access/java8-connector/ > > > > > > The driver loads/works fine for the DIH endpoint when its placed in the > > > /opt/server/dist folder. However when I use the stream endpoint I get > a > > > the jdbc driver failed to load error. > > > > > > On Mon, Sep 12, 2022 at 10:17 AM James Greene < > > ja...@jamesaustingreene.com > > > > > > > wrote: > > > > > > > The jdbc class/driver loads fine when I use the DIH endpoint. When I > > use > > > > the same connection string for the stream endpoint I get the class > not > > > > found driver error. I also see a msg in solr logs during startup that > > the > > > > class has been added to the classloader from path 'opt/solr/dist' > > (where > > > I > > > > have the mariadb jdbc driver jar file). > > > > > > > > On Sun, Sep 11, 2022, 4:05 PM Joel Bernstein <joels...@gmail.com> > > wrote: > > > > > > > >> I suspect this is a classloader issue. You may have to move the > driver > > > >> into > > > >> the lib directory with the Solr core and Solrj libs so that its > > visible > > > >> to > > > >> both DIH and streaming expressions. > > > >> > > > >> > > > >> Joel Bernstein > > > >> http://joelsolr.blogspot.com/ > > > >> > > > >> > > > >> On Sat, Sep 10, 2022 at 10:18 PM James Greene < > > > >> ja...@jamesaustingreene.com> > > > >> wrote: > > > >> > > > >> > Looking at the solr start logs, I can see that the classes i'm > > > including > > > >> > the in solrconfig.xml are being loaded: > > > >> > > > > >> > 2022-09-11 02:07:05.108 INFO > > > >> > (coreLoadExecutor-21-thread-1-processing-n:172.18.0.5:8983_solr) > > > >> [c:col1 > > > >> > s:shard2 r:core_node4 x:col1_shard2_replica_n2] > > > >> o.a.s.c.SolrResourceLoader > > > >> > Added 1 libs to classloader, from paths: [/opt/solr/dist] > > > >> > > > > >> > Also, the jdbc library loads fine and is usable when I use the DIH > > > >> > endpoint. > > > >> > > > > >> > > > > >> > Query: > > > >> > jdbc(connection="jdbc:mariadb://<host>:3306/<db>",sql="select id > > from > > > >> tbl", > > > >> > sort="id ASC", driver="org.mariadb.jdbc.Driver", user="<user>", > > > >> > password="<pass>") > > > >> > > > > >> > Error: > > > >> > ""Failed to load JDBC driver for 'org.mariadb.jdbc.Driver'"" > > > >> > > > > >> > solrconfig.xml > > > >> > <lib dir="${solr.install.dir}/dist/" > > > >> regex="mariadb-java-client-.*\.jar" /> > > > >> > <expressible name="jdbc" > > > >> > class="org.apache.solr.client.solrj.io.stream.JDBCStream"/> > > > >> > > > > >> > Cheers, > > > >> > > > > >> > /************************************ > > > >> > * James Austin Greene > > > >> > * www.jamesaustingreene.com > > > >> > * 336-lol-nerd > > > >> > ************************************/ > > > >> > > > > >> > > > > >> > On Fri, Sep 9, 2022 at 5:01 PM Joel Bernstein <joels...@gmail.com > > > > > >> wrote: > > > >> > > > > >> > > You may be having classloader issues. I'm not sure where you > have > > > the > > > >> > > driver jar, but it may need to be copied into the lib directory > > with > > > >> core > > > >> > > Solr jars. > > > >> > > > > > >> > > > > > >> > > Joel Bernstein > > > >> > > http://joelsolr.blogspot.com/ > > > >> > > > > > >> > > > > > >> > > On Fri, Sep 9, 2022 at 4:58 PM Joel Bernstein < > joels...@gmail.com > > > > > > >> > wrote: > > > >> > > > > > >> > > > Can you post the stack trace from the log? > > > >> > > > > > > >> > > > It looks like the JDBCStream is registered, but the stack > trace > > > >> > > > may provide some more info on why the driver didn't load. > > > >> > > > > > > >> > > > Joel Bernstein > > > >> > > > http://joelsolr.blogspot.com/ > > > >> > > > > > > >> > > > > > > >> > > > On Fri, Sep 9, 2022 at 12:51 PM James Greene < > > > >> > > ja...@jamesaustingreene.com> > > > >> > > > wrote: > > > >> > > > > > > >> > > >> I'm still unable to get the JDBC stream to work. I've > verified > > > >> that > > > >> > > >> 'jdbc' > > > >> > > >> shows up in the plugin list: > > > >> > > >> > > > >> > > >> http://localhost:8983/solr/col1/stream?action=PLUGINS > > > >> > > >> > > > >> > > >> When using the DIH I use the jdbc driver: > > > >> > > >> org.mariadb.jdbc.Driver > > > >> > > >> > > > >> > > >> With connection string: > > > >> > > >> jdbc:mysql://{{host}}:{{port}}/{{database}} > > > >> > > >> > > > >> > > >> I am able to get DIH pulling data from mariadb with that > > > connection > > > >> > > >> string. > > > >> > > >> > > > >> > > >> However using the same connection string in the streaming > > > >> expression: > > > >> > > >> > jdbc(connection="jdbc:mysql://mariadb:3306/db",sql="select > > id > > > >> from > > > >> > > tbl > > > >> > > >> order by id asc limit 1", sort="id asc", > > > >> > > driver="org.mariadb.jdbc.Driver") > > > >> > > >> > > > >> > > >> I get the error: > > > >> > > >> "EXCEPTION": "Failed to load JDBC driver for > > > >> > > >> 'org.mariadb.jdbc.Driver'" > > > >> > > >> > > > >> > > >> I tried upgrading jdbc connector to 3x version and using the > > > >> > connection > > > >> > > >> string "jdbc:mariadb://<host>:<port>/<db>" and get the same > > error > > > >> > > message. > > > >> > > >> > > > >> > > >> > > > >> > > >> > > > >> > > >> > > > >> > > >> > > > >> > > >> On Thu, Sep 8, 2022 at 10:16 PM Joel Bernstein < > > > joels...@gmail.com > > > >> > > > > >> > > >> wrote: > > > >> > > >> > > > >> > > >> > The jdbc stream was removed from the standard expression > > > library > > > >> for > > > >> > > >> > security reasons. It is still in the jars though and can be > > > >> included > > > >> > > >> > through a change in the solrconfig.xml. > > > >> > > >> > > > > >> > > >> > This jira describes how to plugin an expression: > > > >> > > >> > > > > >> > > >> > https://issues.apache.org/jira/browse/SOLR-9103 > > > >> > > >> > > > > >> > > >> > The class is: > > org.apache.solr.client.solrj.io.stream.JDBCStream > > > >> > > >> > > > > >> > > >> > > > > >> > > >> > > > > >> > > >> > Joel Bernstein > > > >> > > >> > http://joelsolr.blogspot.com/ > > > >> > > >> > > > > >> > > >> > > > > >> > > >> > On Thu, Sep 8, 2022 at 6:54 PM James Greene < > > > >> > > >> ja...@jamesaustingreene.com> > > > >> > > >> > wrote: > > > >> > > >> > > > > >> > > >> > > I'm on solr 8.11.1 and trying to use the 'jdbc' streaming > > > >> > expression > > > >> > > >> as > > > >> > > >> > > documented here: > > > >> > > >> > > > > > >> > > > > > https://solr.apache.org/guide/8_11/stream-source-reference.html#jdbc > > > >> > > >> > > > > > >> > > >> > > When I try to run a jdbc stream expression I get the > error: > > > >> > > >> > > 'function 'jdbc' is unknown (not mapped to a valid > > > TupleStream' > > > >> > > >> > > > > > >> > > >> > > Here is an example of the expression i'm trying to run: > > > >> > > >> > > > > > >> > > >> > > > >> > > > > jdbc(connection="jdbc:mysql://mariadb:3306/threatquotient2",sql="select > > > >> > > >> > id > > > >> > > >> > > from data order by id asc limit 1", sort="id asc" > > > >> > > >> > > driver="org.mariadb.jdbc.Driver") > > > >> > > >> > > > > > >> > > >> > > I use the mariadb jdbc driver to get data from our > mariadb > > > >> > instance > > > >> > > >> using > > > >> > > >> > > the data import handler so the jdbc driver should be > > property > > > >> > > loaded. > > > >> > > >> > > > > > >> > > >> > > > > >> > > >> > > > >> > > > > > > >> > > > > > >> > > > > >> > > > > > > > > > >
