spring-session-jdbc ORA-01461 on save session attributes

[benwynn]

Spring Session JDBC worked with 2.0.5.RELEASE, is now broken with 2.0.6.RELEASE due to code change resulting from issue #1031

We are using Oracle Enterprise Edition 11.2.0.4.v16 on AWS RDS and the new query yields an error:

ORA-01461: can bind a LONG value only for insert into a LONG column

from org.springframework.session.jdbc.JdbcOperationsSessionRepository:

Old Working Query:
private static final String CREATE_SESSION_ATTRIBUTE_QUERY =
"INSERT INTO %TABLE_NAME%_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) (?, ?, ?)";

New Broken Query:
private static final String CREATE_SESSION_ATTRIBUTE_QUERY =
"INSERT INTO %TABLE_NAME%_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) " +
"SELECT PRIMARY_ID, ?, ? " +
"FROM %TABLE_NAME% " +
"WHERE SESSION_ID = ?";

My hypothesis is that while binding a blob or clob in to a table works fine, when you bind it to a fictional table created as the result set of "select literal from ..." the type of that BLOB literal gets modified by oracle for backwards compatability to the 'LONG' type which it then cannot use for inserting into the other table. perhaps add a type cast letting the fictional table know what type it is supposed to be?

Thank You

[vpavic]

Thanks for the report @benwynn. Unfortunately our integration tests didn't catch this as we can't have an Oracle specific integration test, due to Oracle JDBC driver not being present in public Maven repositories.

As a workaround, could you try using JdbcOperationsSessionRepository#setCreateSessionAttributeQuery to manually set the query with cast expression and see if that works for you? We cannot take the cast approach by default, as that part is very specific to database vendor and wouldn't work everywhere.

[vpavic]

I've tried to reproduce this locally by creating the following integration test:

diff --git a/spring-session-jdbc/spring-session-jdbc.gradle b/spring-session-jdbc/spring-session-jdbc.gradle
index 40e880fc..3fe62426 100644
--- a/spring-session-jdbc/spring-session-jdbc.gradle
+++ b/spring-session-jdbc/spring-session-jdbc.gradle
@@ -21,5 +21,6 @@ dependencies {
 	integrationTestCompile "org.testcontainers:mariadb"
 	integrationTestCompile "org.testcontainers:mssqlserver"
 	integrationTestCompile "org.testcontainers:mysql"
+	integrationTestCompile "org.testcontainers:oracle-xe"
 	integrationTestCompile "org.testcontainers:postgresql"
 }
diff --git a/spring-session-jdbc/src/integration-test/java/org/springframework/session/jdbc/Oracle11gJdbcOperationsSessionRepositoryITests.java b/spring-session-jdbc/src/integration-test/java/org/springframework/session/jdbc/Oracle11gJdbcOperationsSessionRepositoryITests.java
new file mode 100644
index 00000000..55936697
--- /dev/null
+++ b/spring-session-jdbc/src/integration-test/java/org/springframework/session/jdbc/Oracle11gJdbcOperationsSessionRepositoryITests.java
@@ -0,0 +1,95 @@
+/*
+ * Copyright 2014-2018 the original author or authors.
+ *
+ * Licensed 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.
+ */
+
+package org.springframework.session.jdbc;
+
+import java.sql.SQLException;
+
+import javax.sql.DataSource;
+
+import oracle.jdbc.pool.OracleDataSource;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.runner.RunWith;
+import org.testcontainers.containers.OracleContainer;
+
+import org.springframework.context.annotation.Bean;
+import org.springframework.context.annotation.Configuration;
+import org.springframework.core.io.ResourceLoader;
+import org.springframework.jdbc.datasource.init.DataSourceInitializer;
+import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
+import org.springframework.test.context.ContextConfiguration;
+import org.springframework.test.context.junit4.SpringRunner;
+import org.springframework.test.context.web.WebAppConfiguration;
+
+/**
+ * Integration tests for {@link JdbcOperationsSessionRepository} using Oracle 11g R2
+ * database.
+ *
+ * @author Vedran Pavic
+ */
+@RunWith(SpringRunner.class)
+@WebAppConfiguration
+@ContextConfiguration
+public class Oracle11gJdbcOperationsSessionRepositoryITests
+		extends AbstractJdbcOperationsSessionRepositoryITests {
+
+	private static Oracle11gR2Container container = new Oracle11gR2Container();
+
+	@BeforeClass
+	public static void setUpClass() {
+		container.start();
+	}
+
+	@AfterClass
+	public static void tearDownClass() {
+		container.stop();
+	}
+
+	@Configuration
+	static class Config extends BaseConfig {
+
+		@Bean
+		public DataSource dataSource() throws SQLException {
+			OracleDataSource dataSource = new OracleDataSource();
+			dataSource.setURL(container.getJdbcUrl());
+			dataSource.setUser(container.getUsername());
+			dataSource.setPassword(container.getPassword());
+			return dataSource;
+		}
+
+		@Bean
+		public DataSourceInitializer initializer(DataSource dataSource,
+				ResourceLoader resourceLoader) {
+			DataSourceInitializer initializer = new DataSourceInitializer();
+			initializer.setDataSource(dataSource);
+			initializer.setDatabasePopulator(
+					new ResourceDatabasePopulator(resourceLoader.getResource(
+							"classpath:org/springframework/session/jdbc/schema-oracle.sql")));
+			return initializer;
+		}
+
+	}
+
+	private static class Oracle11gR2Container extends OracleContainer {
+
+		Oracle11gR2Container() {
+			super("wnameless/oracle-xe-11g:18.04");
+		}
+
+	}
+
+}

