Solr 5.5.0 MSSQL Datasource Example

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Solr 5.5.0 MSSQL Datasource Example

Per Newgro
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
Reply | Threaded
Open this post in threaded view
|

Re: Solr 5.5.0 MSSQL Datasource Example

Fuad Efendi
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 <[hidden email]> <[hidden email]>
Reply: [hidden email] <[hidden email]>
<[hidden email]>
Date: February 7, 2017 at 10:15:42 AM
To: solr-user-group <[hidden email]>
<[hidden email]>
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
Reply | Threaded
Open this post in threaded view
|

Aw: Re: Solr 5.5.0 MSSQL Datasource Example

Per Newgro
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" <[hidden email]>
> An: "Per Newgro" <[hidden email]>, [hidden email]
> 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 <[hidden email]> <[hidden email]>
> Reply: [hidden email] <[hidden email]>
> <[hidden email]>
> Date: February 7, 2017 at 10:15:42 AM
> To: solr-user-group <[hidden email]>
> <[hidden email]>
> 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
>