[TRAFODION-265] LP Bug: 1319103 - Get problem_with_server_read when delete from table with foreign key - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 0.8 (pre-incubation)
Component/s: None
Labels:
None

External issue URL:
https://bugs.launchpad.net/bugs/1319103

Description

SQL>delete from male_actors where m_no = 6555;

- - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough
  - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough

– Testlog
SQL>env;

COLSEP " "
HISTOPT DEFAULT [No expansion of script files]
IDLETIMEOUT 30 min(s)
LIST_COUNT 0 [All Rows]
LOG FILE t04log
LOG OPTIONS CLEAR,CMDTEXT ON
MARKUP RAW
PROMPT SQL>
SCHEMA SEABASE
SERVER rhel-cdh1.hpl.hp.com:37800
SQLTERMINATOR ;
STATISTICS OFF
TIME OFF
TIMING OFF
USER trafodion

SQL>drop schema debug_tab02 cascade;

— SQL operation complete.

SQL>create schema debug_tab02;

— SQL operation complete.

SQL>set schema debug_tab02;

— SQL operation complete.

SQL>create table Female_actors (
f_no int not null not droppable,
f_name varchar(30) not null,
f_realname varchar(50) default null,
f_birthday date constraint md1 check (f_birthday > date '1900-01-01'),
primary key (f_no)
)
;

— SQL operation complete.

SQL>create table Male_actors (
m_no int not null not droppable unique,
m_name varchar(30) not null,
m_realname varchar(50) default null,
m_birthday date constraint md2 check (m_birthday > date '1900-01-01')
) no partition;

— SQL operation complete.

SQL>create table Directors (
d_no int not null not droppable,
d_name varchar(30) not null,
"d_specialty" varchar(15) not null unique,
primary key (d_no),
constraint td1 check ("d_specialty" <> 'Music Video'),
unique (d_no, "d_specialty")
);

— SQL operation complete.

SQL>Create table Movie_titles (
mv_no int not null not droppable,
mv_name varchar (40) not null,
mv_malestar int default NULL constraint debug_tab02.ma_fk
references male_actors(m_no),
mv_femalestar int default NULL,
mv_director int default 0 not null,
mv_yearmade int check (mv_yearmade > 1901),
mv_star_rating char(4),
mv_movietype varchar(15),
primary key (mv_no),
constraint fa_fk foreign key (mv_femalestar)
references female_actors,
constraint d_fk foreign key (mv_director, mv_movietype)
references directors (d_no, "d_specialty")
);

— SQL operation complete.

SQL>insert into directors values (0, 'No director named','Unknown')
,(1234, 'Alfred Hitchcock', 'Mystery')
,(1345, 'Clint Eastwood','Action')
,(1456, 'Fred Zinneman', 'Western')
,(1567, 'George Cukor', 'Drama')
,(1789, 'Roger Corman','Scary')
;

— 6 row(s) inserted.

SQL>insert into Male_actors values (0, 'No male actor','No male actor', current_date)
,(1111, 'Cary Grant','Archibald Alec Leach',date '1904-01-18')
,(1222, 'Gary Cooper','Frank James Cooper', date '1901-05-07')
,(1333, 'Clint Eastwood','Clinton Eastwood Jr.', date '1930-05-31');

— 4 row(s) inserted.

SQL>insert into Female_actors values (0, 'No female actor','No female actor', current_date),
(6111, 'Grace Kelly', 'Grace Patricia Kelly', date '1929-11-12'),
(6123, 'Katherine Hepburn','Katharine Houghton Hepburn', date '1907-05-12'),
(6124, 'Joan Crawford','Lucille Fay LeSueur', date '1904-03-23'),
(6125, 'Ingrid Bergman', 'Ingrid Bergman', date '1915-08-29');

— 5 row(s) inserted.