However this works fine for me. @benwynn Could you try running this against your Oracle instance? You would need to clone Spring Session repository, and add the Oracle JDBC driver and the diff from the above. Before running the tests, you would also need to configure OracleDataSource to use your instance.

[vpavic]

@benwynn I've been unable to reproduce this on different editions of Oracle. Therefore I'm removing bug label from the issue until we have a reliable way to reproduce the problem.

[candrews]

@vpavic would it be possible for you to try again with large values for parameter 2? I'm seeing it for larger byte array, it's certainly happening here with one of length 23425.

[vpavic]

Thanks @candrews, now I am able to reproduce the problem. Can you confirm that JdbcOperationsSessionRepository#setCreateSessionAttributeQuery is a viable workaround?

[candrews]

I haven't been able to figure out a query to use with JdbcOperationsSessionRepository#setCreateSessionAttributeQuery that solves the problem.

Using "INSERT INTO PRPS.SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, ?, utl_raw.cast_to_raw(?) FROM PRPS.SPRING_SESSION WHERE SESSION_ID = ? doesn't work; it results in:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_RAW", line 224

[candrews]

I found a something that works:
Don't use JdbcOperationsSessionRepository#setCreateSessionAttributeQuery
Do:

final DefaultLobHandler lobHandler = new DefaultLobHandler();
lobHandler.setCreateTemporaryLob(true);
jdbcOperationsSessionRepository.setLobHandler(lobHandler);

And that works!

It sounds like lobHandler.setCreateTemporaryLob(true); should be used for all JDBC 4.0 compliant drivers; could DatabaseMetaData.getJDBCMajorVersion be used to conditionally configure the lobHandler?

[vpavic]

Thanks for looking into this @candrews.

Yes, providing a custom LobHandler should be the way to address this problem. Our configuration provides support for this by picking up LobHandler bean named springSessionLobHandler. So to work around this one has to register the following bean:

@Bean
public LobHandler springSessionLobHandler() {
	DefaultLobHandler lobHandler = new DefaultLobHandler();
	lobHandler.setCreateTemporaryLob(true);
	return lobHandler;
}

We should however enhance our configuration to do this automatically with Oracle, unless of course the user explicitly provides their own LobHandler as shown above.

It sounds like lobHandler.setCreateTemporaryLob(true); should be used for all JDBC 4.0 compliant drivers; could DatabaseMetaData.getJDBCMajorVersion be used to conditionally configure the lobHandler?

I'm afraid we can't do this solely on basis of JDBC version - for instance, I'm pretty sure PostgreSQL's JDBC driver won't play nice with createTemporaryLob set to true. See LobHandler and DefaultLobHandler javadoc for more details.

[candrews]

Thanks for that workaround info and for putting the resolution of this issue on track for 2.1.0 :)

If there's anything else I can do, please let me know.

I'm curious if other database (such as Postgres, as you mentioned) may have trouble with larger blobs; perhaps another integration test is in order to check that?

[vpavic]

Thanks for that workaround info and for putting the resolution of this issue on track for 2.1.0 :)

We will also backport this fix to 2.0.7.RELEASE as well.

If there's anything else I can do, please let me know.

I've got a fix ready locally, and plan to push it during the day after some more testing. Once that is done, if you could give snapshot a spin I'd highly appreciate it.

I'm curious if other database (such as Postgres, as you mentioned) may have trouble with larger blobs; perhaps another integration test is in order to check that?

Yes, I have an integration test ready to verify this problem - outside of Oracle, no other database fails it without setting createTemporaryLob to true.