You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Per Newgro <pe...@gmx.ch> on 2017/02/07 15:15:14 UTC

Solr 5.5.0 MSSQL Datasource Example

Hello,

has someone a working example for MSSQL Datasource with 'Standard Microsoft SQL Driver'.

My environment:
debian
Java 8
Solr 5.5.0 Standard (download and installed as service)

server/lib/ext
sqljdbc4-4.0.jar

Global JNDI resource defined
server/etc/jetty.xml
    <New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg></Arg>
        <Arg>java:comp/env/jdbc/mydb</Arg>
        <Arg>
            <New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
                <Set name="serverName">ip</Set>
                <Set name="databaseName">mydb</Set>
                <Set name="user">user</Set>
                <Set name="password">password</Set>
            </New>
        </Arg>
    </New>

or 2nd option tried
    <New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg></Arg>
        <Arg>java:comp/env/jdbc/mydb</Arg>
        <Arg>
            <New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
                <Set name="uRL">jdbc:sqlserver://ip;databaseName=mydb;</Set>
                <Set name="user">user</Set>
                <Set name="password">password</Set>
            </New>
        </Arg>
    </New>


collection1/conf/db-data-config.xml
<dataConfig>
  <dataSource jndiName="java:comp/env/jdbc/mydb" type="JdbcDataSource">
  ...

This leads to SqlServerException login failed for user.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
        at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
        at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
        at org.apache.solr.handler.dataimport.JdbcDataSource$1.getFromJndi(JdbcDataSource.java:256)
        at org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:182)
        at org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:172)
        at org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:463)
        at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:309)
        ... 12 more

But when i remove the jndi datasource and rewrite the dataimport data source to
<dataConfig>
<dataSource type="JdbcDataSource" 
              driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
              url="jdbc:sqlserver://ip;databaseName=mydb"
              user="user" password="password" />
...

Then it works.
But this way i need to configure the db in every core. I would like to avoid that.

Thanks
Per

Aw: Re: Solr 5.5.0 MSSQL Datasource Example

Posted by Per Newgro <pe...@gmx.ch>.
Thank you Fuad,

with dbcp2 BasicDataSource it is working

1st i need to add the libraries to server/lib/ext
commons-dbcp2-2.1.1.jar
commons-logging-1.2.jar
commons-pool2-2.4.2.jar
The current version i've found in http://mvnrepository.com/search?q=dbcp

Then my DataSource looks like this
    <New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg></Arg>
        <Arg>java:comp/env/jdbc/myds</Arg>
        <Arg>
            <New class="org.apache.commons.dbcp2.BasicDataSource">
                <Set name="driverClassName">com.microsoft.sqlserver.jdbc.SQLServerDriver</Set>
                <Set name="url">jdbc:sqlserver://ip;databaseName=my_db</Set>
                <Set name="username">user</Set>
                <Set name="password">password</Set>
                <Set name="initialSize">25</Set>
                <Set name="maxWaitMillis">5000</Set>
                <Set name="validationQuery">SELECT 1</Set>
                <Set name="maxTotal">-1</Set>
            </New>
        </Arg>
    </New>

Thanks for your support
Per

> Gesendet: Dienstag, 07. Februar 2017 um 21:39 Uhr
> Von: "Fuad Efendi" <fu...@efendi.ca>
> An: "Per Newgro" <pe...@gmx.ch>, solr-user@lucene.apache.org
> Betreff: Re: Solr 5.5.0 MSSQL Datasource Example
>
> Perhaps this answers your question:
> 
> 
> http://stackoverflow.com/questions/27418875/microsoft-sqlserver-driver-datasource-have-password-empty
> 
> 
> Try different one as per Eclipse docs,
> 
> http://www.eclipse.org/jetty/documentation/9.4.x/jndi-datasource-examples.html
> 
> 
> <New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
> 
>      <Arg></Arg>
> 
>      <Arg>jdbc/DSTest</Arg>
> 
>      <Arg>
> 
>         <New class="net.sourceforge.jtds.jdbcx.JtdsDataSource">
> 
>            <Set name="User">user</Set>
> 
>            <Set name="Password">pass</Set>
> 
>            <Set name="DatabaseName">dbname</Set>
> 
>            <Set name="ServerName">localhost</Set>
> 
>            <Set name="PortNumber">1433</Set>
> 
>         </New>
> 
>      </Arg>
> 
>     </New>
> 
> 
> 
> 
> --
> 
> Fuad Efendi
> 
> (416) 993-2060
> 
> http://www.tokenizer.ca
> Search Relevancy, Recommender Systems
> 
> 
> From: Per Newgro <pe...@gmx.ch> <pe...@gmx.ch>
> Reply: solr-user@lucene.apache.org <so...@lucene.apache.org>
> <so...@lucene.apache.org>
> Date: February 7, 2017 at 10:15:42 AM
> To: solr-user-group <so...@lucene.apache.org>
> <so...@lucene.apache.org>
> Subject:  Solr 5.5.0 MSSQL Datasource Example
> 
> Hello,
> 
> has someone a working example for MSSQL Datasource with 'Standard Microsoft
> SQL Driver'.
> 
> My environment:
> debian
> Java 8
> Solr 5.5.0 Standard (download and installed as service)
> 
> server/lib/ext
> sqljdbc4-4.0.jar
> 
> Global JNDI resource defined
> server/etc/jetty.xml
> <New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
> <Arg></Arg>
> <Arg>java:comp/env/jdbc/mydb</Arg>
> <Arg>
> <New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
> <Set name="serverName">ip</Set>
> <Set name="databaseName">mydb</Set>
> <Set name="user">user</Set>
> <Set name="password">password</Set>
> </New>
> </Arg>
> </New>
> 
> or 2nd option tried
> <New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
> <Arg></Arg>
> <Arg>java:comp/env/jdbc/mydb</Arg>
> <Arg>
> <New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
> <Set name="uRL">jdbc:sqlserver://ip;databaseName=mydb;</Set>
> <Set name="user">user</Set>
> <Set name="password">password</Set>
> </New>
> </Arg>
> </New>
> 
> 
> collection1/conf/db-data-config.xml
> <dataConfig>
> <dataSource jndiName="java:comp/env/jdbc/mydb" type="JdbcDataSource">
> ...
> 
> This leads to SqlServerException login failed for user.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
> 
> at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
> at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
> 
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
> 
> at
> com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
> 
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$1.getFromJndi(JdbcDataSource.java:256)
> 
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:182)
> 
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:172)
> 
> at
> org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:463)
> 
> at
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:309)
> 
> ... 12 more
> 
> But when i remove the jndi datasource and rewrite the dataimport data
> source to
> <dataConfig>
> <dataSource type="JdbcDataSource" br/>
> driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" br/>
> url="jdbc:sqlserver://ip;databaseName=mydb"
> user="user" password="password" />
> ...
> 
> Then it works.
> But this way i need to configure the db in every core. I would like to
> avoid that.
> 
> Thanks
> Per
>

