[SOLVED] Database connectivity when running as a Windows Service

EMDuellEMDuell Member Posts: 21 Contributor II
edited June 2020 in Help
I am setting up a proof of concept with a customer to try and demonstrate the potential of RapidMiner in a corporate setting.  I'm starting with the 5.3CE server in hopes that we can get funding for funding next year.

Here is my setup:

1) RapidMiner 5.3CE Server, running as an installed Windows service, on Windows Server 2012 Standard (64-bit) w/8GB memory running in a VMware environment, plenty of hard drive, and Java JRE 1.8.0_20
2) MySQL 5.1.73 running on RedHat Linux 6.5 (64-bit) with 8GB RAM, plenty of hard drive.

When I start the service everything is great.  No problems.  After a while, things just stop working.  Here are the two error situations:

1) I can hit the login page, and it doesn't "break" but it cannot authenticate the user.  I can see from watching in MySQL workbench that the RM server stops attempting to connect to the MySQL database.  No accounts work when attempting to access the web interface.  Connecting from the RM client either does not authenticate or says "Cannot refresh folder. ... Reason: Read timed out"

2) In a more extreme failure situation, I'll just get the JBOSS error page.  I don't have a copy of it right at the moment, but if memory serves, it says that the database server is no longer communicating with the JBOSS application.

I thought this might be a Java MySQL problem, so I updated the MySQL connector to my-sql-connector-java-5.1.32-bin.  It did not seem to make a difference that I noticed.

This is really perplexing, I have installed RM Server 5.3 CE in a full Linux environment with no issues.  So I'm not sure if I should look at something related to Windows, running RM Server as a Windows service, the Java configuration in Windows, etc.

I realize more information might be needed, but I'm not all that familiar with JDBC so am not quite sure where to look.  Any help would be appreciated.  

THANK YOU in advance for your help!

-Eric
Tagged:

