You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@guacamole.apache.org by Nick Couchman <vn...@apache.org> on 2017/10/12 14:50:23 UTC
[DISCUSS] JDBC SQLite Module?
Just wanted to throw this out for discussion - is there any interest in a
JDBC SQLite module? Seems like maybe it would provide a good method for
people to test/tinker with Guacamole before actually implementing it. I
can do a JIRA issue and pull request if there's interest, or not...?
-Nick
Re: [DISCUSS] JDBC SQLite Module?
Posted by Nick Couchman <vn...@apache.org>.
>
>
>> Well, got it most of the way done and hitting an obnoxious bug or issue
> that I can't pinpoint exactly where it's coming from. Four of the queries
> - two in the ConnectionMapper.xml file and two in the
> ConnectionGroupMapper.xml file - are causing some problems. One example is
> below. Something about the queries where it tries to execute multiple
> SELECT statements along with the <foreach> loops - it almost looks like
> it's not resetting the start position of the array index in each of the
> foreach loops, so when it goes to do the second loop it hits an
> ArrayIndexOutOfBoundsException because it's trying to access a parameter
> index beyond what's been delivered to it. It's really, really odd that it
> works perfectly fine in PostgreSQL, SQL Server, and MySQL, but fails in
> SQLite. It seems like it should be an issue with MyBatis or MyBatis-Guice,
> but the fact that it's only happening in SQLite points to something in the
> JDBC driver.
>
> Someone else was, apparently, trying to do this and also ran across the
> issue.
>
> -Nick
>
This looks to be a bug (or feature, perhaps) in the way the SQLite JDBC
driver handles parameters in prepared statements. I tracked it most of the
way down (minus a solution), and filed a GitHub issue on the SQLite JDBC
driver page [1]. I also responded to the StackOverflow question that was
posted back in January [2].
I'll probably go ahead and submit the PR for the SQLite module with the
changes necessary to overcome the buggy (IMHO) behavior in the driver and
see if anything becomes of the issue I filed. Won't be real clean, but
it'll work.
-Nick
[1] https://github.com/xerial/sqlite-jdbc/issues/277
[2]
https://stackoverflow.com/questions/41969037/sqlite-mybatis-guice-throw-arrayindexoutofboundsexception
Re: [DISCUSS] JDBC SQLite Module?
Posted by Nick Couchman <ni...@gmail.com>.
>
> Sounds great to me.
>
> +1
>
> - Mike
>
Well, got it most of the way done and hitting an obnoxious bug or issue
that I can't pinpoint exactly where it's coming from. Four of the queries
- two in the ConnectionMapper.xml file and two in the
ConnectionGroupMapper.xml file - are causing some problems. One example is
below. Something about the queries where it tries to execute multiple
SELECT statements along with the <foreach> loops - it almost looks like
it's not resetting the start position of the array index in each of the
foreach loops, so when it goes to do the second loop it hits an
ArrayIndexOutOfBoundsException because it's trying to access a parameter
index beyond what's been delivered to it. It's really, really odd that it
works perfectly fine in PostgreSQL, SQL Server, and MySQL, but fails in
SQLite. It seems like it should be an issue with MyBatis or MyBatis-Guice,
but the fact that it's only happening in SQLite points to something in the
JDBC driver.
Someone else was, apparently, trying to do this and also ran across the
issue [1].
Anyone have any insight/hints? I came up with a kludgy way of fixing it -
basically multiple result maps, then splitting out the select statements
into individual statements and calling them from the <collection> tag.
This works fine, but is a bunch of extra XML and doesn't perform nearly as
well since it ends up having to run more queries.
-Nick
[1]
https://stackoverflow.com/questions/41969037/sqlite-mybatis-guice-throw-arrayindexoutofboundsexception
15:22:03.976 [http-nio-8080-exec-997] ERROR o.a.g.rest.RESTExceptionWrapper
- Unexpected internal error:
### Error querying database. Cause: org.apache.ibatis.type.TypeException:
Could not set parameters for mapping:
ParameterMapping{property='__frch_identifier_2', mode=IN, javaType=class
java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null',
jdbcTypeName='null', expression='null'}. Cause: org.apac
he.ibatis.type.TypeException: Error setting non null for parameter #3 with
JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a
different configuration property. Cause:
java.lang.ArrayIndexOutOfBoundsException: 2
### The error may exist in
org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT connection_group_id,
connection_group_name, parent_id, type,
max_connections, max_connections_per_user,
enable_session_affinity FROM guacamole_connection_group
WHERE connection_group_id IN
( ? , ? ) ;
SELECT parent_id, connection_group_id FROM
guacamole_connection_group WHERE parent_id IN (
? , ? ) ;
SELECT parent_id, connection_id
FROM guacamole_connection WHERE parent_id IN (
? , ? ) ;
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters
for mapping: ParameterMapping{property='__frch_identifier_2', mode=IN,
javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null,
resultMapId='null', jdbcTypeName='null', expression='null'}. Cause:
org.apache.ibatis.type.TypeExcepti
on: Error setting non null for parameter #3 with JdbcType VARCHAR . Try
setting a different JdbcType for this parameter or a different
configuration property. Cause: java.lang.ArrayIndexOutOfBoundsException: 2
15:22:04.042 [http-nio-8080-exec-997] DEBUG o.a.g.rest.RESTExceptionWrapper
- Unexpected error in REST endpoint.
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.type.TypeException:
Could not set parameters for mapping:
ParameterMapping{property='__frch_identifier_2', mode=IN, javaType=class
java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null',
jdbcTypeName='null', expression='null'}. Cause: org.apac
he.ibatis.type.TypeException: Error setting non null for parameter #3 with
JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a
different configuration property. Cause:
java.lang.ArrayIndexOutOfBoundsException: 2
### The error may exist in
org/apache/guacamole/auth/jdbc/connectiongroup/ConnectionGroupMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT connection_group_id,
connection_group_name, parent_id, type,
max_connections, max_connections_per_user,
enable_session_affinity FROM guacamole_connection_group
WHERE connection_group_id IN
( ? , ? ) ;
SELECT parent_id, connection_group_id FROM
guacamole_connection_group WHERE parent_id IN (
? , ? ) ;
SELECT parent_id, connection_id
FROM guacamole_connection WHERE parent_id IN (
? , ? ) ;
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters
for mapping: ParameterMapping{property='__frch_identifier_2', mode=IN,
javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null,
resultMapId='null', jdbcTypeName='null', expression='null'}. Cause:
org.apache.ibatis.type.TypeExcepti
on: Error setting non null for parameter #3 with JdbcType VARCHAR . Try
setting a different JdbcType for this parameter or a different
configuration property. Cause: java.lang.ArrayIndexOutOfBoundsException: 2
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
~[na:na]
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
~[na:na]
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
~[na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[na:1.8.0_141]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[na:1.8.0_141]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[na:1.8.0_141]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_141]
at
org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:350)
~[na:na]
at com.sun.proxy.$Proxy10324.selectList(Unknown Source) ~[na:na]
at
org.apache.ibatis.session.SqlSessionManager.selectList(SqlSessionManager.java:206)
~[na:na]
at
org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
~[na:na]
at
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
~[na:na]
at
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[na:na]
at com.sun.proxy.$Proxy10381.select(Unknown Source) ~[na:na]
at
org.apache.guacamole.auth.jdbc.base.ModeledDirectoryObjectService.retrieveObjects(ModeledDirectoryObjectService.java:396)
~[na:na]
at
org.apache.guacamole.auth.jdbc.connectiongroup.ConnectionGroupDirectory.getAll(ConnectionGroupDirectory.java:54)
~[na:na]
at
org.mybatis.guice.transactional.TransactionalMethodInterceptor.invoke(TransactionalMethodInterceptor.java:96)
~[na:na]
at
org.apache.guacamole.rest.connectiongroup.ConnectionGroupTree.addConnectionGroupDescendants(ConnectionGroupTree.java:281)
~[classes/:na]
at
org.apache.guacamole.rest.connectiongroup.ConnectionGroupTree.<init>(ConnectionGroupTree.java:369)
~[classes/:na]
at
org.apache.guacamole.rest.connectiongroup.ConnectionGroupResource.getConnectionGroupTree(ConnectionGroupResource.java:110)
~[classes/:na]
at
org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:153)
~[classes/:na]
Re: [DISCUSS] JDBC SQLite Module?
Posted by Mike Jumper <mi...@guac-dev.org>.
On Thu, Oct 12, 2017 at 7:50 AM, Nick Couchman <vn...@apache.org> wrote:
>
> Just wanted to throw this out for discussion - is there any interest in a
> JDBC SQLite module? Seems like maybe it would provide a good method for
> people to test/tinker with Guacamole before actually implementing it. I
> can do a JIRA issue and pull request if there's interest, or not...?
>
Sounds great to me.
+1
- Mike