|
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 |
|
I developed a workaround with an allocation table, modified publications and insert- and delete-triggers. This works. The server does not crash anymore. |
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
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.
There are no dump files.
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.
Ok, thanks Breck. I'll give it a try.
Did not work. Server still crashed.
> Server still crashed
When the server crashes, what (if anything) is written to the dbsrv17.exe -o file?
Without a dump file, it is not possible to determine the root cause of the crash.