New OpenKM setup - Cannot connect to MySQL server

[lpallard]

Hi! First time posting here, I've seen several threads regarding mysql problems but I didnt want to hijack an existing thread..

So I have installed OpenKM on my slackware server and followed the instructions of http://wiki.openkm.com/index.php/MySQL_-_OpenKM_6.2 to the letter, browsed every thread I could find, still to no avail.

When I access the login page, I am greeted by this error message:

Code: Select all

Application error
Class: 	org.apache.jasper.JasperException
Message: 	javax.servlet.ServletException: com.openkm.core.DatabaseException: Cannot open connection
Date: 	Mon Dec 02 20:18:34 EST 2013

I have confirmed the DB exists in MySQL and has the identical same credentials of server.xml, I can login to the mysql server with the credentials of server.xml (so I believe mysql is not at fault).

Without pasting the entire catalina log file here (wayy too long), heres a typical error section:

Code: Select all

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)
	at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnectionHelper.java:51)
	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:263)
	at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:219)
	at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:372)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
	at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:202)
	at com.openkm.dao.HibernateUtil.getSessionFactory(HibernateUtil.java:114)
	at com.openkm.servlet.RepositoryStartupServlet.init(RepositoryStartupServlet.java:100)
	at javax.servlet.GenericServlet.init(GenericServlet.java:160)
	at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1266)
	at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1185)
	at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1080)
	at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:5015)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5302)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:895)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:871)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:615)
	at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:962)
	at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1603)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:724)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.Util.getInstance(Util.java:384)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2412)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
	... 28 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2334)
	... 41 more
Caused by: java.net.ConnectException: Connection refused
	at java.net.PlainSocketImpl.socketConnect(Native Method)
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
	at java.net.Socket.connect(Socket.java:579)
	at java.net.Socket.connect(Socket.java:528)
	at java.net.Socket.<init>(Socket.java:425)
	at java.net.Socket.<init>(Socket.java:241)
	at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293)
	... 42 more

