Updated: failed due to concurrent event execution

[Breck_Carter]

Does the following "Attempt to reload definition" message come from the SQL Anywhere 16 server? (Google hasn't been much help)

SQL Anywhere Network Server Version 16.0.0.2419
...
E. 04/22 08:23:24. Attempt to reload definition for event "rroad_monitor_sample_loop" failed due to concurrent event execution

AFAIK it appears when a particular TRIGGER EVENT is executed, in legacy code which has been executed billions of times over the years without this symptom.

If this is an (undocumented?) SQL Anywhere message, is it an exception that would (should?) be trapped by EXCEPTION WHEN OTHERS THEN?

Bad news: The code has EXCEPTION handlers everywhere; i.e., in the triggering block and in the event handler itself, and neither of them saw an exception.

Good news: Stopping and restarting dbsrv16 made it go away.

Bad news: A repro is unlikely 🙂

---

2017 05 28 Update This symptom is reproducible, and appears to be version dependent.

The database was created with SQL Anywhere 16.0.0.2127.

It works OK when run on SQL Anywhere 16.0.0.2127.

It fails when run on SQL Anywhere 16.0.0.2419: Attempt to reload definition for event "rroad_monitor_sample_loop" failed due to concurrent event execution

It fails when run on SQL Anywhere 17.0.6.2806: Attempt to reload definition for event "rroad_monitor_sample_loop" failed due to concurrent event execution

The error message appears when this statement is executed, before the event starts executing.

         TRIGGER EVENT rroad_monitor_sample_loop ( 
            @p_sampling_id        = STRING ( @sampling_id ),
            @p_initial_delay_time = STRING ( @initial_delay_time ) );

Sadly, I have no control over which build of SQL Anywhere the client uses, so I'll be looking for a workaround (an EBF won't help me).

The bad news is, this is a monstrous bug in Foxhound.

The good news is, I did NOT miss it during testing which used 16.0.0.2127.

In the meantime, if this is of any interest to The Powers That Sustain SQL Anywhere, I can share further details.

---

Update #2 2017 05 29

Several builds were tested, not an exhaustive list but exhausting for me :)...

Here are the builds that matter:

SQL Anywhere 16.0.0.2193 works OK

SQL Anywhere 16.0.0.2344 fails with Attempt to reload definition for event ...

SQL Anywhere 16.0.0.2471 (latest V16 EBF) fails with Attempt to reload definition for event ...

SQL Anywhere 17.0.0.1359 works ok

SQL Anywhere 17.0.4.2053 fails with Attempt to reload definition for event ...

SQL Anywhere 17.0.7.3399 (latest V17 EBF) fails with Attempt to reload definition for event ...

Conclusions:

SQL Anywhere 16 behavior changed at some point between 16.0.0.2193 and 16.0.0.2344, and remains unchanged up to the latest EBF 16.0.0.2471.

SQL Anywhere 17 behavior changed at some point between 17.0.0.1359 and 17.0.4.2053, and remains unchanged up to the latest EBF 17.0.7.3399.

The "remains unchanged up to the latest EBF" probably means a Foxhound patch must be developed to work around SQL Anywhere's behavior change [cue sound of hopes being dashed].

[MarkCulp · Accepted solution]

The noted message does get generated by the server. This message will be printed to the server console log when an event is being prepared to be executed, the event is marked as needing to be reloaded (for example, due to DDL having been executed on the database), and the attempt to reload the event definition failed (for not entirely understood reasons ... but looks to be related to having multiple occurrences of a single event executing at the same time).

This message cannot be trapped by an exception clause because the issue is raised on the worker that is attempting to execute the event. I.e. it is running asynchronous to the worker that triggered the event.

AFAICT There is no easy way to detect when this has happened (except scanning the console log for the message).

[Breck_Carter · Accepted solution]

The symptom is reproducible; the question has been updated.

[VolkerBarth · Accepted solution]

related to having multiple occurrences of a single event executing at the same time

Breck, might that be an issue, or do you restrict that by checking event_parameter('NumActive')?

[Breck_Carter · Accepted solution]

It should not be an issue, and no, the code doesn't check NumActive... there is no technical reason to check it, and in Foxhound's case, there is no business reason to check NumActive. In stress tests, Foxhound regularly has 100 instances of the same event running (to monitor 100 target databases), and it has worked well for many years. In this particular case, multiple instances do work, but start failing after something happens (I am still looking for the "something" 🙂