SQL>insert into Movie_titles values
(1,'To Catch a Thief',1111, 6111, 1234, 1955, '****','Mystery'),
(2,'High Noon',1222, 6111, 1456, 1951, '****','Western'),
(3,'Unforgiven', 1333, 0, 1345, 1990, '***', 'Action'),
(4,'The Women', 0, 6124, 1567, 1939, '****', 'Drama'),
(5,'The Philadelphia Story',1111, 6123,1567, 1940, '****','Drama'),
(6,'Notorious', 1111, 6125, 1234, 1946, '****','Mystery');

— 6 row(s) inserted.

SQL>--------------------------------------------

SQL>insert into male_actors values (6555,'Jimmy Stewart','James Maitland Stewart', date '1908-05-20');

— 1 row(s) inserted.

SQL>insert into female_actors values (6127,'Constance Bennett', 'Constance Campbell Bennett',date '1904-10-22');

— 1 row(s) inserted.

SQL>insert into female_actors values (6128,'Eva Marie Saint','Eva Marie Saint', date '1924-07-04');

— 1 row(s) inserted.

SQL>insert into female_actors values (6130,'Irene Dunne','Irene Marie Dunne', date '1898-12-20');

- - ERROR[8101] The operation is prevented by check constraint TRAFODION.DEBUG_TAB02.MD1 on table TRAFODION.DEBUG_TAB02.FEMALE_ACTORS. [2014-05-13 08:59:09]

SQL>insert into female_actors values (6130,'Irene Dunne','Irene Marie Dunne', date '1900-12-20');

— 1 row(s) inserted.

SQL>insert into directors values (1799,'Norman MacLoud','Comedy1');

— 1 row(s) inserted.

SQL>insert into directors values (1890,'Garson Kanin','Comedy2');

— 1 row(s) inserted.

SQL>showddl male_actors, internal;

CREATE TABLE TRAFODION.DEBUG_TAB02.MALE_ACTORS
(
M_NO INT NO DEFAULT NOT NULL NOT DROPPABLE
, M_NAME VARCHAR(30) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, M_REALNAME VARCHAR(50) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
, M_BIRTHDAY DATE DEFAULT NULL
)
;