I have tried everything I could think of (deleting the repository, flushing mysql's blocked hosts) but somehow, openkm is not able to create anything in the DB. phpmyadmin shows that the database okmdb is totally empty..

Can anybody help!? I can provide anything else, just ask!
Thanks

[jllort]

Your connection is not right. Try to connect to mysql using the username and password credentials from OpenKM server. Is a connection problem or bad openkm xml configuration problem.

From terminal connection should be:

Code: Select all

mysql -h localhost -u openkm -p

[lpallard]

Hi! thanks for replying!

Im sorry to say, its not working.

The credentials located in "server.xml" allow connection to the MySQL server with the command

Code: Select all

mysql -h localhost -u openkm -p

So my understanding is that the problem is not caused by credential issues.

User "openkm" as specified in server.xml has all privileges on okmbd database so I dont see where OpenKM has troubles connecting to the okmdb database...

My server.xml file, if that can help:

Code: Select all

<?xml version='1.0' encoding='utf-8'?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<!-- Note:  A "Server" is not itself a "Container", so you may not
     define subcomponents such as "Valves" at this level.
     Documentation at /docs/config/server.html
 -->
<Server port="8005" shutdown="SHUTDOWN">
  <!-- Security listener. Documentation at /docs/config/listeners.html
  <Listener className="org.apache.catalina.security.SecurityListener" />
  -->
  <!--APR library loader. Documentation at /docs/apr.html -->
  <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" />
  <!--Initialize Jasper prior to webapps are loaded. Documentation at /docs/jasper-howto.html -->
  <Listener className="org.apache.catalina.core.JasperListener" />
  <!-- Prevent memory leaks due to use of particular java/javax APIs-->
  <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" />
  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />
  <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" />
  
  <!-- Automatically reload log4j configuration -->
  <Listener className="com.openkm.util.Log4JInitializer" configFile="${catalina.base}/conf/log4j.properties"/>

  <!-- Global JNDI resources
       Documentation at /docs/jndi-resources-howto.html
  -->
  <GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users
    -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />
              
    <Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
            username="openkm" password="somepass" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&characterEncoding=UTF8"/>
            
    <Resource name="mail/OpenKM" auth="Container" type="javax.mail.Session"
            mail.smtp.host="localhost" mail.from="testing@openkm.com"/>
  </GlobalNamingResources>

  <!-- A "Service" is a collection of one or more "Connectors" that share
       a single "Container" Note:  A "Service" is not itself a "Container",
       so you may not define subcomponents such as "Valves" at this level.
       Documentation at /docs/config/service.html
   -->
  <Service name="Catalina">

    <!--The connectors can use a shared executor, you can define one or more named thread pools-->
    <!--
    <Executor name="tomcatThreadPool" namePrefix="catalina-exec-"
        maxThreads="150" minSpareThreads="4"/>
    -->


    <!-- A "Connector" represents an endpoint by which requests are received
         and responses are returned. Documentation at :
         Java HTTP Connector: /docs/config/http.html (blocking & non-blocking)
         Java AJP  Connector: /docs/config/ajp.html
         APR (HTTP/AJP) Connector: /docs/apr.html
         Define a non-SSL HTTP/1.1 Connector on port 8055
    -->
    <Connector port="8055" address="0.0.0.0" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />
    <!-- A "Connector" using the shared thread pool-->
    <!--
    <Connector executor="tomcatThreadPool"
               port="8055" protocol="HTTP/1.1"
               connectionTimeout="20000"
               redirectPort="8443" />
    -->
    <!-- Define a SSL HTTP/1.1 Connector on port 8443
         This connector uses the JSSE configuration, when using APR, the
         connector should be using the OpenSSL style configuration
         described in the APR documentation -->
    <!--
    <Connector port="8443" protocol="HTTP/1.1" SSLEnabled="true"
               maxThreads="150" scheme="https" secure="true"
               clientAuth="false" sslProtocol="TLS" />
    -->

    <!-- Define an AJP 1.3 Connector on port 8009 -->
    <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />


    <!-- An Engine represents the entry point (within Catalina) that processes
         every request.  The Engine implementation for Tomcat stand alone
         analyzes the HTTP headers included with the request, and passes them
         on to the appropriate Host (virtual host).
         Documentation at /docs/config/engine.html -->

    <!-- You should set jvmRoute to support load-balancing via AJP ie :
    <Engine name="Catalina" defaultHost="localhost" jvmRoute="jvm1">
    -->
    <Engine name="Catalina" defaultHost="localhost">

      <!--For clustering, please take a look at documentation at:
          /docs/cluster-howto.html  (simple how to)
          /docs/config/cluster.html (reference documentation) -->
      <!--
      <Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster"/>
      -->

      <!-- Use the LockOutRealm to prevent attempts to guess user passwords
           via a brute-force attack -->
      <Realm className="org.apache.catalina.realm.LockOutRealm">
        <!-- This Realm uses the UserDatabase configured in the global JNDI
             resources under the key "UserDatabase".  Any edits
             that are performed against this UserDatabase are immediately
             available for use by the Realm.  -->
        <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
               resourceName="UserDatabase"/>
      </Realm>

      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <!-- SingleSignOn valve, share authentication between web applications
             Documentation at: /docs/config/valve.html -->
        <!--
        <Valve className="org.apache.catalina.authenticator.SingleSignOn" />
        -->

        <!-- Access log processes all example.
             Documentation at: /docs/config/valve.html
             Note: The pattern used is equivalent to using pattern="common" -->
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log." suffix=".txt"
               pattern="%h %l %u %t "%r" %s %b" />

      </Host>
    </Engine>
  </Service>
</Server>

I'd be very glad if someone could help me get going so I can test OpenKM for good and ditch knowledgetree that is already 3 years old and no longer maintained....
Thanks!

[jllort]

There's no hidden documentation, is as simply as you see. If you have not changed mysql port etc.. .and from terminal can connect from openkm should also get connection. If you arrived here then you got some problem between application connection and mysql that's blocking ( I suggest take a look firewalls or others applications that could be the reason why you're not getting connection ).

The cause is clear Caused by: java.net.ConnectException: Connection refused

[lpallard]

Fixed!

For whatever reasons, mysql did not accept a login from the openkm user from the localhost, instead I had to replace localhost in the server.xml file by the actual physical server's IP address

so from

Code: Select all

url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&characterEncoding=UTF8"/>

to

Code: Select all

url="jdbc:mysql://192.168.0.101:3306/okmdb?autoReconnect=true&characterEncoding=UTF8"/>

After that, I replaced "none" by " create" in openkm.cfg for it to create the tables in the DB, restarted Openkm and voila!

Weird... I have dozens of other applications and frameworks using "localhost" to connect to mysql and they all work. Something in my jdbc driver?

[jllort]

Sometimes also happened to us. Last time I spend two hours trying to find the problem and the most singular is that you can connect with MySQL client to localhost but not with JDBC. Really client is lying you and you can connect when really you should not get connection.

The last time was somebody who had changed MySQL config file to connect only with IP. Other reason could be bad network configuration or bad OpenKM MySQL security grants for network connection. Take in mind is not a JDBC problem or OpenKM sure. Always is on MySQL or OS networking miss configuration. For my experience normally is with MySQL.