Hello, I've found a bug in a multi tenant application.
I've tracked down the problem and can also provide a small test to
reproduce the problem.
When executing the same preparedStatement once for TENANT_1 and then for
TENANT_2, the data that should go to TENANT_2 goes to TENANT_1
The problem is, that Session.prepareLocal returns an already prepared
command for the first schema if schema was switched.
A *fix* could be to to check also Session.getModificationId() in
org.h2.engine.Session.prepareLocal() and invalidate the cache if needed.
The *workaround* is to disable the QueryCache with QUERY_CACHE_SIZE=0
Cheers
Roland
Here's the test case
public class Test {
public static void main(String[] a) throws Exception {
Class.forName("org.h2.Driver");
Connection conn0 = DriverManager.getConnection(
"jdbc:h2:mem:tests;TRACE_LEVEL_SYSTEM_OUT=3", "SA", "");
// this will fix the problem
// Connection conn0 =
DriverManager.getConnection("jdbc:h2:mem:tests;TRACE_LEVEL_SYSTEM_OUT=3;QUERY_CACHE_SIZE=0",
"SA", "");
// Create schemas
Statement stat1 = conn0.createStatement();
stat1.execute("CREATE SCHEMA TENANT_1");
Statement stat2 = conn0.createStatement();
stat2.execute("CREATE SCHEMA TENANT_2");
// create test tables in schema 1 und 2
conn0.setSchema("TENANT_1");
PreparedStatement prep1 = conn0.prepareStatement("create table
local_model ( id integer auto_increment not null, name varchar(255),
constraint pk_local_model primary key (id) )");
prep1.execute();
conn0.setSchema("TENANT_2");
prep1.execute();
// insert data in Tenant 1
conn0.setSchema("TENANT_1");
PreparedStatement prep2 = conn0.prepareStatement("insert into
local_model (name) values (?)", new String[]{"id"});
prep2.setString(1, "tenant 1");
prep2.executeUpdate();
prep2.close();
conn0.commit();
// insert data in Tenant 2
conn0.setSchema("TENANT_2");
PreparedStatement prep3 = conn0.prepareStatement("insert into
local_model (name) values (?)", new String[]{"id"});
prep3.setString(1, "tenant 2");
prep3.executeUpdate();
prep3.close();
conn0.commit();
// Out
Statement stat3 = conn0.createStatement();
stat3.executeQuery("SCRIPT TO 'out/dump.sql'");
/* Take a look at the
* _should_:
* INSERT INTO TENANT_1.LOCAL_MODEL(ID, NAME) VALUES (1, 'tenant 1');
* INSERT INTO TENANT_2.LOCAL_MODEL(ID, NAME) VALUES (1, 'tenant 2');
*
* _is_:
* INSERT INTO TENANT_1.LOCAL_MODEL(ID, NAME) VALUES (1, 'tenant 1'),
(2, 'tenant 2');
*/
conn0.close();
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
