Re: create trigger crashes online
In article <348E8C64.229A@dvs1.informatik.th-darmstadt.de>, Christoph
Liebig <chris@dvs1.informatik.th-darmstadt.de> writes
>Hi !
>
>We are using online 7.23.UC2 in a database course (lab).
>Some students manage to crash nformix by issuing the following "create
>trigger" statement:
>
>CREATE TRIGGER ins_Reserv_Tr INSERT ON RESERV_TEILVB>REFERENCING NEW AS new_RT
>FOR EACH ROW
>WHEN
>(( SELECT COUNT (*)
> FROM STRECKENABSCHNITT
> WHERE AbschnittNr IN
> ( select TA.AbschnittNr
> from TVB_AUF TA
> where (TA.TVBNr = new_RT.TVBNr) and
> (TA.AbschnittNr NOT IN
> ( select FGF.StreckenAbs
> from FK_GILT_FUER FGF, RESERVIERUNG R
> where (R.ReservNr = new_RT.ReservNr) and
> (FGF.FKartenNr = R.ZuFKarte))))) = 0)
>(EXECUTE PROCEDURE errorproc('some err_msg')),
>WHEN
>(( SELECT COUNT (r1.ReservNr)
> FROM RESERVIERUNG r1, RESERVIERUNG r2
> WHERE (r1.GiltAm = r2.GiltAm) AND
> (r1.AufWagen = r2.AufWagen) AND
> (r1.AufPlatz = r2.AufPlatz) AND
> (r1.ReservNr IN
> ( SELECT DISTINCT ReservNr
> FROM RESERV_TEILVB
> WHERE (TVBNr = new_RT.TVBnr) AND
> (ReservNr != new_RT.ReservNr)))) > 0)
>(EXECUTE PROCEDURE errorproc('some err_msg')),
>--WHEN
>--(((SELECT COUNT (r2.AufPlatz)
>-- FROM RESERVIERUNG r1, RESERVIERUNG r2, Wagen w1, Wagen w2
>-- WHERE
>-- ( r2.ReservNr = new_rt.ReservNr AND
>-- r2.GiltAm = r1.GiltAm AND
>-- w2.WagenNr = r2. AufWagen AND
>-- w1.ZugNr = w2.ZugNr AND
>-- r1.AufWagen = w1.WagenNr AND
>-- r1.ReservNr IN
>-- ( SELECT ReservNr
>-- FROM RESERV_TEILVB
>-- WHERE (TVBNr = new_rt.TVBNr)))))
>-- -
>-- (SELECT Plaetze * 0.85
>-- FROM ZUG z, WAGEN w, RESERVIERUNG r
>-- WHERE
>-- ( r.ReservNr = new_RT.ReservNr AND
>-- w.WagenNr = r.AufWagen AND
>-- z.ZugNr = w.ZugNr))
>-- > 0)
>--(EXECUTE PROCEDURE errorproc('some err_msg'));
>
>If you do not quote the last 'when' section, the create trigger will not
>succeed but crash the server.
>However, executed as an SQL statement the when clauses do succeed.
>
>
>The log gives the following diagnostivs:
>
>09:30:30 Assert Failed: Internal Error - Segmentation Violation
>09:30:30 Who: Session(91, dbprak9@tunis, 13579, 169143752)
> Thread(132, sqlexec, a12bdd8, 1)
>09:30:30 Results: OnLine must abort
>09:30:30 Action: Reinitialize shared memory
>09:30:30 See Also: /tmp/af.845326, shmem.845326.0
>09:30:41 rsinit.c, line 9947, thread 132, proc id 17894, Segmentation>Violation
>.
>09:30:41 PANIC: Attempting to bring system down>
>
>For more insight here is af.845326:
>
>09:30:30
>09:30:30 Assert Failed: Internal Error - Segmentation Violation
>09:30:30 Who: Session(91, dbprak9@tunis, 13579, 169143752)
> Thread(132, sqlexec, a12bdd8, 1)
>09:30:30 Results: OnLine must abort
>09:30:30 Action: Reinitialize shared memory
>09:30:30 See Also: /tmp/af.845326, shmem.845326.0
>09:30:30 Stack for thread: 132 sqlexec>
> base: 0x0a636018
> len: 33280
> pc: 0x00384850
> tos: 0x0a63c760
>
>0x003841bc mt_affail (0x845326, 0xa24aea8, 0xa12bdd8, 0xa14edc8,
>0xa14edc8
>, 0xa24ae48)
multi-thread af fail.
>0x0027f434 rsam_affail (0xa24ae00, 0xa24ae48, 0xa24aea8, 0xa12bdd8,
>0xa14edc
>8, 0x0)
>0x0007b990 hang_thread (0x4deb40, 0x2400, 0xb, 0x4df3b0, 0x0,
>0x10000)
>0xef5f7d38 ***unknown*** (0xb, 0x0, 0xa63d030, 0x0, 0x0, 0x0)
>0x0011bfc8 wrttext (0x0, 0xa63de70, 0x70000007, 0x0, 0x20, 0x0)
write text?
>0x0011f974 wrtagg (0xa5ff830, 0xa68a1b8, 0xa63de0c, 0xa68b0f0,
>0x81, 0x
>a63de70)
write aggregate?
>0x0011d1f4 wrtexpr (0xa5ff830, 0xa68a1b8, 0xa68b0f0, 0x0, 0x81,
>0xa63de7
write expression?
>0)
>0x0011c14c wrtcollist (0xa5ff830, 0xa68a1b8, 0xa68b108, 0x0, 0x81,
>0xa63de7
write column list?
>0)
>0x0011bd04 wrtquery (0xa5ff830, 0xa68a1b8, 0x81, 0xa63de70,
>0xa63de0c, 0x
>0)
write query?
>0x0011d0a4 wrtexpr (0xa5ff830, 0xa5ff830, 0xa68bda0, 0x0, 0x81,
>0xa63de7
>0)
>0x0011eeec wrtexpr (0xa5ff830, 0xa5ff830, 0xa68cc80, 0x4f4858,
>0x81, 0xa
>63de70)
>0x0011eeec wrtexpr (0xa5ff830, 0xa5ff830, 0xa68ccf0, 0x4f4858,
>0x81, 0xa
>63de70)
>0x001202b8 wrttract (0xa5ff830, 0xa689ca8, 0xa6783a4, 0xa63de70,
>0x0, 0x0)
>0x001200e0 trbodytext (0xa5ff830, 0xa678360, 0xa63dfc4, 0xffffffff,
>0x0, 0x
translate body text (it is compiling the stored procedure).
>0)0x0015765c genbodytxt (0xa5ff830, 0xa678360, 0xa63dfc4,
>0xa5d64c0, 0x0, 0xf
>ffff1f0)
generate body text
>0x00157104 creatrig (0xa5ff830, 0xa5b0018, 0x1092, 0x1000, 0x0,
>0x0)
create trigger
>0x0007fb2c aud_creatrig (0xa5ff830, 0x186ea4, 0xa63e0ec, 0xa63e0e8,
>0x0, 0x33
>2)
audited create trigger
>0x00187c8c excommand (0x0, 0xa5b0018, 0xa5ff960, 0xa5ffaf8, 0x0,
>0x1)
execute command
>0x001604bc sq_execute (0x0, 0x0, 0xe230, 0xa004800, 0x18, 0xc0)
sql execute
>0x001724fc sqmain (0xe000, 0x7, 0x54a91c, 0x54a800, 0x54cd44,
>0x7)
sql main
>0x0036a6e4 startup (0xffffffff, 0x0, 0xa247900, 0x10,
startup
>0xef6fd5ac, 0x54aa
>30)
>0x003a84e4 net_wait_for_io (0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
>0x00000000 ***unknown***
>
>Can anyone help ? (If you need the create tables, feel free to ask)
>
All I can think of is...
1) That is quite a nested stack trace, have you tried increasing
the ONCONFIG parameter STACKSIZE to 64K?
2) Talk to Informix Tech Support.
>--Chris
>
>[\\ Dipl. Inform. Christoph Liebig [\\
> \\] Department of Computer Science \\]
>[\\ Database Research Group [\\
> \\] University of Technology, Darmstadt, Germany \\]
> / /
>[\\ email: chris@dvs1.informatik.tu-darmstadt.de [\\
> \\] phone(fax): 0049 6151 16-6231(6229) \\]
--
David Williams