Hi all,

i've got a massive problem with SA17 latest Build (17.0.10.5820): The database server crashes every time when it comes to execute the following trigger:

ALTER TRIGGER "_kunde_unit_id_on_update" before update of "UNIT_ID"
order 1 on "DBA"."KUNDE"
referencing old as "oldrow" new as "newrow"
for each row
begin
   update "dba"."kd_preise" publication "dba"."agenturdaten unit" old subscribe by
      "oldrow"."unit_id" new subscribe by
      "newrow"."unit_id" where "kd_preise"."kd_id" = "newrow"."kd_id";
    update "dba"."pg_to_kunde" publication "dba"."agenturdaten unit" old subscribe by
      "oldrow"."unit_id" new subscribe by
      "newrow"."unit_id" where "pg_to_kunde"."kd_id" = "newrow"."kd_id";
    update "dba"."rabatt_to_kd" publication "dba"."agenturdaten unit" old subscribe by
      "oldrow"."unit_id" new subscribe by
      "newrow"."unit_id" where "rabatt_to_kd"."kd_id" = "newrow"."kd_id";
    update "dba"."rechnungen" publication "dba"."agenturdaten unit" old subscribe by
      "oldrow"."unit_id" new subscribe by
      "newrow"."unit_id" where "rechnungen"."kd_id" = "newrow"."kd_id";
    update "dba"."rech_pos" publication "dba"."agenturdaten unit" old subscribe by
      "oldrow"."unit_id" new subscribe by
      "newrow"."unit_id" where "rech_pos"."rech_id" = any(select "rechnungen"."rech_id" from "dba"."rechnungen" where "rechnungen"."kd_id" = "newrow"."kd_id")
end

The database is the consolidated db of a sqlremote system (of course) and dbremote runs into an error every time (of course). Are there any know issues or does anyone know a workaround?

Thanks, Axel

asked 13 Jul, 03:51

Axel%20Siepmann's gravatar image

Axel Siepmann
1157716
accept rate: 0%

edited 13 Jul, 07:36

Volker%20Barth's gravatar image

Volker Barth
33.8k332487717

How do you know the trigger is the source? Is it safe to assume that the crashes go away if you remove the trigger?

What changed that caused the crash? Did you upgrade from one version to another, and if so, what version? Did you change the schema of your database?

Are any mini-dump files created when engine crashes? Mini-dump files are typically located at %ALLUSERSPROFILE%\SQL Anywhere 17\diagnostics on windows.

Reg

(14 Jul, 08:30) Reg Domaratzki

Yes, it is. The server does not crash if the trigger is removed. I just tried it now (again). I can't say what was the last version of SA which executed the trigger correctly. The case the trigger is fired occurs very rarely. The crash already occurred with build 17.09 and i already did a rebuild of the database. I have some other "update publication" triggers in this database but all of them react on inserts and deletes but not on updates and work fine. I'll try to build an allocation table with kd_id and unit_id and corresponding insert and delete triggers.

(14 Jul, 12:34) Axel Siepmann

There are no dump files.

(14 Jul, 12:50) Axel Siepmann
Replies hidden
1

Wild Idea Number One: Try running it without the final update "dba"."rech_pos"... to see if that is the culprit.

Wild Idea Number Two: If it worked without the final update "dba"."rech_pos"..., try changing the where clause to this...

where exists ( select * from "dba"."rechnungen" where "rechnungen"."kd_id" = "newrow"."kd_id" and "rechnungen"."rech_id" = "rech_pos"."rech_id" )

Sometimes bugs can be avoided by changing the code to take a different path.

(14 Jul, 15:16) Breck Carter

Ok, thanks Breck. I'll give it a try.

(15 Jul, 06:32) Axel Siepmann

Did not work. Server still crashed.

(16 Jul, 04:01) Axel Siepmann

> Server still crashed

When the server crashes, what (if anything) is written to the dbsrv17.exe -o file?

(17 Jul, 09:05) Breck Carter

Without a dump file, it is not possible to determine the root cause of the crash.

(17 Jul, 09:07) Reg Domaratzki
More comments hidden
showing 5 of 8 show all flat view

I developed a workaround with an allocation table, modified publications and insert- and delete-triggers. This works. The server does not crash anymore.

permanent link

answered 16 Jul, 04:00

Axel%20Siepmann's gravatar image

Axel Siepmann
1157716
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×136
×89
×49
×15

question asked: 13 Jul, 03:51

question was seen: 108 times

last updated: 17 Jul, 09:07