Description:
The Select query from a JDBC connection always returns the cached result even if cache was disabled at the server with query_cache_size = 0.
This senario occurs with innodb engine. When MyISAM tables are used the Select Query is consistent with the actualy data in the tables. No further tests were made with other types of engine.
An example of the problem is described below.
Suppost a Database called DB_TEST contains one table "SERVICE" as following:
CREATE TABLE SERVICE(
SERVICE_ID INTEGER NOT NULL,
SERVICE_NAME VARCHAR(25) NOT NULL,
PRIMARY KEY (SERVICE_ID)
)ENGINE=InnoDB
Then the table was populated with the following data:
INSERT INTO SERVICE VALUES(1,'SERVICE_1');
INSERT INTO SERVICE VALUES(2,'SERVICE_2');
There are two JAVA applications that performs queries and updates on this table, Let's say Application-A and Application-B.
The following scenario will produce false data:
STEP 1 : Application-A performs: "SELECT * FROM SERVICE". the result should be
+------------+---------------+
| SERVICE_ID | SERVICE_NAME|
+------------+---------------+
| 1 | SERVICE_1 |
| 2 | SERVICE_2 |
+------------+---------------+
STEP 2 : Application-B performs: "INSERT INTO SERVICE VALUES(3,'SERVICE_3')"
STEP 3: Application-A performs the command: "SELECT * FROM SERVICE" --> the result returned is the same one as at STEP 1,
+------------+---------------+
| SERVICE_ID | SERVICE_NAME|
+------------+---------------+
| 1 | SERVICE_1 |
| 2 | SERVICE_2 |
+------------+---------------+
which is inconsistent with the actual data in the table...
I'm connection to the Database using a JDBC connection as following:
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception ex2) {
....
}
try {
con = DriverManager.getConnection(jdbc:mysql://localhost:3306/DB_TEST?user=USR&password=PASWD);
}
Note that if the engine and the tables were MyISAM the problem does not occur.
catch (SQLException ex1) {
....
}
How to repeat:
1 - Start mysql with innodb as default engine
2 - Create a database: DB_TEST for example
3 - Create a table: SERVICE for example
4 - Develop two java Applications that connects to this database.
5 - Implement the scenario described in the Description section.
Suggested fix:
Have an option to completely turn off Cache with a JDBC connection
Description: The Select query from a JDBC connection always returns the cached result even if cache was disabled at the server with query_cache_size = 0. This senario occurs with innodb engine. When MyISAM tables are used the Select Query is consistent with the actualy data in the tables. No further tests were made with other types of engine. An example of the problem is described below. Suppost a Database called DB_TEST contains one table "SERVICE" as following: CREATE TABLE SERVICE( SERVICE_ID INTEGER NOT NULL, SERVICE_NAME VARCHAR(25) NOT NULL, PRIMARY KEY (SERVICE_ID) )ENGINE=InnoDB Then the table was populated with the following data: INSERT INTO SERVICE VALUES(1,'SERVICE_1'); INSERT INTO SERVICE VALUES(2,'SERVICE_2'); There are two JAVA applications that performs queries and updates on this table, Let's say Application-A and Application-B. The following scenario will produce false data: STEP 1 : Application-A performs: "SELECT * FROM SERVICE". the result should be +------------+---------------+ | SERVICE_ID | SERVICE_NAME| +------------+---------------+ | 1 | SERVICE_1 | | 2 | SERVICE_2 | +------------+---------------+ STEP 2 : Application-B performs: "INSERT INTO SERVICE VALUES(3,'SERVICE_3')" STEP 3: Application-A performs the command: "SELECT * FROM SERVICE" --> the result returned is the same one as at STEP 1, +------------+---------------+ | SERVICE_ID | SERVICE_NAME| +------------+---------------+ | 1 | SERVICE_1 | | 2 | SERVICE_2 | +------------+---------------+ which is inconsistent with the actual data in the table... I'm connection to the Database using a JDBC connection as following: try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex2) { .... } try { con = DriverManager.getConnection(jdbc:mysql://localhost:3306/DB_TEST?user=USR&password=PASWD); } Note that if the engine and the tables were MyISAM the problem does not occur. catch (SQLException ex1) { .... } How to repeat: 1 - Start mysql with innodb as default engine 2 - Create a database: DB_TEST for example 3 - Create a table: SERVICE for example 4 - Develop two java Applications that connects to this database. 5 - Implement the scenario described in the Description section. Suggested fix: Have an option to completely turn off Cache with a JDBC connection