Guacamole 0.9.14 with remote mysql db

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

Guacamole 0.9.14 with remote mysql db

riccardopiccoli
Hello everyone.
I am experiencing an issue using remote MySQL database authentication on
Guacamole 0.9.14.
I am using Guacamole 0.9.14, compiled from source on ubunutu 18.04 LTS. My
servlet container is Tomcat 8, and I am using MySQL as authentication
database. As long as Guacamole (and guacd) and the MySQL instance are on the
same machine (let's call it the "Guacamole Server"), everything is fine.
The problem is, as soon as I try to use MySQL db on a different machine
(let's call it a "DB Server") I get blank page on
[guacamole-server]:8080/Guacamole/#/ (where the login form should be) and
nothing else. Tomcat's webapp manager says that guacamole.war is working
alright, and there are no errors from guacd on syslog. The only error
message I get is a "500 Internal Server Error" (I pasted the full text at
the end of this mail), which appears on catalina.out whenever I try to
access (or refresh) [guacamole-server]:8080/Guacamole/#/.
If I go back to using the local MySQL db, the login page and everything
beyond it are good again.
The only change I make in my configuration between the two scenarios is in
/etc/guacamole/guacamole.properties, where I change "mysql-hostname:" from
"loacalhost" to the static IP of my "DB Server", or vice versa.
In either scenario, I can manually login to the remote MySQL instance of
guacamole_db using -u guacamole_user -h [address-of-db-server].
"Guacamole Server" and "DB Server" are on the same subnet and can ping each
other. The "DB Server" is listening on 3306 and tcpdump shows mysql messages
coming and going on both ends when I attempt to connect (and the login page
stays blank).  
I have already tried rebuilding remote db from schemas (it was initially
imported from dump), rebuild guacamole on a fresh ubuntu installation,
setting blank password for guacamole_user, giving him ALL PRIVILEGES ON *.*,
identifying him as 'guacamole_user'@'%' (instead of providing a restrictive
source ip) and copying Connector/J to "DB Server", but everytime I get the
same error.
I think a similiar issue has already been documented in this thread
(https://www.mail-archive.com/user@.../msg02356.html)
but it looks like no solution was found at the time.
If anyone has any idea on what could be causing this, please let me know.

The error code in catalina.out is:

/SEVERE [http-nio-8080-exec-15]
com.sun.jersey.spi.container.ContainerResponse.logException Mapped exception
to response: 500 (Internal Server Error)
 org.apache.guacamole.rest.APIException
        at
org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202)
        at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
        at
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:564)
        at
com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
        at
com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
        at
com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
        at
com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
        at
com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
        at
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at
com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
        at
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511)
        at
com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442)
        at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391)
        at
com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381)
        at
com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416)
        at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538)
        at
com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        at
com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
        at
com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
        at
com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
        at
com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
        at
com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
        at
com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
        at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
        at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
        at
org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
        at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
        at
org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
        at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
        at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
        at
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135)
        at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.base/java.lang.Thread.run(Thread.java:844)

/



--
Sent from: http://apache-guacamole-general-user-mailing-list.2363388.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Guacamole 0.9.14 with remote mysql db