Re: Solr 5.5.0 MSSQL Datasource Example

Posted by Fuad Efendi <fu...@efendi.ca>.
Perhaps this answers your question:


http://stackoverflow.com/questions/27418875/microsoft-sqlserver-driver-datasource-have-password-empty


Try different one as per Eclipse docs,

http://www.eclipse.org/jetty/documentation/9.4.x/jndi-datasource-examples.html


<New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">

     <Arg></Arg>

     <Arg>jdbc/DSTest</Arg>

     <Arg>

        <New class="net.sourceforge.jtds.jdbcx.JtdsDataSource">

           <Set name="User">user</Set>

           <Set name="Password">pass</Set>

           <Set name="DatabaseName">dbname</Set>

           <Set name="ServerName">localhost</Set>

           <Set name="PortNumber">1433</Set>

        </New>

     </Arg>

    </New>




--

Fuad Efendi

(416) 993-2060

http://www.tokenizer.ca
Search Relevancy, Recommender Systems


From: Per Newgro <pe...@gmx.ch> <pe...@gmx.ch>
Reply: solr-user@lucene.apache.org <so...@lucene.apache.org>
<so...@lucene.apache.org>
Date: February 7, 2017 at 10:15:42 AM
To: solr-user-group <so...@lucene.apache.org>
<so...@lucene.apache.org>
Subject:  Solr 5.5.0 MSSQL Datasource Example

Hello,

has someone a working example for MSSQL Datasource with 'Standard Microsoft
SQL Driver'.

My environment:
debian
Java 8
Solr 5.5.0 Standard (download and installed as service)

server/lib/ext
sqljdbc4-4.0.jar

Global JNDI resource defined
server/etc/jetty.xml
<New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>java:comp/env/jdbc/mydb</Arg>
<Arg>
<New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
<Set name="serverName">ip</Set>
<Set name="databaseName">mydb</Set>
<Set name="user">user</Set>
<Set name="password">password</Set>
</New>
</Arg>
</New>

or 2nd option tried
<New id="dataSource" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>java:comp/env/jdbc/mydb</Arg>
<Arg>
<New class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
<Set name="uRL">jdbc:sqlserver://ip;databaseName=mydb;</Set>
<Set name="user">user</Set>
<Set name="password">password</Set>
</New>
</Arg>
</New>


collection1/conf/db-data-config.xml
<dataConfig>
<dataSource jndiName="java:comp/env/jdbc/mydb" type="JdbcDataSource">
...

This leads to SqlServerException login failed for user.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)

at
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)

at
com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)

at
com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)

at
org.apache.solr.handler.dataimport.JdbcDataSource$1.getFromJndi(JdbcDataSource.java:256)

at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:182)

at
org.apache.solr.handler.dataimport.JdbcDataSource$1.call(JdbcDataSource.java:172)

at
org.apache.solr.handler.dataimport.JdbcDataSource.getConnection(JdbcDataSource.java:463)

at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:309)

... 12 more

But when i remove the jndi datasource and rewrite the dataimport data
source to
<dataConfig>
<dataSource type="JdbcDataSource" br/>
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver" br/>
url="jdbc:sqlserver://ip;databaseName=mydb"
user="user" password="password" />
...

Then it works.
But this way i need to configure the db in every core. I would like to
avoid that.

Thanks
Per