– The following index is a system created index –
CREATE UNIQUE INDEX MALE_ACTORS_968125641_4426 ON
TRAFODION.DEBUG_TAB02.MALE_ACTORS
(
M_NO ASC
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MALE_ACTORS ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.MALE_ACTORS_968125641_4426 UNIQUE
(
M_NO
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MALE_ACTORS ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.MD2 CHECK (TRAFODION.DEBUG_TAB02.MALE_ACTORS.M_BIRTHDAY
> DATE '1900-01-01')

— SQL operation complete.

SQL>showddl movie_titles, internal;

CREATE TABLE TRAFODION.DEBUG_TAB02.MOVIE_TITLES
(
MV_NO INT NO DEFAULT NOT NULL NOT DROPPABLE
, MV_NAME VARCHAR(40) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
, MV_MALESTAR INT DEFAULT NULL
, MV_FEMALESTAR INT DEFAULT NULL
, MV_DIRECTOR INT DEFAULT 0 NOT NULL NOT DROPPABLE
, MV_YEARMADE INT DEFAULT NULL
, MV_STAR_RATING CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
, MV_MOVIETYPE VARCHAR(15) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL
, PRIMARY KEY (MV_NO ASC)
)
;

– The following index is a system created index –
CREATE INDEX D_FK ON TRAFODION.DEBUG_TAB02.MOVIE_TITLES
(
MV_DIRECTOR ASC
, MV_MOVIETYPE ASC
)
;

– The following index is a system created index –
CREATE INDEX FA_FK ON TRAFODION.DEBUG_TAB02.MOVIE_TITLES
(
MV_FEMALESTAR ASC
)
;

– The following index is a system created index –
CREATE INDEX MA_FK ON TRAFODION.DEBUG_TAB02.MOVIE_TITLES
(
MV_MALESTAR ASC
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MOVIE_TITLES ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.D_FK FOREIGN KEY
(
MV_DIRECTOR
, MV_MOVIETYPE
)
REFERENCES TRAFODION.DEBUG_TAB02.DIRECTORS
(
D_NO
, "d_specialty"
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MOVIE_TITLES ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.FA_FK FOREIGN KEY
(
MV_FEMALESTAR
)
REFERENCES TRAFODION.DEBUG_TAB02.FEMALE_ACTORS
(
F_NO
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MOVIE_TITLES ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.MA_FK FOREIGN KEY
(
MV_MALESTAR
)
REFERENCES TRAFODION.DEBUG_TAB02.MALE_ACTORS
(
M_NO
)
;

ALTER TABLE TRAFODION.DEBUG_TAB02.MOVIE_TITLES ADD CONSTRAINT
TRAFODION.DEBUG_TAB02.MOVIE_TITLES_746735641_4426 CHECK
(TRAFODION.DEBUG_TAB02.MOVIE_TITLES.MV_YEARMADE > 1901)

— SQL operation complete.

SQL>select * from directors;

D_NO D_NAME d_specialty
----------- ------------------------------ ---------------
0 No director named Unknown
1234 Alfred Hitchcock Mystery
1345 Clint Eastwood Action
1456 Fred Zinneman Western
1567 George Cukor Drama
1789 Roger Corman Scary
1799 Norman MacLoud Comedy1
1890 Garson Kanin Comedy2

— 8 row(s) selected.

SQL>select * from male_actors;

M_NO M_NAME M_REALNAME M_BIRTHDAY
----------- ------------------------------ -------------------------------------------------- ----------
0 No male actor No male actor 2014-05-13
1111 Cary Grant Archibald Alec Leach 1904-01-18
1222 Gary Cooper Frank James Cooper 1901-05-07
1333 Clint Eastwood Clinton Eastwood Jr. 1930-05-31
6555 Jimmy Stewart James Maitland Stewart 1908-05-20

— 5 row(s) selected.

SQL>select * from female_actors;

F_NO F_NAME F_REALNAME F_BIRTHDAY
----------- ------------------------------ -------------------------------------------------- ----------
0 No female actor No female actor 2014-05-13
6111 Grace Kelly Grace Patricia Kelly 1929-11-12
6123 Katherine Hepburn Katharine Houghton Hepburn 1907-05-12
6124 Joan Crawford Lucille Fay LeSueur 1904-03-23
6125 Ingrid Bergman Ingrid Bergman 1915-08-29
6127 Constance Bennett Constance Campbell Bennett 1904-10-22
6128 Eva Marie Saint Eva Marie Saint 1924-07-04
6130 Irene Dunne Irene Marie Dunne 1900-12-20

— 8 row(s) selected.

SQL>select count from male_actors;

(EXPR)
--------------------
5

— 1 row(s) selected.

SQL>select * from male_actors where m_no = 6555;

M_NO M_NAME M_REALNAME M_BIRTHDAY
----------- ------------------------------ -------------------------------------------------- ----------
6555 Jimmy Stewart James Maitland Stewart 1908-05-20

— 1 row(s) selected.

SQL>--#expect any 1 row(s) deleted

SQL>-- unexpected ERROR[1] The message id: problem_with_server_read

SQL>delete from male_actors where m_no = 6555;

- - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough
  - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough

SQL>select * from directors;

- - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough
  - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough

SQL>select * from directors where d_no in (1799, 1890);

- - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough
  - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough

SQL>--#expect any 2 row(s) deleted

SQL>delete from directors where d_no in (1799,1890);

- - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough
  - ERROR[1] The message id: problem_with_server_read
  - ERROR[1] The message id: header_not_long_enough

SQL>--------------------------------------------

SQL>log off;

LP Bug: 1319103 - Get problem_with_server_read when delete from table with foreign key

Details

Description

Attachments

Activity

People

Dates