vnick
On Mon, May 28, 2018 at 10:34 AM, riccardopiccoli <[hidden email]> wrote:
Hello everyone.
I am experiencing an issue using remote MySQL database authentication on
Guacamole 0.9.14.
I am using Guacamole 0.9.14, compiled from source on ubunutu 18.04 LTS. My
servlet container is Tomcat 8, and I am using MySQL as authentication
database. As long as Guacamole (and guacd) and the MySQL instance are on the
same machine (let's call it the "Guacamole Server"), everything is fine.
The problem is, as soon as I try to use MySQL db on a different machine
(let's call it a "DB Server") I get blank page on
[guacamole-server]:8080/Guacamole/#/ (where the login form should be) and
nothing else. Tomcat's webapp manager says that guacamole.war is working
alright, and there are no errors from guacd on syslog. The only error
message I get is a "500 Internal Server Error" (I pasted the full text at
the end of this mail), which appears on catalina.out whenever I try to
access (or refresh) [guacamole-server]:8080/Guacamole/#/.
If I go back to using the local MySQL db, the login page and everything
beyond it are good again.
The only change I make in my configuration between the two scenarios is in
/etc/guacamole/guacamole.properties, where I change "mysql-hostname:" from
"loacalhost" to the static IP of my "DB Server", or vice versa.
In either scenario, I can manually login to the remote MySQL instance of
guacamole_db using -u guacamole_user -h [address-of-db-server].
"Guacamole Server" and "DB Server" are on the same subnet and can ping each
other. The "DB Server" is listening on 3306 and tcpdump shows mysql messages
coming and going on both ends when I attempt to connect (and the login page
stays blank). 
I have already tried rebuilding remote db from schemas (it was initially
imported from dump), rebuild guacamole on a fresh ubuntu installation,
setting blank password for guacamole_user, giving him ALL PRIVILEGES ON *.*,
identifying him as 'guacamole_user'@'%' (instead of providing a restrictive
source ip) and copying Connector/J to "DB Server", but everytime I get the
same error.
I think a similiar issue has already been documented in this thread
(https://www.mail-archive.com/user@....apache.org/msg02356.html)
but it looks like no solution was found at the time.
If anyone has any idea on what could be causing this, please let me know.

The error code in catalina.out is:

You might need to provide a more complete error log, or put your Guacamole instance into DEBUG or TRACE logging (described in the manual, using the logback.xml file).

I would check a couple of things:
- On the system running Guacamole, using the MySQL client to try to connect to your remote database server, using the same credentials you expect Guacamole to use.
- When you say DB server is listening on 3306, make sure it's listening on all interfaces and not just on the localhost interface.
- Make sure no network firewalls are preventing or dropping the traffic.

-Nick 
Reply | Threaded
Open this post in threaded view
|

Re: Guacamole 0.9.14 with remote mysql db

riccardopiccoli
Thanks to Nick Couchman's advice, I think we solved this issue.
After enabling DEBUG level logging, as Nick suggested, the following appeared in catalina.out:
### Error querying database.  Cause: java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
### The error may exist in org/apache/guacamole/auth/jdbc/user/UserMapper.xml
### The error may involve org.apache.guacamole.auth.jdbc.user.UserMapper.selectOne
### The error occurred while executing a query
### Cause: java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
Setting system timezone from CEST to UTC on both Guacamole and the MySQL database server solves the issue: Guacamole can now connect to the remote mysql instance and authenticate users from there. Everything works as it should.

I am not sure if there is actually an error in the UserMapper.xml file included in guacamole-auth-jdbc-mysql-0.9.14.jar, or if this is the intended behaviour.
In any case, for anyone who may run the same problem, issuing the command "sudo timedatectl set-timezone UTC" on both machines should provide at least a temporary solution.

Thanks again Nick Couchman for your advice.



On 28 May 2018 at 16:42, Nick Couchman <[hidden email]> wrote:
On Mon, May 28, 2018 at 10:34 AM, riccardopiccoli <[hidden email]> wrote:
Hello everyone.
I am experiencing an issue using remote MySQL database authentication on
Guacamole 0.9.14.
I am using Guacamole 0.9.14, compiled from source on ubunutu 18.04 LTS. My
servlet container is Tomcat 8, and I am using MySQL as authentication
database. As long as Guacamole (and guacd) and the MySQL instance are on the
same machine (let's call it the "Guacamole Server"), everything is fine.
The problem is, as soon as I try to use MySQL db on a different machine
(let's call it a "DB Server") I get blank page on
[guacamole-server]:8080/Guacamole/#/ (where the login form should be) and
nothing else. Tomcat's webapp manager says that guacamole.war is working
alright, and there are no errors from guacd on syslog. The only error
message I get is a "500 Internal Server Error" (I pasted the full text at
the end of this mail), which appears on catalina.out whenever I try to
access (or refresh) [guacamole-server]:8080/Guacamole/#/.
If I go back to using the local MySQL db, the login page and everything
beyond it are good again.
The only change I make in my configuration between the two scenarios is in
/etc/guacamole/guacamole.properties, where I change "mysql-hostname:" from
"loacalhost" to the static IP of my "DB Server", or vice versa.
In either scenario, I can manually login to the remote MySQL instance of
guacamole_db using -u guacamole_user -h [address-of-db-server].
"Guacamole Server" and "DB Server" are on the same subnet and can ping each
other. The "DB Server" is listening on 3306 and tcpdump shows mysql messages
coming and going on both ends when I attempt to connect (and the login page
stays blank). 
I have already tried rebuilding remote db from schemas (it was initially
imported from dump), rebuild guacamole on a fresh ubuntu installation,
setting blank password for guacamole_user, giving him ALL PRIVILEGES ON *.*,
identifying him as 'guacamole_user'@'%' (instead of providing a restrictive
source ip) and copying Connector/J to "DB Server", but everytime I get the
same error.
I think a similiar issue has already been documented in this thread
(https://www.mail-archive.com/user@.../msg02356.html)
but it looks like no solution was found at the time.
If anyone has any idea on what could be causing this, please let me know.

The error code in catalina.out is:

You might need to provide a more complete error log, or put your Guacamole instance into DEBUG or TRACE logging (described in the manual, using the logback.xml file).

I would check a couple of things:
- On the system running Guacamole, using the MySQL client to try to connect to your remote database server, using the same credentials you expect Guacamole to use.
- When you say DB server is listening on 3306, make sure it's listening on all interfaces and not just on the localhost interface.
- Make sure no network firewalls are preventing or dropping the traffic.

-Nick