[SOLVED] Database connectivity when running as a Windows Service

EMDuell

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

EMDuell

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_Boeck

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

EMDuell

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.

EMDuell

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_Boeck

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

EMDuell

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>