[
https://issues.apache.org/jira/browse/TRAFODION-878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14721167#comment-14721167
]
Suresh Subbiah commented on TRAFODION-878:
------------------------------------------
Weishiun Tsai (wei-shiun-tsai) on 2015-01-12
summary: - HYBRID QUERY CACHE messed up transaction settings
+ Hybrid Query Cache messed up transaction settings
Jian Jin (jian-jin) on 2015-01-13
Changed in trafodion:
assignee: nobody → Howard Qin (hao-qin)
John Messavussu (john-messavussu) on 2015-01-13
Changed in trafodion:
milestone: r1.0 → r1.1
Howard Qin (hao-qin) on 2015-01-14
Changed in trafodion:
status: New → In Progress
Julie Thai (julie-y-thai) wrote on 2015-02-19: #1
Verified fix on qinhao_hqc_utt_0124:
>>drop schema mytest cascade;
*** ERROR[1003] Schema TRAFODION.MYTEST does not exist.
--- SQL operation failed with errors.
>>create schema mytest;
--- SQL operation complete.
>>set schema mytest;
--- SQL operation complete.
>>create table t (a int);
--- SQL operation complete.
>>insert into t values (1);
--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;
--- SQL operation complete.
>>insert into t values (2);
*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT,
UPDATE, DELETE, or DDL statement.
*** ERROR[8822] The statement was not prepared.
>>select * from t;
A
-----------
1
--- 1 row(s) selected.
Also verified on pcec_hqc_utt:
>>drop schema mytest cascade;
--- SQL operation complete.
>>create schema mytest;
--- SQL operation complete.
>>set schema mytest;
--- SQL operation complete.
>>create table t (a int);
--- SQL operation complete.
>>insert into t values (1);
--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;
--- SQL operation complete.
>>insert into t values (2);
*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT,
UPDATE, DELETE, or DDL statement.
*** ERROR[8822] The statement was not prepared.
>>select * from t;
A
-----------
1
--- 1 row(s) selected.
>>exit;
Julie Thai (julie-y-thai) wrote on 2015-03-09: #2
verified fix on hqc_utt_0304:
MY_SQROOT=/opt/home/trafodion/hqc_utt_0304
who@host=trafodion@amethyst-hn1
JAVA_HOME=/usr/java/jdk1.7.0_67
linux=2.6.32-279.el6.x86_64
redhat=6.3
Release 1.1.0 (Build release [qinhao], branch 087e0e4-hqc-bug-fix, date 04Mar15)
>From TRAFCI:
SQL>drop schema mytest cascade;
*** ERROR[1003] Schema TRAFODION.MYTEST does not exist. [2015-03-09 20:22:45]
SQL>create schema mytest;
--- SQL operation complete.
SQL>set schema mytest;
--- SQL operation complete.
SQL>create table t (a int);
--- SQL operation complete.
SQL>insert into t values (1);
--- 1 row(s) inserted.
SQL>SET TRANSACTION READ ONLY;
--- SQL operation complete.
SQL>insert into t values (2);
*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT,
UPDATE, DELETE, or DDL statement. [2015-03-09 20:22:48]
SQL>select * from t;
A
-----------
1
--- 1 row(s) selected.
Weishiun Tsai (wei-shiun-tsai) wrote on 2015-03-24: #3
Verified on the v0324 build installed on a workstation. This problem has bee
fixed:
>>create schema mytest;
--- SQL operation complete.
>>set schema mytest;
--- SQL operation complete.
>>create table t (a int);
--- SQL operation complete.
>>insert into t values (1);
--- 1 row(s) inserted.
>>SET TRANSACTION READ ONLY;
--- SQL operation complete.
>>insert into t values (2);
*** ERROR[3141] The transaction access mode cannot be READ ONLY for an INSERT,
UPDATE, DELETE, or DDL statement.
*** ERROR[8822] The statement was not prepared.
>>select * from t;
A
-----------
1
--- 1 row(s) selected.
Changed in trafodion:
status: In Progress → Fix Released
> LP Bug: 1409830 - Hybrid Query Cache messed up transaction settings
> -------------------------------------------------------------------
>
> Key: TRAFODION-878
> URL: https://issues.apache.org/jira/browse/TRAFODION-878
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Howard Qin
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> With HYBRID QUERY CACHE turned on, SET TRANSACTION READ ONLY ends up having
> no effect. As shown in the following example, the 2nd insert statement was
> expecting error 3141, but the insert went through even after the TRANSACTION
> was set to READ ONLY. This problem goes away once we turn the CQD HYBRID
> QUERY CACHE to ‘OFF’.
> This is seen on the v0110_0830 build. It is a regression introduced sometime
> around the end of December.
> ----------------------------------------------
> Here is the entire script to reproduce it:
> drop schema mytest cascade;
> create schema mytest;
> set schema mytest;
> create table t (a int);
> insert into t values (1);
> SET TRANSACTION READ ONLY;
> insert into t values (2);
> select * from t;
> ----------------------------------------------
> Here is the execution output with the CQD HYBRID_QUERY_CACHE turned on (it’s
> on by default)
> >>drop schema mytest cascade;
> *** ERROR[1003] Schema TRAFODION.MYTEST does not exist.
> --- SQL operation failed with errors.
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- SQL operation complete.
> >>create table t (a int);
> --- SQL operation complete.
> >>insert into t values (1);
> --- 1 row(s) inserted.
> >>SET TRANSACTION READ ONLY;
> --- SQL operation complete.
> >>insert into t values (2);
> --- 1 row(s) inserted.
> >>select * from t;
> A
> -----------
> 1
> 2
> --- 2 row(s) selected.
> ----------------------------------------------
> Here is the execution output with the CQD HYBRID_QUERY_CACHE turned off
> >>cqd HYBRID_QUERY_CACHE 'OFF';
> --- SQL operation complete.
> >>drop schema mytest cascade;
> --- SQL operation complete.
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- SQL operation complete.
> >>create table t (a int);
> --- SQL operation complete.
> >>insert into t values (1);
> --- 1 row(s) inserted.
> >>SET TRANSACTION READ ONLY;
> --- SQL operation complete.
> >>insert into t values (2);
> *** ERROR[3141] The transaction access mode cannot be READ ONLY for an
> INSERT, UPDATE, DELETE, or DDL statement.
> *** ERROR[8822] The statement was not prepared.
> >>select * from t;
> A
> -----------
> 1
> --- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