Answers

  • EMDuellEMDuell Member Posts: 21 Contributor II
    So ... here's the last few exceptions posted by the JBOSS response.  I have been doing a bit of further research, seeing posts about using C3PO and adjusting hibernate settings with MySQL and JDBC, but I would have to believe that's really it, otherwise everyone would have the problem.

    Please let me know if you have suggestions.

    -Eric
    ---
    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    The last packet successfully received from the server was 882,029 milliseconds ago.  The last packet sent successfully to the server was 31 milliseconds ago.
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    java.lang.reflect.Constructor.newInstance(Unknown Source)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3697)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3586)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4131)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
    com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5359)
    org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:547)
    org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:836)
    org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:250)
    sun.reflect.GeneratedMethodAccessor393.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:71)
    com.sun.proxy.$Proxy220.prepareStatement(Unknown Source)
    org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:81)
    org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3756)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3725)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.triggeredJobComplete(JobStoreSupport.java:2957)
    org.quartz.core.QuartzScheduler.notifyJobStoreJobComplete(QuartzScheduler.java:1785)
    org.quartz.core.JobRunShell.run(JobRunShell.java:287)
    org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)

    root cause

    java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3143)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3597)
    com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3586)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4131)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
    com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5359)
    org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:547)
    org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:836)
    org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:250)
    sun.reflect.GeneratedMethodAccessor393.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:71)
    com.sun.proxy.$Proxy220.prepareStatement(Unknown Source)
    org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:81)
    org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3756)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3725)
    org.quartz.impl.jdbcjobstore.JobStoreSupport.triggeredJobComplete(JobStoreSupport.java:2957)
    org.quartz.core.QuartzScheduler.notifyJobStoreJobComplete(QuartzScheduler.java:1785)
    org.quartz.core.JobRunShell.run(JobRunShell.java:287)
    org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,993 RM Engineering
    Hi,

    the old RapidMiner Server 1.3.15 did not support Java 7 and it was never tested with Java 8. The first step in such cases is to go with the recommended Java version because otherwise you might experience all sorts of strange problems. Please try again with the latest version of Java 6.
    Apart from that, what is the -Xmx parameter set to? That is the maximum amount of memory the server can take, even if there would be much more free memory on that machine. If that value is too low, memory might fill up and Java might start swapping.

    Regards,
    Marco
  • EMDuellEMDuell Member Posts: 21 Contributor II
    Hi Marco, thank you for the reply.  I will downgrade to Java 6 and we'll see if that makes a difference.

    I have server v1.3.15 running under Java 7 in a full Linux environment and there are no major issues that I have come across although the implementation is still at a basic level of sophistication, so maybe they will show up later.

    I'm running with the -Xmx parameter set to 6144 and so far memory does not seem to be the issue.

    -Eric.
  • EMDuellEMDuell Member Posts: 21 Contributor II
    Ok, I went back to Java 6 and am still having the same error.  After an hour or two JBOSS stops communicating with the server.  Any suggestions would be appreciated.

    -Eric
  • Marco_BoeckMarco_Boeck Administrator, Moderator, Employee, Member, University Professor Posts: 1,993 RM Engineering
    Hi,

    while there was a known performance problem with RapidMiner Server 2.0 to 2.0.8 on Windows (that could be fixed by removing the -XX:TieredCompilation parameter from the standalone.bat file), I don't know anything about specific Windows performance problems of the old RapidAnalytics 1.3.x.

    If that is an option, I suggest trying out the latest RapidMiner Server instead. This also has the advantage of a much faster server and the full reporting capabilities being available, even with the Starter license. And if that is not enough, you can also get a 2 week Trial license for it.
    If you remove the parameter I mentioned above, it should run just fine (Version 2.0.9 which comes with a fix for that problem has not yet been released). It requires Java 7 (neither 6 nor 8 will work), though.

    Regards,
    Marco
  • EMDuellEMDuell Member Posts: 21 Contributor II
    Hi Marco,

    Thanks for your replies.  Right now we're running a proof of concept so that we can show a financial return which would open the door to moving to the latest edition.  Having the CE edition of the previous version is a huge marketing advantage for your company, and I appreciate that it's an option for all of us "self starters".  

    I worked on the mysql-ds.mxl file extensively, and the server seems to have settled down.  Here are the settings I am using.  If you see a setting that looks problematic, or that might cause unintended behavior of RapidMiner, please let me know.

    Thank you!

    -Eric

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <datasources>
       <local-tx-datasource>
           <jndi-name>jdbc/RapidAnalyticsDS</jndi-name>
           <rar-name>jboss-local-jdbc.rar</rar-name>
           <use-java-context>false</use-java-context>
           <min-pool-size>3</min-pool-size>
           <max-pool-size>40</max-pool-size>
           <blocking-timeout-millis>30000</blocking-timeout-millis>
           <idle-timeout-minutes>30</idle-timeout-minutes>
           <prefill>false</prefill>
           <background-validation>false</background-validation>
           <background-validation-millis>0</background-validation-millis>
           <validate-on-match>true</validate-on-match>
           <statistics-formatter>org.jboss.resource.statistic.pool.JBossDefaultSubPoolStatisticFormatter</statistics-formatter>
           <isSameRM-override-value>false</isSameRM-override-value>
           <allocation-retry>0</allocation-retry>
           <allocation-retry-wait-millis>5000</allocation-retry-wait-millis>
           <security-domain xsi:type="securityMetaData" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
           <metadata/>
           <local-transaction/>
           <prepared-statement-cache-size>0</prepared-statement-cache-size>
           <share-prepared-statements>false</share-prepared-statements>
           <set-tx-query-timeout>false</set-tx-query-timeout>
           <query-timeout>0</query-timeout>
           <use-try-lock>0</use-try-lock>
           <url-delimiter>|</url-delimiter>
           <connection-property name="autoReconnect">true</connection-property>        <maxReconnects>50</maxReconnects>
           <initialTimeout>15</initialTimeout>
           <failOverReadOnly>false</failOverReadOnly>
           <driver-class>com.mysql.jdbc.Driver</driver-class>
           <connection-url>jdbc:mysql://url_here</connection-url>
    <user-name>username_here</user-name>
    <password>password_here</password>
         <metadata>
           <type-mapping>mySQL</type-mapping>
         </metadata>
       </local-tx-datasource>
    </datasources>
Sign In or Register to comment.